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

One-liner for running queries against CSV files with SQLite

til.simonwillison.net

Post reply on HN
Print view View original

91–100 of 131 posts

Re: One-liner for running queries against CSV files with SQLite

#91

Post by ketralnis »

_joel wrote: post #7
throwaway892238 wrote: post #5

sqlite3 :memory: -cmd '.mode csv' ... It should be a war crime for programs in 2022 to use non-UNIX/non-GNU style command line options. Add it to the Rome Statute's Article 7 list of crimes against humanity. Full blown tribunal at The Hague presided over by the international criminal court. Punishable by having to use Visual Basic 3.0 for all programming for the rest of their life.

Would :memory: even parse in some shells?

Why wouldn't it? I'm not aware of colons being a special character in any shell I can think of

Re: One-liner for running queries against CSV files with SQLite

#92

Post by adren67 »

Miller is also a great tool to do computation, transformations on CSV/TSV/JSON/SQLite files...

Here is the way to pretty-print the same result with mlr:

mlr --icsv --opprint --barred stats1 -a count,mean -f total_amount -g passenger_count then sort -f passenger_count taxi.csv

Re: One-liner for running queries against CSV files with SQLite

#93

Post by simonw »

eatonphil wrote: post #78
simonw wrote: post #42

Earlier quoted context omitted.

I added a feature last year that lets you do this as a one-step process - "sqlite-utils memory": https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/

Those steps still exist though surely, just managed by the one command. If you're querying via SQLite you have to ingest first.

Yup - 'sqlite-utils memory' works by creating an in-memory database, importing various different file formats into it, running the query and then throwing everything away again. It's generally not a good fit for anything over a few dozen MBs of data.

Re: One-liner for running queries against CSV files with SQLite

#96

Post by gpvos »

gigatexal wrote: post #79

I’ve been doing this. But I hate it. CSVs need to die. They’re terrible data formats. But here we are. And SQLlite makes things amazing.

I'm willing to bet CSV will still be around in 200 years. It's ugly, but exceedingly effective.

Re: One-liner for running queries against CSV files with SQLite

#97

Post by AdrenalinMd »

alana314 wrote: post #87

I like using q for querying CSVs on the command line: https://github.com/harelba/q

Same. And I believe q uses sqlite under hood, so you can use the same SQL syntax as the one supported by sqlite. Joining multiple csv files is also possible without too much setup. http://harelba.github.io/q/

Re: One-liner for running queries against CSV files with SQLite

#98

Post by wizofaus »

gpvos wrote: post #96
gigatexal wrote: post #79

I’ve been doing this. But I hate it. CSVs need to die. They’re terrible data formats. But here we are. And SQLlite makes things amazing.

I'm willing to bet CSV will still be around in 200 years. It's ugly, but exceedingly effective.

I wouldn't even assume the concept of text files as we know them today will still exist in any meaningful way in 100 years. It's just as likely all digital data will be stored in something like neutral networks with no obvious textual representation. But yes, CSV has had remarkable persistence (the most recent major feature addition I made to our product had to read from a supposedly modern web API response that was in CSV format, despite all the other endpoints returning JSON).

Re: One-liner for running queries against CSV files with SQLite

#99

Post by mattewong »

cldellow wrote: post #41
pstuart wrote: post #19

Earlier quoted context omitted.

And in fact there is a CSV virtual table available from SQLite but it's not built in the normal client: https://www.sqlite.org/csv.html It really should be, as the code is tiny and this functionality is not overly exotic.

In my experience [1], the CSV virtual table was really slow. Doing some analysis on a 1,291 MB file, a query took 24.7 seconds using the virtual table vs 3.4 seconds if you imported the file first. The CSV virtual table source code is a good pedagogical tool for teaching how to build a virtual table, though. [1]: https://cldellow.com/2018/06/22/sqlite-parquet-vtable.html

https://github.com/liquidaty/zsv/blob/main/app/external/sqli... modifies the sqlite3 virtual table engine to use the faster zsv parser. have not quantified the difference, but in all tests I have run, `zsv sql` runs faster (sometimes much faster) than other sqlite3-on-CSV solutions mentioned in this entire discussion (unless you include those that cache their indexes and then measure against a post-cached query). Disclaimer: I'm the main zsv author

Re: One-liner for running queries against CSV files with SQLite

#100

Post by eatonphil »

zX41ZdbW wrote: post #82
eatonphil wrote: post #76

Earlier quoted context omitted.

Clickhouse-local is incredible. It does the best of any similar tool I've benchmarked. But the reason I took it out of the linked benchmarks in OP's post is because it's 2+GB. That's a massive binary. It's the whole server. I'm not sure you want to be distributing this all over the place in general. It's just not in the same category IMO. Disclaimer: I build another tool that does similar things.

It is shipped with debug info and symbol tables just in case. Without debug info, it will be 350 MB, and compressed can fit in 50 MB: https://github.com/ClickHouse/ClickHouse/issues/29378 It is definitely a worth improvement.

Aha, thanks for the clarification and link. I'll be following that issue.
Post reply on HN