Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Q – Run SQL Directly on CSV or TSV Files

harelba.github.io

Post reply on HN
Print view View original

31–40 of 62 posts

Re: Q – Run SQL Directly on CSV or TSV Files

#31

Post by smartmic »

redsaz wrote: post #4

A satisfied user here. Found it very useful when tools like cut and sort weren't enough, usually when I need to do a join on two different tables (err, files). Left joins work, but I don't think right joins are supported. I've used this in combination with jq as well. I'll use jq to convert json to CSV, and then use SQL to do whatever else.

Depending on how complex the task is, I also jump from sort/join/awk/sed to the SQL train more often. But if I have already gone this step, then I would also like to have the whole SQL(ite) power and that would then but really blow up the command line. In such cases I usually write a TCL script, the integration of SQLite3 [0] is quasi native and besides the full SQLite3 functionality I also have flexible extensions (e.g. directly usable TCL procedures of any complexity) at my disposal. Tools like Q represent a middle ground, although they build on SQLite they remain behind in functionality [1]. But as long as I want to keep it simple on the command line while adhering to the UNIX philosophy, coreutils, sed, awk and possibly perl remain my best friends.

[0]: https://www.sqlite.org/tclsqlite.html [1]: http://harelba.github.io/q/#limitations

Re: Q – Run SQL Directly on CSV or TSV Files

#32

Post by aasasd »

bobivl wrote: post #30
redsaz wrote: post #4

A satisfied user here. Found it very useful when tools like cut and sort weren't enough, usually when I need to do a join on two different tables (err, files). Left joins work, but I don't think right joins are supported. I've used this in combination with jq as well. I'll use jq to convert json to CSV, and then use SQL to do whatever else.

Cut, sort, join and awk can be pretty powerful and fast. If it becomes too tedious to manually write them, you can also use BigBash [1] to convert a SQL query automatically to a one-liner that only use these tools to execute the query. [1] http://bigbash.it

Any experienced programmer learns to not use string processing on structured data, because that will bite them in the ass.

Meanwhile HN luddites: let me use awk, cut and whatnot despite the existence of an util that explicitly sidesteps this issue.

Re: Q – Run SQL Directly on CSV or TSV Files

#34

Post by gourabmi »

jmt_ wrote: post #24

You can do the same thing with csvsql from csvkit: https://towardsdatascience.com/analyze-csvs-with-sql-in-comm... Since csvkit comes with so many other tools, I'm not sure I see a reason to use q over csvsql

Big fan of csvsql here. I use it often to look at log files.

Re: Q – Run SQL Directly on CSV or TSV Files

#36

Post by eatonphil »

luzifer42 wrote: post #35

clickhouse-local is a powerful alternative. https://clickhouse.com/docs/en/operations/utilities/clickhou... https://news.ycombinator.com/item?id=31561780

It's fast. But it's also a 1-2gb binary. And its SQL implementation is work in progress and often makes up its own names for common functions.

If you can put up with both for an adhoc cli exploration tool then yeah it's incredible.

For analytics queries in general though (not talking about clickhouse-local) I don't think there's any OSS competition.

Re: Q – Run SQL Directly on CSV or TSV Files

#39

Post by joshe »

Temp DB's feel like a slightly underused technique too. (Not saying it's always better.)

Ie, just a quick script that adds a serial id as the first column. Then imports to postgres/mysql based on header names (column names) and file name (becomes table name) to a brand new db.

Usually DBs are so long lived and carefully designed that there's a bit of mental block to just importing trash data and dropping the whole database later. I'm always 15 mins into awk before i remember.

Also in postgres you can do it as a new schema in an existing database, and join with the existing data. Probably safest to not do that in production :-).

Like so: https://stackoverflow.com/questions/5712387/can-we-join-two-...

Then just drop the whole schema when you are done screwing around.

Re: Q – Run SQL Directly on CSV or TSV Files

#40

Post by dublin »

redsaz wrote: post #4

A satisfied user here. Found it very useful when tools like cut and sort weren't enough, usually when I need to do a join on two different tables (err, files). Left joins work, but I don't think right joins are supported. I've used this in combination with jq as well. I'll use jq to convert json to CSV, and then use SQL to do whatever else.

In addition to the usual cut/paste/sort/awk stuff, we've had really powerful "stream-operator" databases based on flat text data files for decades. They used to be somewhat slow. Not anymore, esp when running from RAMdisks.

One good one is Strozzi NoSQL (his use of the term NoSQL predates the current use of the term by many years...): http://www.strozzi.it/cgi-bin/CSA/tw7/I/en_US/NoSQL/Home%20P...

Starbase is another, with interesting extensions for astronomical work.

Linux Review article on the concept here: https://www.linuxjournal.com/article/3294

The article that started it all: http://www.linux.it/~carlos/nosql/4gl.ps

And there's even a book on the subject, centered on the /rdb implementation by the late RSW software. But I warn you, reading this WILL permanently change the way you think about databases: https://www.amazon.com/Relational-Database-Management-Prenti...

Post reply on HN