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

121–130 of 131 posts

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

#121

Post by electroly »

petercooper wrote: post #119
electroly wrote: post #9

Earlier quoted context omitted.

I like the virtual table API a lot but it has some serious drawbacks. You don't need to know much and indeed, you can't know much about the execution engine, even if that knowledge would help you. Many parts of the query are not pushed down into the virtual table. For instance, if the user query is: SELECT COUNT(*) FROM my_vtab; ... the query your virtual table will effectively see is: SELECT * FROM my_vtab; SQLite d…

I wrote a module that exposes remote SQL Server/PostgreSQL/MySQL servers as SQLite virtual tables, and joins basically don't work at all if your server is not on your local network. There's nothing I can do about it (other than heuristically guessing what IDs might be coming and request them ahead of time) because SQLite doesn't provide enough information to the virtual table layer. Is it possible to wait until all o…

Unfortunately it's synchronous. The interface requires you to return the rows for the first key before it gives you the second one, and so on.

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

#122

Post by pstuart »

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

Dang, that's a bummer -- thanks for the intel, as well as the article / Parquet code.

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

#123

Post by adamgordonbell »

simonw wrote: post #42
adamgordonbell wrote: post #37

I've become a fan of using SQLite-utils to work with CSV or JSON files. It's a two step process though. One to create and insert into a DB and a second to select from and return. https://sqlite-utils.datasette.io/en/stable/index.html

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/

Very cool!

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

#124

Post by mattewong »

AdrenalinMd wrote: post #97
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/

It's nice that q has caching.... then again, it kind of needs it to solve its performance inefficiency.

Running "select *" on a 1mm-row worldcitiespop_mil file, q takes 27 seconds compared to `zsv sql` which takes 1.7 seconds ( https://github.com/liquidaty/zsv ) and also supports multiple file joins. I'm sure q is faster once cached, but taking a 16x performance hit up-front is not for me

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

#125

Post by michaelcampbell »

zrail wrote: post #28
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.

The initial release of sqlite was in 2000. Yes, well after GNU-style command line options existed but not by much.

> well after ... but not by much.

This seems a contradiction to me. Was it "well after", or after "but not by much"?

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

#126

Post by michaelcampbell »

wenc wrote: post #11
dotancohen wrote: post #4

Lately I've been using Visidata for any text file that looks like a table or other squarish data source, including JSON. https://www.visidata.org/

Visidata is wonderful. Also for querying large CSV and Parquet files, I use DuckDB. It has a vectorized engine and is super fast. It can also query SQLite files directly. The SQL support is outstanding. https://duckdb.org/ Just have start the DuckDB REPL and start querying e.g. Select * from ‘bob.CSV’ a Join ‘Mary.parquet’ b On a.Id = b.Id Zips through multi GB files in a few seconds.

Is the capitalized keywords a requirement? ew (IMO)

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

#127

Post by wenc »

michaelcampbell wrote: post #126
wenc wrote: post #11

Earlier quoted context omitted.

Visidata is wonderful. Also for querying large CSV and Parquet files, I use DuckDB. It has a vectorized engine and is super fast. It can also query SQLite files directly. The SQL support is outstanding. https://duckdb.org/ Just have start the DuckDB REPL and start querying e.g. Select * from ‘bob.CSV’ a Join ‘Mary.parquet’ b On a.Id = b.Id Zips through multi GB files in a few seconds.

Is the capitalized keywords a requirement? ew (IMO)

No, that’s an artifact of me typing on my phone.

Also if you know SQL you’ll know that SQL keywords are case insensitive in most DBMSes.

Don’t be too quick to ew.

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

#128

Post by westurner »

mattewong wrote: post #110
westurner wrote: post #108

Earlier quoted context omitted.

What are the differences between the zsv and csv parsers? Is csvw with linked data URIs also doable?

Not sure what you mean by csvw. But, zsvlib is a CSV parser, and zsv is a CLI that uses zsvlib. zsv also uses the sqlite3 vtable based on the example in the original sqlite3 code, but it modifies it to use the zsvlib parser instead of the original CSV parser. The zsv parser is different from most CSV parsers in how it uses SIMD operations and minimizes memory copying. zsvlib parses CSV based on the same spec that Exc…

zsvlib does SIMD CSV like simdjson? Sweet.

Zsvlib on cloudfuzz would be good if that's not already

Yeah linked data schema support is distinct from the parser primitives and xsd data type uris, for example.

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

#129

Post by michaelcampbell »

wenc wrote: post #127
michaelcampbell wrote: post #126

Earlier quoted context omitted.

Is the capitalized keywords a requirement? ew (IMO)

No, that’s an artifact of me typing on my phone. Also if you know SQL you’ll know that SQL keywords are case insensitive in most DBMSes. Don’t be too quick to ew.

I was ew'ing at capitalized keywords, whether or not they're required. I'll be as quick as I like, thanks.

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

#130

Post by wenc »

michaelcampbell wrote: post #129
wenc wrote: post #127

Earlier quoted context omitted.

No, that’s an artifact of me typing on my phone. Also if you know SQL you’ll know that SQL keywords are case insensitive in most DBMSes. Don’t be too quick to ew.

I was ew'ing at capitalized keywords, whether or not they're required. I'll be as quick as I like, thanks.

Yes and they’re not a requirement. Expressing premature disgust without investigation leads to low quality comments. You can do it but it goes against an ethos of curiosity and unfairly diminishes the object of discussion.
Post reply on HN