[HN Gopher] Q - Run SQL Directly on CSV or TSV Files
___________________________________________________________________
Q - Run SQL Directly on CSV or TSV Files
Author : thunderbong
Score : 100 points
Date : 2021-06-07 14:50 UTC (8 hours ago)
(HTM) web link (harelba.github.io)
(TXT) w3m dump (harelba.github.io)
| thesnide wrote:
| The implementation part is actually irrelevant.
|
| The nice part is that it is CLI, like jq(1).
|
| I did something similar a long time ago with DBD::CSV but lost
| the code. So I'm glad the idea resurfaced.
| xn wrote:
| When working with large csv files, you're better off importing
| the file into a sqlite database if you want to perform more
| than one query.
|
| Knowing the import is happening behind the scenes is useful
| when you hit performance problems with the tool.
| mechatroner wrote:
| Another one: RBQL (https://rbql.org/) - It doesn't use sqlite (or
| any other database) and provides either SQL/Python syntax or
| SQL/JavaScript syntax.
| nkcmr wrote:
| Yesss. Glad to see this getting some love. I use this tool very
| frequently in my day-to-day and my work would be so much harder
| without it.
|
| A lot of folks mentioning great projects/solutions that work the
| same but I love me some good unix piping action:
| cat data.csv | xsv select 1,3 | q 'select * from - where col1 !=
| "foo"'
|
| (xsv: also invaluable on the terminal:
| https://github.com/BurntSushi/xsv)
| SahAssar wrote:
| Is that any different than using sqlite3 directly like this?
| cat data.csv | sqlite3 -csv ':memory:' '.import /dev/stdin
| data' 'select col1, col3 from data where col1 != "foo"'
|
| You can of course use the .import command directly on the file,
| but keeping it there to show piping.
| borisvl wrote:
| If you want the best of two worlds, you can also give bigbash
| [1] a try. It generates a bash statement using unix tools from
| a sql query that can be run on csv files.
|
| [1] http://bigbash.it/
| dima55 wrote:
| And yet another set of tools is vnlog
| (https://github.com/dkogan/vnlog). It assumes you have
| whitespace-separated data, with a legend comment, then you can
| do the above thing with < data.vnl vnl-filter
| -p thiscolumn,thatcolumn 'thiscolumn != "foo"'
|
| If you don't strictly need csv, those tools are quite nice, and
| have a really friendly learning curve. Disclaimer: I'm the
| author
| nojito wrote:
| Even shorter with r and probably a good deal quicker
|
| r -e "data.table::fread(data.csv)[col1 != "foo", .(1,3)]"
| burntsushi wrote:
| I don't know anything about R and I couldn't get this to
| work. First, I had to install R. Then it told me that
| 'data.table' was unrecognized. So I searched around and
| installed that. Then, 'data.csv' is not quoted. So fixed
| that. So I tried running it on some data I have:
| > data.table::fread("version_downloads.csv")[date == "foo",
| .(1,3)] Error: character string is not in a standard
| unambiguous format Execution halted
|
| Dunno how to fix this now.
| splithalf wrote:
| Fix your strings. :)
| nojito wrote:
| Might be the datatypes. fread automagically tries to
| determine the datatypes of your columns by jumping around
| the file as it's being read.
|
| https://www.rdocumentation.org/packages/data.table/versions
| /...
|
| Or you can pass colClasses = 'character' as an argument to
| read everything as a string, but that will be much much
| slower.
|
| fread also reads from stdin so you can pass bash commands
| into the first parameter instead of using bash to run r via
| R -e.
| tacostakohashi wrote:
| Is that better than: cut -d, -f1,3 data.csv |
| grep -v '^foo'
|
| I'm sure there are some things where you need sql/q, but this
| isn't one of them, and there is a lot of utility in productive
| with bare POSIX tools.
| burntsushi wrote:
| If always splitting on ',' is correct for your data, then
| your solution is probably fine. You probably want '^foo,'
| though, otherwise yours includes things where 'foo' is just a
| prefix of the first column value.
|
| But CSV permits quoted fields that contain literal ','. So in
| that case, splitting on ',' would be incorrect.
| timeinput wrote:
| This breaks down when you have commas in your CSV data like
| if you had something like locations as the quoted string
| "Atlanta, GA"
| forgetfulness wrote:
| Expressing an 'or' or having any logic to the condition
| becomes quickly annoying with usual *nix tools, and most
| people use SQL much more often than they forget how to use
| sed and awk.
| e12e wrote:
| Why use xsv in this case (as opposed to SELECT col1, col3)?
| nkcmr wrote:
| Mostly for just demonstrating how it combines with other
| tools in my workflow over pipes :D
| zoomablemind wrote:
| Nicely thought out set of features!
|
| If I understand it correctly, the main value is the automating of
| SQLite import and datatype-assignment (vs affinity).
|
| Combined with ability to save the resulting db also makes this
| tool a useful import wizard. Just one needs to pay attention to
| csv-headers and backtick-enquote the column names which contain
| spaces (something which is not very commonly done in csv).
|
| I agree, picking a single-letter name for the utility should be
| rather left to user's own alias choice, if needed.
|
| A more descriptive name would better integrate within the already
| populous namespace.
| ageitgey wrote:
| This is neat. Thanks for sharing.
|
| Another great tool for working with csv files on the command line
| is the excellent VisiData - https://www.visidata.org/
|
| It lets you explore/browse/search all your csv/json/etc files
| super quickly and easily in an interactive command line tool,
| sort of like the old Norton Commander.
| dima55 wrote:
| That's excellent! Thanks for posting. I've seen most of the
| tools in this area, and visidata is a new one to me. And it
| actually does lots of things the others do not.
| zabzonk wrote:
| You probably don't want to be arsed fiddling with it, but the
| Windows ODBC driver supports access to CSV files via SQL queries.
| pjmlp wrote:
| Since Access exists actually.
| SahAssar wrote:
| IMO it would be nice to say outright that this uses sqlite under
| the hood instead of saying it supports "sqlite's syntax" and so
| on.
|
| It can be very useful, but this is a wrapper that constructs a
| sqlite db from CSV/TSV (similar to sqlite's '.import') and then
| runs SQL on it, right?
| bob1029 wrote:
| > [...] this is a wrapper that constructs a sqlite db from
| CSV/TSV (similar to sqlite's '.import') and then runs SQL on
| it, right?
|
| It appears so:
| https://github.com/harelba/q/blob/master/bin/q.py#L267
|
| Regardless, simple utilities that expose SQL interfaces over
| arbitrary data are still incredibly compelling & useful.
| cube2222 wrote:
| Looking at the comments I see there are more and more such
| projects, that's great! Declarative interfaces rule!
|
| Anyways, I too felt a void to be filled with tools like these, so
| I (and a couple of other folks) developed OctoSQL[0], check it
| out if you like this. It lets you query json, csv, and various
| databases using SQL, and also let's you join between them.
|
| It differs from most of the other tools in that it also supports
| streaming data sources using temporal SQL extensions. (Inspired
| by the great paper, One SQL To Rule Them All[1])
|
| [0]: https://github.com/cube2222/octosql
|
| [1]: https://arxiv.org/abs/1905.12133
| mslusarz wrote:
| Here's a similar project I've been working on in my spare time:
| https://github.com/mslusarz/csv-nix-tools. It supports both SQL
| syntax (SQLite and native) and Unix-like processing (grep, sort,
| uniq, etc). It also includes source (ls, ps, etc) and exporting
| (exec, to-json, plot) tools.
| jordan314 wrote:
| I use this to search for transactions in my bank transaction
| history for taxes. Very useful!
| qpiox wrote:
| Many SQL tools that use JDBC have support for CSV, TSV and other
| table-in-a-file data stores (e.g. DBF).
|
| I use DBeaver for nearly everything SQL. It is also open-source.
|
| Create a New Database connection, choose CSV, choose the folder
| with the CSV files, and you will get a database connection to a
| "database" where each CSV file is a table.
| vptr wrote:
| I think I've seen several similar projects to this one. It always
| make me wonder how are these different from sqlite virtual tables
| (https://www.sqlite.org/csv.html)? And why would I want to use
| this over sqlite? Perhaps some API in python? But then again I
| could just run sqlite script and dump results into temp csv and
| read them back in any other lang of my choice.
| cntlzw wrote:
| Where would the world be if we just would stop innovating after
| the first idea that works?
| nonameiguess wrote:
| There actually is a Python API for doing this:
| https://pypi.org/project/dataframe-sql/
|
| It doesn't expose a CLI, though.
| mikepurvis wrote:
| I think the value here probably depends a lot on what it's
| doing under the hood. Like, if this is just a wrapper on
| importing to sqlite, building up an index, and running a query,
| then maybe there aren't a lot of use-cases.
|
| But maybe it does something interesting with the fact that it
| can receive data in a stream?
| moogly wrote:
| I've used the similar LogParser[1] on Windows here and there bit
| over the years.
|
| [1]: https://en.wikipedia.org/wiki/Logparser
|
| On Windows, you can also just create an ODBC connection to any
| CSV file using the Text Driver, and use any ODBC-compatible
| toolchain you have available.
| elephantum wrote:
| For completeness sake, I would like to mention, that Apache Drill
| works with local files and can be used for exactly the same
| purpose.
| nezirus wrote:
| Postgres too https://www.postgresql.org/docs/current/file-
| fdw.html
| jamaicahest wrote:
| This sounds similar to Amazon Athena, but without the Amazon
| part. Interesting!
| scanr wrote:
| q is awesome. Initially I wasn't sure what the underlying engine
| was so was amazed at how good the sql support was. Turns out it
| feeds all the data into sqlite under the covers so you can do
| some pretty complex queries against arbitrary csv files in a one
| liner.
| hexo wrote:
| This is Q:
| https://en.wikipedia.org/wiki/Q_(programming_language_from_K...
| And this, sort of, too:
| https://en.wikipedia.org/wiki/Pure_(programming_language)
|
| So, how many Q's do we have now?
| jcla1 wrote:
| Not to be confused with the also very SQL-like Q language[1] from
| Kx Systems/Arthur Whitney.
|
| [1]:
| https://en.wikipedia.org/wiki/Q_(programming_language_from_K...
| maest wrote:
| Indeed they're very similar (including the name and the
| documentation layout!) so I was very confused for a bit.
| 29athrowaway wrote:
| Worst name for a proyect in 2021.
| danso wrote:
| FWIW, csvkit has similar functionality in its csvsql tool:
|
| https://csvkit.readthedocs.io/en/latest/scripts/csvsql.html
| csvsql --query \ "select avg(i.sepal_length) from
| iris as i join irismeta as m on (i.species = m.species)" \
| examples/iris.csv examples/irismeta.csv
|
| I believe it creates an in-memory SQLite database of the CSV,
| then executes the query and produces the CSV result.
|
| edit: Looking through q's docs, I see that it also uses Python
| and SQLite in-memory databases:
|
| http://harelba.github.io/q/#implementation
|
| > _The current implementation is written in Python using an in-
| memory database, in order to prevent the need for external
| dependencies._
|
| I'm confused though that, according to its Limitations[0]
| section, it doesn't support CTE's or `SELECT * FROM <subquery>`,
| when both of those things are supported by the SQLite standard?
|
| [0] http://harelba.github.io/q/#limitations
| nathanwallace wrote:
| If you like SQL interfaces then please also checkout Steampipe
| (https://steampipe.io).
|
| Use SQL to instantly query cloud resources like AWS, GitHub,
| Yahoo Finance, etc (no CSV yet). It's written in Go and uses
| Postgres Foreign Data Wrappers (similar to SQLite virtual
| tables).
|
| Disclaimer: It's open source. I'm a lead on the project.
| res0nat0r wrote:
| This looks awesome. Thanks for sharing.
| andylynch wrote:
| Looks useful and well done, but the choice of name is unfortunate
| since it clashes hard with the Q language and the q-sql functions
| it implements (and also given some Q users would definitely find
| this interesting)
| cpr wrote:
| Even worse, might confuse us Q followers. ;-)
| houqp wrote:
| Shameless plug, I also built a tool in Rust to provide
| SQL/GraphQL query access to CSV and many other tabular file
| formats: https://github.com/roapi/roapi.
| isoprophlex wrote:
| Not to diss the author or chime in with the umpteenth "this is
| easy why did you spend time on this"...
|
| A postgres db can query from external files as well. See:
|
| https://www.postgresql.org/docs/13/file-fdw.html
| LegitShady wrote:
| The major difference is seemingly that one is a database
| installation and the other is just a command line tool. What if
| I don't have a Postgres DB, but I have some tabular data in a
| csv I want to query? Should I set up a database to query a
| file?
|
| I'm not sure why I would set up a database to run a query on a
| file when I can do it from the command line.
| zokier wrote:
| Well, running postgres doesn't really need to be much more
| than `docker run --rm -v $PWD:/data -e
| POSTGRES_HOST_AUTH_METHOD=trust postgres`. While docker might
| have its downsides, it is convenient for this sort of ad-hoc
| stuff.
| desktopninja wrote:
| In days gone by, MS Logparser did the trick:
| https://docs.microsoft.com/en-us/previous-versions/windows/i...
___________________________________________________________________
(page generated 2021-06-07 23:01 UTC)