It is importing the data into SQLite and running the query there. I don't see any attempt at type detection.
SQLite has virtual tables where app provided code can provide the underlying data and you can then use SQL queries against it. This is by far the easiest approach for foreign data formats. http://www.sqlite.org/vtab.html
I'm the author of a Python wrapper for SQLite. It provides a shell compatible with the main SQLite one invocable from the command line (ie you don't need to go anywhere near Python) - http://rogerbinns.github.io/apsw/shell.html
That shell has an autoimport command which automatically figures things out for csv like data (separators, data types etc)
sqlite> .help autoimport
.autoimport FILENAME ?TABLE? Imports filename creating a table and automatically working out
separators and data types (alternative to .import command)
The import command requires that you precisely pre-setup the table and schema, and set the data
separators (eg commas or tabs). In many cases this information can be automatically deduced from
the file contents which is what this command does. There must be at least two columns and two rows.
If the table is not specified then the basename of the file will be used.
Additionally the type of the contents of each column is also deduced - for example if it is a number
or date. Empty values are turned into nulls. Dates are normalized into YYYY-MM-DD format and
DateTime are normalized into ISO8601 format to allow easy sorting and searching. 4 digit years must
be used to detect dates. US (swapped day and month) versus rest of the world is also detected
providing there is at least one value that resolves the ambiguity.
Care is taken to ensure that columns looking like numbers are only treated as numbers if they do not
have unnecessary leading zeroes or plus signs. This is to avoid treating phone numbers and similar
number like strings as integers.
This command can take quite some time on large files as they are effectively imported twice. The
first time is to determine the format and the types for each column while the second pass actually
imports the data.
I've written a log file viewer (http://lnav.org) that exports log messages as SQLite virtual tables. I was hoping this project also leveraged virtual tables since they're one of the coolest features in SQLite.
lnav tries to push things a little further and extract data from unstructured log messages. For example, given the following message:
"Registering new address record for 10.1.10.62 on eth0."
It will create a virtual table with two columns that capture the IP address (10.1.10.62) and the device name (eth0). Queries against this table will return the values from all messages with the same format (Registering new address record for COL_0 on COL_1). See http://lnav.readthedocs.org/en/latest/data.html for more info.
I did a similar thing at work, but with virtual tables. Supports zip/gz files and multiple files with glob expansion. Might open source if there's interest. It's based on a sample CSV vtab source. Has type automatic detection. No indexing yet, though.
SQLite has virtual tables where app provided code can provide the underlying data and you can then use SQL queries against it. This is by far the easiest approach for foreign data formats. http://www.sqlite.org/vtab.html
I'm the author of a Python wrapper for SQLite. It provides a shell compatible with the main SQLite one invocable from the command line (ie you don't need to go anywhere near Python) - http://rogerbinns.github.io/apsw/shell.html
That shell has an autoimport command which automatically figures things out for csv like data (separators, data types etc)
sqlite> .help autoimport
.autoimport FILENAME ?TABLE? Imports filename creating a table and automatically working out separators and data types (alternative to .import command)
The import command requires that you precisely pre-setup the table and schema, and set the data separators (eg commas or tabs). In many cases this information can be automatically deduced from the file contents which is what this command does. There must be at least two columns and two rows.
If the table is not specified then the basename of the file will be used.
Additionally the type of the contents of each column is also deduced - for example if it is a number or date. Empty values are turned into nulls. Dates are normalized into YYYY-MM-DD format and DateTime are normalized into ISO8601 format to allow easy sorting and searching. 4 digit years must be used to detect dates. US (swapped day and month) versus rest of the world is also detected providing there is at least one value that resolves the ambiguity.
Care is taken to ensure that columns looking like numbers are only treated as numbers if they do not have unnecessary leading zeroes or plus signs. This is to avoid treating phone numbers and similar number like strings as integers.
This command can take quite some time on large files as they are effectively imported twice. The first time is to determine the format and the types for each column while the second pass actually imports the data.