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

71–80 of 131 posts

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

#71

Post by ehvatum »

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.

We can’t pay for anything that doesn’t have Jira integration, sorry!

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

#72

Post by simonw »

eli wrote: post #53

the .import command used for actually loading the CSV is kinda picky about your CSVs being well-formatted. I don't think it supports embedded newlines at all.

I just tested it against a CSV file with newlines that were wrapped in double quotes and it worked correctly. I used this CSV file: https://til.simonwillison.net/tils/til.csv?_stream=on&_size=...

And this query:

    sqlite3 :memory: -cmd '.mode csv' -cmd '.import til.csv til' \
      -cmd '.mode json' 'select * from til limit 1' | jq

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

#73

Post by hinkley »

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.

22 years is a long time to deprecate bad command line arguments.

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

#74

Post by lenwood »

valw wrote: post #20

Btw, am I alone in thinking that DataFrame abstractions in OOP languages (like Pandas in Python) are oftentimes simply inferior to relational algebra? I'm not sure that many Data Scientists are aware of the expressive power of SQL.

Agree. I've completed data pipelines for several projects and have found that the cleanest, and often fastest solution is to use SQL to structure the data as needed. This is anecdotal and I'm not an expert with SQL, but I haven't come across a situation where R or Pandas dataframes worked better than a well written query for data manipulation. This has the benefit of simplifying collaboration across teams because within my company not everyone uses the same toolset for analysis, but we all have access to the same database. Other tools are better suited to analysis or expansion of the data with input from other sources, but within our own data SQL wins.

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

#75

Post by lazypenguin »

electroly wrote: post #9
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…

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…

You also can’t add new columns using alter statements. I really like virtual tables in SQLite but It would be nice if documentation included some limitations and reasons not to use.

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

#76

Post by eatonphil »

avogar wrote: post #33

Using ClickHouse you can also process local files in one line using clickhouse-local command tool. And it will look a lot easier: clickhouse local -q "SELECT passenger_count, COUNT(*), AVG(total_amount) FROM file(taxi.csv, 'CSVWithNames') GROUP BY passenger_count" And ClickHouse supports a lot of different file formats both for import and export (you can see all of them here https://clickhouse.com/docs/en/interfaces/…

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.

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

#77

Post by eatonphil »

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

Heads up: cube2222 is the original author of this benchmark. :) I copied it and Simon copied my copy of it.

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

#78

Post by eatonphil »

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/

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

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

#80

Post by valw »

Myrmornis wrote: post #62
valw wrote: post #29

Earlier quoted context omitted.

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

I absolutely agree - one of the biggest shortcomings of SQL is that its primary programming interface is based on text and intended for human, instead of being based on data structures and intended for programs.
Post reply on HN