From what I can tell, this creates an SQLite instance each time it's executed, and creates it in memory unless told not to do so. So I'd guess it wouldn't scale well. Still really neat though!
Even then, it should scale reasonable well if one added a repl, and kept the database in memory. Probably would be fine then up until at least a couple gigabytes, depending on how well sqlite handles in-memory tables (probably very well).
I assume textql is basically just running a similar SQL query, but perhaps autodetecting column types for you? (Haven't looked at the code.) Is that the value proposition, or does it do something more?
Your examples should be using single quotes for strings not double quotes. The latter are for quoting identifiers (eg if you named a table "select") and through an accident of SQLite history degrade to a string if no matching identifier is found.
+ is the SQL numeric addition operator. || is string concatenation.
Key differences:
- sqlite import demands an on disk db, textql will use an in memory db if it can, increases performance
- sqlite import will not accept stdin, breaking unix pipes. textql will happily do so.
I will make a note of this in the project readme, seems to crop up a lot.
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 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.
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.
On a related note there is a recently published paper exploring how to load CSV data into a database efficiently[1].
The authors get a loading throughput (including index creation!) of over 1.5 GB/S with multithreading. They don't seem to do any dirty tricks when loading the data as they immediately can run hundreds of thousands of queries per second on the data.
I have been doing this with the unix commands 'join' and 'awk'. For larger files I import it to a database (ex sqllite). I am not sure what advantages this tool provides.
We use a format called Tabix[1] frequently in bioinformatics. It doesn't have a full query language, but is optimized for extracting regions from a text file and supports compression. It's pretty clever — it's based on a block compressed (BGZF) tab file with virtual offsets. An index allows for fast random access.
An alternative approach is Microsoft Log Parser. While it is old in internet time (2005), it works brilliantly on very large files. I've successfully used it on 10+ GigaByte files. The documentation is a little sparse but once you get the hang of it it's pretty simple. Clearly it only works on windows boxen.
Example:
#Find all unique from col1
logparser -i:csv -o:csv -stats:off -dtlines:2000 -headers:off "select distinct col1 from input.csv" >out.csv
It seems that textql doesn't parse the query at all, it imports the CSV into an sqlite3 db and then runs the query against it. What you ask for would be doable but it would be much more complex (parsing SQL is not that trivial, and people would want to do more complex queries like JOINing multiple CSV files etc., so you can't just look for "from <filename>").
I do a great deal of work in DevOps and I have a text file full of scripts which come handy to do work like this during production issues.
Nearly every sysadmin/firefigher I know has command line perl/awk/sed magic text files full of this.
Having said that I would appreciate if something like this was available for XMLs. Having to deal with SQL designs force fully shoe horned into NoSQL databases so often these days. I frequently run into situations where I have to query data from XML dumps. Most of the time, I have to invent a Perl one liner that essentially does the job of a select query.
Well there is XSL and XQL (XML Query Language). There doesn't seem to be many XQL implementations, but this looks interesting: http://www.cs.york.ac.uk/fp/Xtract/. Also check xmllint and xmlstar.
I have a command-line thing put together with HtmlAgilityPack that represents HTML in an XPath-navigable document. It lets me apply the same XPath queries to HTML, for screen scraping.
I end up parsing a lot of excels at work (not my choice). I saw this and thought it looked interesting - export Execl to a CSV and run a query there.
At the end of the day the problem is that non technical users don't fill in things properly. They have a space before a number so it gets treated as text. Or in the same column Excel spits out an integer as a float. Basically the things that a real database keeps in order for you, so unfortunately I don't see it solving my problems. Shame.
I was going to write something like this. From my experience parsing a lot of CSV files, what's really tough isn't parsing data out of a nicely formatted CSV file, but cleaning and correcting the junk that is written into it. Sometimes I'd have to pass 3 or 4 different "correctors" and checkers in order to get an acceptable CSV file.
I assume this is a common problem to be solved. Are there any best practices, or libraries, or techniques that I could look at to help.
At the moment I parse and update the parser every time a new error appears. Most of the time it seems to be excel presenting things to the user that appear correct when they are not.
I have even been tempted to write my own "data import grid". It would look like excel, have very little of the functionality, but could allow a developer to set rules on what can / can't be entered in each column.
That said, if you are dealing with excel, you can use Spreadsheet-ParseExcel and Spreadsheet-WriteExcel to have perl handle excel .xls files directly (though I'm not sure about xls files including embedded vba macros).
H2 SQL has this functionality as well. You can specify CSV file at table creation. It can even mix regular tables and CSV. And it has nice web interface build in.
91 comments
[ 4.1 ms ] story [ 187 ms ] thread+ is the SQL numeric addition operator. || is string concatenation.
textql imports your data into SQLite. In other words its a import utility.
Key differences: - sqlite import demands an on disk db, textql will use an in memory db if it can, increases performance - sqlite import will not accept stdin, breaking unix pipes. textql will happily do so.
I will make a note of this in the project readme, seems to crop up a lot.
http://www.postgresql.org/docs/current/interactive/file-fdw....
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 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.
lnav tries to push things a little further and extract data from unstructured log messages. For example, given the following message:
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.The authors get a loading throughput (including index creation!) of over 1.5 GB/S with multithreading. They don't seem to do any dirty tricks when loading the data as they immediately can run hundreds of thousands of queries per second on the data.
There also seem to be more interesting papers on the research project page http://www.hyper-db.de/index.html
[1] "Instant Loading for Main Memory Databases" http://www.vldb.org/pvldb/vol6/p1702-muehlbauer.pdf
http://www.steve.org.uk/Software/asql/
[1]http://samtools.sourceforge.net/tabix.shtml
Example: #Find all unique from col1 logparser -i:csv -o:csv -stats:off -dtlines:2000 -headers:off "select distinct col1 from input.csv" >out.csv
http://www.steve.org.uk/Software/asql/
Otherwise - already checking out, that's what I wanted for a while.
Nearly every sysadmin/firefigher I know has command line perl/awk/sed magic text files full of this.
Having said that I would appreciate if something like this was available for XMLs. Having to deal with SQL designs force fully shoe horned into NoSQL databases so often these days. I frequently run into situations where I have to query data from XML dumps. Most of the time, I have to invent a Perl one liner that essentially does the job of a select query.
30-second XPath pitch:
This gets the 'bar' children from every 'foo', regardless of where it occurs in the hierarchy. Gets everything like: <baz name="Alice">There's lots of more complicated things you can do, but these are the most frequent types of things I've had to do.
xml sel --template --value-of "//foo/bar" \ --template --value-of "//baz[@name='Alice']" << EOF <foo> <bar>hello</bar> <baz name="Alice"> world</baz> </foo> EOF
http://xmlstar.sourceforge.net/docs.php
It also can edit with XPath which is really handy.
XML/HTML support, via XPATH selectors, is coming. I would love to be able to pipe the output of curl through this!
At the end of the day the problem is that non technical users don't fill in things properly. They have a space before a number so it gets treated as text. Or in the same column Excel spits out an integer as a float. Basically the things that a real database keeps in order for you, so unfortunately I don't see it solving my problems. Shame.
At the moment I parse and update the parser every time a new error appears. Most of the time it seems to be excel presenting things to the user that appear correct when they are not.
I have even been tempted to write my own "data import grid". It would look like excel, have very little of the functionality, but could allow a developer to set rules on what can / can't be entered in each column.
That said, if you are dealing with excel, you can use Spreadsheet-ParseExcel and Spreadsheet-WriteExcel to have perl handle excel .xls files directly (though I'm not sure about xls files including embedded vba macros).