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

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

#81

Post by qoega » Tue, Jun 21, 2022, 6:36 PM UTC

eatonphil wrote: post #76

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.

It is that big only with debug symbols. If you take packaged version of ClickHouse it will be much smaller. Or just strip large binary manually if you already have it.

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

#82

Post by zX41ZdbW » Tue, Jun 21, 2022, 6:36 PM UTC

eatonphil wrote: post #76

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.

It is shipped with debug info and symbol tables just in case.

Without debug info, it will be 350 MB, and compressed can fit in 50 MB: https://github.com/ClickHouse/ClickHouse/issues/29378

It is definitely a worth improvement.

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

#83

Post by minusf » Tue, Jun 21, 2022, 6:48 PM UTC

chrisweekly wrote: post #51

One of my all-time favorite (and somehow still-obscure / relatively unknown) tools is called `lnav` ^1. It's a mini-ETL powertool with embedded SQLite, perfect for wrangling log files or other semi-structured data (a few millions of rows are no problem), it's intuitive and flexible... 1. https://lnav.org

lnav is super cool, but as its name says: log navigator, it's more of a less/tail/etc supercharged with sqlite under the hood.

of course because it has a flexible format definition it can deal with csv files as well, but it's true power is getting sql queries out of nginx log files and the like without the intermediate step of exporting them to csv.

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

#84

Post by mattewong » Tue, Jun 21, 2022, 6:53 PM UTC

mrfusion wrote: post #36

How smart is SQLite at detecting column types from Csv data? I once wrote a Python script to load csv files into SQLite. It had a whole hierarchy of rules to determine the data type of each column.

It doesn't detect column types automatically-- they are imported as text. You can, however, use math functions on them and sqlite3 will dynamically convert where possible (e.g. "select number_1_stored_as_text + 1 from mytable" will output 2)

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

#85

Post by cube2222 » Tue, Jun 21, 2022, 6:53 PM UTC

eatonphil wrote: post #77

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.

Thanks for the acknowledgement!

Though btw., I think I personally prefer the SPyQL benchmarks[0], as they test a bigger variety of scenarios. This benchmark is mostly testing CSV decoding speed - because the group by is very simple, with just a few keys in the grouping.

[0]:https://colab.research.google.com/github/dcmoura/spyql/blob/...

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

#86

Post by pdimitar » Tue, Jun 21, 2022, 6:54 PM UTC

corytheboyd wrote: post #56

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

piping to jq (using json mode of course) also works well for this

Or `jless`, which shows you a tree and doesn't flood your terminal.

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

#87

Post by alana314 » Tue, Jun 21, 2022, 6:59 PM UTC

I like using q for querying CSVs on the command line: https://github.com/harelba/q

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

#88

Post by corytheboyd » Tue, Jun 21, 2022, 7:03 PM UTC

pdimitar wrote: post #86

corytheboyd wrote: post #56

Earlier quoted context omitted.

piping to jq (using json mode of course) also works well for this

Or `jless`, which shows you a tree and doesn't flood your terminal.

Nice, that's definitely better than `jq | less`

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

#89

Post by eatonphil » Tue, Jun 21, 2022, 7:04 PM UTC

cube2222 wrote: post #85

eatonphil wrote: post #77

Earlier quoted context omitted.

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

Thanks for the acknowledgement! Though btw., I think I personally prefer the SPyQL benchmarks[0], as they test a bigger variety of scenarios. This benchmark is mostly testing CSV decoding speed - because the group by is very simple, with just a few keys in the grouping. [0]: https://colab.research.google.com/github/dcmoura/spyql/blob/...

Actually I have a big issue with that benchmark in that it doesn't ORDER BY. I don't believe all those tools will produce the same result and it's not required by SQL for them to do so.

That doesn't change the poor performance of dsq but it does change the relative and absolute scores in that benchmark.

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

#90

Post by a-dub » Tue, Jun 21, 2022, 7:07 PM UTC

i used to daydream about adding an ALLOW DOING IT LIVE option to cassandra's csql client. in the event that your where clause was incompatible with your table's key, it would just wholesale dump the table in question into a sqlite while indexing the appropriate columns, run the query in question, actually return the result and then properly clean up.