One-liner for running queries against CSV files with SQLite • Hacker News Board

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

#101

Post by aargh_aargh » Tue, Jun 21, 2022, 8:15 PM UTC

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

Hi cube2222!

Just today I tried octosql for the first time when I wanted to correlate a handful of CSV files based on identifiers present in roughly equal form. Great idea but I immediately ran into many rough edges in what I think was a simple use case. Here are my random observations.

Missing FULL JOIN (this was a dealbreaker for me). LEFT/RIGHT join gave me "panic: implement me".

It took me a while to figure out how to quote CSV column names with non-ASCII characters and spaces. It's not documented as far as I've seen (please document quoting rules). This worked:

  octosql 'SELECT `tablename.Mötley Crüe` FROM tablename.csv'

replace() is documented [1] as replace(old, new, text) but actually is replace(text, old, new) just like in postgres and mysql.

index() is documented [1] as index(substring, text)

  (postgresql equivalent: position ( substring text IN string text ) → integer)
  octosql "SELECT index('y', 'Mötley Crüe')"
  Error: couldn't parse query: invalid argument syntax error at position 13 near 'index'
  octosql "SELECT index('Mötley Crüe', 'y')"
  Error: couldn't parse query: invalid argument syntax error at position 13 near 'index'

Hope this helps and I wish you all the best.

[1] https://github.com/cube2222/octosql/wiki/Function-Documentat...

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

#102

Post by cube2222 » Tue, Jun 21, 2022, 8:50 PM UTC

aargh_aargh wrote: post #101

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

Hi cube2222! Just today I tried octosql for the first time when I wanted to correlate a handful of CSV files based on identifiers present in roughly equal form. Great idea but I immediately ran into many rough edges in what I think was a simple use case. Here are my random observations. Missing FULL JOIN (this was a dealbreaker for me). LEFT/RIGHT join gave me "panic: implement me". It took me a while to figure out h…

Hey!

Thanks a lot for this writeup!

> but I immediately ran into many rough edges

OctoSQL is definitely not in a stable state yet, so depending on the use case, there definitely are rough edges and occasional regressions.

> Missing FULL JOIN (this was a dealbreaker for me). LEFT/RIGHT join gave me "panic: implement me".

Indeed, right now only inner join is implemented. The others should be available soon.

> replace() is documented [1] as replace(old, new, text) but actually is replace(text, old, new) just like in postgres and mysql. index() is documented [1] as index(substring, text)

I've removed the offending docs pages, they were documenting a very old version of OctoSQL. The way to browse the available functions right now is built-in to OctoSQL:

  octosql "SELECT * FROM docs.functions"

> invalid argument syntax error at position 13 near 'index'

Looks like a parser issue which I can indeed replicate, will look into it.

Thanks again, cheers!

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

#103

Post by krylon » Tue, Jun 21, 2022, 8:56 PM UTC

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.

CSV sucks, yes, but for moving/exchanging/aggregating data between various independent sources, it's the least terrible option everyone can process easily.

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

#104

Post by beaugunderson » Tue, Jun 21, 2022, 9:24 PM UTC

snidane wrote: post #54

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…

A bit clunky, but works. ps | awk '$1=$1' OFS=, | duckdb :memory: "select PID,TTY,TIME from read_csv_auto('/dev/stdin')"

can't figure out how to make this work in bash; it just prints out "select PID,TTY,TIME from read_csv_auto('/dev/stdin')" but split into columns... using cli v0.4.0 da9ee490d which seems like the latest

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

#105

Post by ggktk » Tue, Jun 21, 2022, 9:30 PM UTC

DarmokJalad1701 wrote: post #59

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.

Let me introduce you to bazel ... bazel build //:--foobar --//::\\

I'm not sure what that does, but it reminds me of GNU Parallel's interface, which although smart, is not very intuitive and memorable in my experience.

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

#106

Post by naniwaduni » Tue, Jun 21, 2022, 9:32 PM UTC

ketralnis wrote: post #91

_joel wrote: post #7

Earlier quoted context omitted.

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

Colons are a little bit special in Bournish shells, since they're a delimiter in variable assignments after which tilde expansions, and some shells extend this behavior to command line arguments.

Frankly, I don't find it outside the realm of possibility that there's some combination of options that will make :memory: misparse on a popular shell, I just don't know of any...

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

#107

Post by cube2222 » Tue, Jun 21, 2022, 9:36 PM UTC

cube2222 wrote: post #102

aargh_aargh wrote: post #101

Earlier quoted context omitted.

Hi cube2222! Just today I tried octosql for the first time when I wanted to correlate a handful of CSV files based on identifiers present in roughly equal form. Great idea but I immediately ran into many rough edges in what I think was a simple use case. Here are my random observations. Missing FULL JOIN (this was a dealbreaker for me). LEFT/RIGHT join gave me "panic: implement me". It took me a while to figure out h…

Hey! Thanks a lot for this writeup! > but I immediately ran into many rough edges OctoSQL is definitely not in a stable state yet, so depending on the use case, there definitely are rough edges and occasional regressions. > Missing FULL JOIN (this was a dealbreaker for me). LEFT/RIGHT join gave me "panic: implement me". Indeed, right now only inner join is implemented. The others should be available soon. > replace()…

Just to update this response, I've just released a new version that contains a new `position` function, as well as the capability to execute SELECT statements without a FROM part. So i.e. the above

  octosql "SELECT position('hello', 'ello')"

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

#108

Post by westurner » Tue, Jun 21, 2022, 10:30 PM UTC

mattewong wrote: post #99

cldellow wrote: post #41

Earlier quoted context omitted.

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). Dis…

What are the differences between the zsv and csv parsers?

Is csvw with linked data URIs also doable?

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

#109

Post by eli » Tue, Jun 21, 2022, 11:36 PM UTC

simonw wrote: post #72

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

Neat! I guess it got fixed in the, uh, decade or so since I last tried

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

#110

Post by mattewong » Wed, Jun 22, 2022, 12:22 AM UTC

westurner wrote: post #108

mattewong wrote: post #99

Earlier quoted context omitted.

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). Dis…

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 Excel implements, so having data URIs in the CSV is fine, but if you wanted a compound value (e.g. text + link), you would need to overlay your own structure into the CSV text data (for example, embed JSON inside a column of CSV data). Not sure that answers your question but if not, feel free to add further detail and I'll try again...