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

61–70 of 131 posts

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

#61

Post by mytherin »

simonw wrote: post #55
mytherin wrote: post #45

Earlier quoted context omitted.

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 f…

This is fantastic, thanks. My solution is a lot less smart - I loop through every record and keep track of which potential types I've seen for each column: https://sqlite-utils.datasette.io/en/latest/python-api.html#... Implementation here: https://github.com/simonw/sqlite-utils/blob/3fbe8a784cc2f3fa...

That works and is similar to what DuckDB does for small CSV files. We have the sampling step primarily for larger CSV files, when you might not want to do two passes over the file. This way we can keep the benefits of streamed file reading while offering type inference that "just works" most of the time without a major performance penalty.

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

#62

Post by Myrmornis »

valw wrote: post #29
devin-petersohn wrote: post #26

Earlier quoted context omitted.

There are loads of things that are not possible or are very cumbersome to write in SQL, but that pandas and many other dataframe systems allow. Examples are dropping null values based on some threshold, one-hot encoding, covariance, and certain data cleaning operations. These are possible in SQL but very cumbersome to write. There are also things that are outright impossible in a relational database related to metada…

Thanks for sharing this. I believe we essentially agree: chaining method calls is inexpressive compared to composing expressions in an algebraic language.

I'm not defending Pandas but just want to point out that the inability to conveniently compose expressions is one of the biggest problems with SQL, since it was designed to be written as a sort of pseudo-English natural language, in an era when people imagined that it would be used by non-programmers. To be clear, that's a problem with SQL, not with the idea of a language based on relational algebra. There are various attempts to create SQL-alternatives which behave like real programming languages in terms of e.g. composability. This blog post makes the point better than I can:

https://opensource.googleblog.com/2021/04/logica-organizing-...

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

#63

Post by jwilk »

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/

Beware that visidata phones home by default:

https://github.com/saulpw/visidata/discussions/940

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

#64

Post by ramraj07 »

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

Just want to add that snowflake (imo) is better. You don’t have to suffer SQLite’s lack of data types and honestly snowflake is the best tool to work with messy data.

Just fyi you can set up a snowflake account with a minimum monthly fee of 25 bucks. It’ll be very hard to actually use 25 bucks if your data isn’t in 100s of GBs and you literally use as little compute as is needed so it’s perfect.

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

#66

Post by humanistbot »

ramraj07 wrote: post #64
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

Just want to add that snowflake (imo) is better. You don’t have to suffer SQLite’s lack of data types and honestly snowflake is the best tool to work with messy data. Just fyi you can set up a snowflake account with a minimum monthly fee of 25 bucks. It’ll be very hard to actually use 25 bucks if your data isn’t in 100s of GBs and you literally use as little compute as is needed so it’s perfect.

This is in no way a relevant good-faith reply. It is spam. A web-based cloud-based data analytics platform isn't in the same category as piping command-line programs together.

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

#67

Post by cube2222 »

elmomle wrote: post #58
cube2222 wrote: post #40

Since many people are sharing one-liners with various tools... OctoSQL[0]: octosql 'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi.csv GROUP BY passenger_count' It also infers everything automatically and typechecks your query for errors. You can use it with csv, json, parquet but also Postgres, MySQL, etc. All in a single query! [0]: https://github.com/cube2222/octosql Disclaimer: author of OctoSQL

I love the simplicity. Is there support for joins / use of multiple tables?

Yes, certainly! And those multiple tables can come from different data sources, so files or databases.

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

#68

Post by madacol »

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've been following you for some months now, and you always put links of your sources.

So please, feel 100% free to ignore that person

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

#70

Post by simonw »

kitd wrote: post #3

I had to do something very similar for analysing CVE information recently, but I don't remember having to use the :memory: option. I suspect it defaults to that if no .db file is specified. Slightly tangentially, when doing aggregated queries, SQLite has a very useful group_concat(..., ',') function that will concatenate the expression in the first arg for each row in the group, separated by the separator in the 2nd…

I just tried it without :memory: and it dropped me into the SQLite shell without executing the query:

    % sqlite3 -cmd '.mode csv' -cmd '.import taxi.csv taxi' \
      'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'
    SQLite version 3.36.0 2021-06-18 18:58:49
    Enter ".help" for usage hints.
    sqlite>
Post reply on HN