I use R for this.
Can you show an example of how to use R to read tabular data on stdin (e.g. in a UNIX pipe line) and perform SQL queries?
Why would you be running SQL queries in R, a language with built-in support for handling tabular data?
TextQL: Execute SQL Against CSV or TSV
61–70 of 95 posts
Re: TextQL: Execute SQL Against CSV or TSV
#62Earlier quoted context omitted.
What you describe would be a super nice utility. Great for prototyping and development, as the code could be copy & pasted from such a tool into application code. The streaming would make it memory efficient, and possibly able to handle some big data - maybe not true "Big Data", but certainly 10s of gigabytes. Anyone want to take this idea into a GoFundMe site?
SQL is set-oriented. How would that work on a potentially indefinite stream, other than as a simple filter which you could just do with a tool such as awk.
Many relational operations don't require a whole stream to compute, and many of those that do don't need it all at once.
Projection (mapping), a join against a fully loaded other side as well as filtering work.
Aggregation can consume an indefinite stream with limited working set if the cardinality of the grouping key isn't large.
And of course you can combine these in nested and unioned operations, computing across multiple indefinite streams concurrently and with limited working set.
It would be tricky to make work effecively without hinting for things like joins, for sure; join order is one of the hardest bits a query engine optimizes.
Re: TextQL: Execute SQL Against CSV or TSV
#63If you work with CSV a lot, then also check out xsv, "a command line program for indexing, slicing, analyzing, splitting and joining CSV files", from Andrew Gallant (burntsushi of Go and Rust fame): https://github.com/BurntSushi/xsv
And check out "Motivation" in xsv's readme (at the end) to understand why it might be a better choice than most other tools suggested here, particularly those that go through Python and sqlight.
Also, while I like/applaud Miller (also referenced), xsv has a simpler UI that only handles CSV.
Re: TextQL: Execute SQL Against CSV or TSV
#64Earlier quoted context omitted.
From the README: Key differences between textql and sqlite importing sqlite import will not accept stdin, breaking unix pipes. textql will happily do so. textql supports quote escaped delimiters, sqlite does not. textql leverages the sqlite in memory database feature as much as possible and only touches disk if asked.
But it is SQLite under the covers? A better title then would be an SQLite wrapper for use in shell pipelines.
In this case, It’s what it does that matters, not how it does it. I don’t care if SQLLite is powering it. Maybe the author would want to swap out the SQLLite engine with something else in the future too. Then it’s even better that they didn’t advertise the link to SQLLite as a selling point.
Re: TextQL: Execute SQL Against CSV or TSV
#65Earlier quoted context omitted.
What you describe would be a super nice utility. Great for prototyping and development, as the code could be copy & pasted from such a tool into application code. The streaming would make it memory efficient, and possibly able to handle some big data - maybe not true "Big Data", but certainly 10s of gigabytes. Anyone want to take this idea into a GoFundMe site?
SQL is set-oriented. How would that work on a potentially indefinite stream, other than as a simple filter which you could just do with a tool such as awk.
I think this might be helpful context:
Re: TextQL: Execute SQL Against CSV or TSV
#66Looks pretty good!
Just for historical interest, similar things have been done before.
e.g.http://quisp.sourceforge.net/shsqlhome.html
See also Facebook's osquery of course. Turns even complex structures into sql tables -- helped by Augeas.net.
Re: TextQL: Execute SQL Against CSV or TSV
#67Does this have any benefits over lnav[1]?
[1] https://www.lnav.org -- a feature-rich, powerful, flexible "mini-ETL" with an embedded sqlite engine -- is one of my all-time favorite CLI tools that somehow remains under the radar
Re: TextQL: Execute SQL Against CSV or TSV
#68PostgreSQL does this out of the box with : CREATE EXTENSION file_fdw; CREATE SERVER import FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE foo ( col1 text, col2 text, ... ) SERVER import OPTIONS ( filename '/path/to/foo.csv', format 'csv' ); SELECT col1 FROM foo WHERE col2='x';
Having to define the schema is the major hangup for me in my workflow. I would prefer a wrapper that could run within unix pipelines around arbitrary text data files, but they all use SQLite. I personally have been using harelba’s q (the un-googleable utility), which is just fine. It would be great not to shift gears into SQLite syntax and date formatting all the time. Does anyone know of a similar tool that runs ove…
> "prefer a wrapper that could run within unix pipelines around arbitrary text data files"
That sounds like [lnav](https://www.lnav.org).
For queries, it does use SQLite under the hood, but before querying your "arbitrary text data" files, you can use regex to define a custom format (eg with named groups and back references), providing structure against which standard SQL is an ideal tool to query.
It's simpler than I'm probably making it sound. Highly recommended.
Re: TextQL: Execute SQL Against CSV or TSV
#69Earlier quoted context omitted.
It's discussed in the README: https://github.com/dinedal/textql#key-differences-between-te... > sqlite import will not accept stdin, breaking unix pipes. textql will happily do so. > textql supports quote escaped delimiters, sqlite does not. > textql leverages the sqlite in memory database feature as much as possible and only touches disk if asked.
#!/usr/bin/env rc row_headers='' output_mode=column fn usage { echo $0 usage echo -o '#' which output mode to use echo -r '#' if present, output row headers echo -h '#' this message } while(~ $1 -*) { switch($1) { case -o output_mode=$2 shift case -r row_headers='.headers on' case -h usage exit 0 case * echo Bad args: $* usage exit 1 } shift } test $#* -ne 1 && echo Wrong number of arguments && usage && exit 1 sql_st…
Erm, people don't have rc installed though. I had completely forgotten it existed, to be honest.
Re: TextQL: Execute SQL Against CSV or TSV
#70Earlier quoted context omitted.
But it is SQLite under the covers? A better title then would be an SQLite wrapper for use in shell pipelines.
In this case, It’s what it does that matters, not how it does it. I don’t care if SQLLite is powering it. Maybe the author would want to swap out the SQLLite engine with something else in the future too. Then it’s even better that they didn’t advertise the link to SQLLite as a selling point.
It does actually matter what's running under, so you know what to expect from its behavior and if one doesn't exactly need the few features textQL offers exclusively, you would think to just use SQLite without random layers on top.