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

41–50 of 131 posts

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

#41

Post by cldellow »

pstuart wrote: post #19
rgovostes wrote: post #2

SQLite's virtual table API ( https://www.sqlite.org/vtab.html ) makes it possible to access other data structures through the query engine. You don't need to know much if anything about how the database engine executes queries, you only need to implement the callbacks it needs to do its job. A few years ago I wrote an extension to let me search through serialized Protobufs which were stored as blobs in a regular data…

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

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

#42

Post by simonw »

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/

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

#44

Post by cldellow »

simonw wrote: post #27
Uptrenda wrote: post #16

I'm looking through this guys website for 'today I learned' and at first I'm impressed by how many of them there are. But then I start thinking: when you're trying to solve a problem you search for a lot of data. None of his posts are attributed. He's getting all his information from somewhere and then he goes and posts these articles just ripping off other sources. I can understand when its based on your original wo…

You should look harder! I attribute in plenty of these pieces, where appropriate. Here's a query showing the 23 posts that link to StackOverflow, for example: https://til.simonwillison.net/tils?sql=select+*+from+til+whe... And 41 where I credit someone on Twitter: https://til.simonwillison.net/tils?sql=select+*+from+til+whe... More commonly I'll include a link from the TIL back to a GitHub Issue thread where I figure…

I tried to reproduce the OP's complaint. Of the 5 most recent TILs, only 1 did not reference some other source as inspiration. One literally gave "thanks" to tips obtained elsewhere.

I'm offended on your behalf! :)

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

#45

Post by mytherin »

simonw wrote: post #39
mytherin wrote: post #38

Using DuckDB [1]: duckdb -c "SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi.csv GROUP BY ALL" DuckDB will automatically infer you are reading a CSV file from the extension, then automatically infer column names from the header, together with various CSV properties (data types, delimiter, quote type, etc). You don't even need to quote the table name as long as the file is in your current directory and t…

How does the column data type inference work? I've run into that challenge myself in the past.

The CSV auto-detector was implemented by Till Döhmen, who did his master thesis on the subject [1] and has actually written a paper about it [2].

Essentially we have a list of candidate types for each column (starting with all types). We then sample a number of tuples from various parts of the file, and progressively reduce the number of candidate types as we detect conflicts. We then take the most restrictive type from the remaining set of types, with `STRING` as a last resort in case we cannot convert to any other type. After we have figured out the types, we start the actual parsing.

Note that it is possible we can end up with incorrect types in certain edge cases, e.g. if you have a column that has only numbers besides one row that is a string. If that row is not present in the sampling an error will be thrown and the user will need to override the type inference manually. This is generally rather rare, however.

You could also use DuckDB to do your type-inference for you!

  duckdb -c "DESCRIBE SELECT * FROM taxi.csv"
And if you want to change the sample size:

  duckdb -c "DESCRIBE SELECT * FROM read_csv_auto('taxi.csv', sample_size=9999999999999)"
[1] https://homepages.cwi.nl/~boncz/msc/2016-Doehmen.pdf

[2] https://ir.cwi.nl/pub/26416/SSDM1111.pdf

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

#46

Post by samstave »

flusteredBias wrote: post #32

I am a data scientists. I have used a lot of tools/libraries to interact with data. SQLite is my favorite. It is hard to beat the syntax/grammar. Also, when I use SQLite I do not output using column mode. I pipe to `tv` (tidy-viewer) to get a pretty output. https://github.com/alexhallam/tv transparency: I am the dev of this utility

No post body was provided.

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

#47

Post by flusteredBias »

flusteredBias wrote: post #32

I am a data scientists. I have used a lot of tools/libraries to interact with data. SQLite is my favorite. It is hard to beat the syntax/grammar. Also, when I use SQLite I do not output using column mode. I pipe to `tv` (tidy-viewer) to get a pretty output. https://github.com/alexhallam/tv transparency: I am the dev of this utility

Here is an example of how I would pipe with headers to `tv`.

sqlite3 :memory: -csv -header -cmd '.import taxi.csv taxi' 'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count' | tv

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

#48

Post by queuebert »

flusteredBias wrote: post #32

I am a data scientists. I have used a lot of tools/libraries to interact with data. SQLite is my favorite. It is hard to beat the syntax/grammar. Also, when I use SQLite I do not output using column mode. I pipe to `tv` (tidy-viewer) to get a pretty output. https://github.com/alexhallam/tv transparency: I am the dev of this utility

What a nice tool. I love how Rust has reinvigorated command line utilities.

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

#49

Post by flusteredBias »

samstave wrote: post #46
flusteredBias wrote: post #32

I am a data scientists. I have used a lot of tools/libraries to interact with data. SQLite is my favorite. It is hard to beat the syntax/grammar. Also, when I use SQLite I do not output using column mode. I pipe to `tv` (tidy-viewer) to get a pretty output. https://github.com/alexhallam/tv transparency: I am the dev of this utility

Quoted post unavailable.

https://github.com/alexhallam/tv#inspiration
Post reply on HN