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

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

#31

Post by wenc » Tue, Jun 21, 2022, 3:29 PM UTC

valw wrote: post #30

wenc wrote: post #25

Earlier quoted context omitted.

SQL does not exactly implement relational algebra in its pure form. SQL implements a kind of set theory with relational elements and a bunch of practical features like pivots, window functions etc. Pandas does the same. Most data frame libraries like dplyr etc. implement a common set of useful constructs. There’s not much difference in expressiveness. LINQ Is another language around manipulating sets that was designe…

> There’s not much difference in expressiveness > However SQL is declarative Pick one :) the way I see it, if declarativeness is not a factor in assessing expressiveness, then expressiveness reduces to the uninteresting notion of Turing-equivalence.

Expressiveness and declarativeness are different things, no?

Are you talking about aesthetics? I’ve used SQL for 20 years and it’s elegant in parts but it also has warts. I talk about this elsewhere but SQL gets repetitive and requires multi layer CTEs to express certain simple aggregations.

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

#32

Post by flusteredBias » Tue, Jun 21, 2022, 3:30 PM UTC

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

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

#33

Post by avogar » Tue, Jun 21, 2022, 3:31 PM UTC

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/formats/).

There is an example of using clickhouse-local with taxi dataset mentioned in the post: https://colab.research.google.com/drive/1tiOUCjTnwUIFRxovpRX...

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

#34

Post by practice9 » Tue, Jun 21, 2022, 3:40 PM UTC

In the past I've used https://github.com/BurntSushi/xsv to query some large CSVs

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

#35

Post by ArchD » Tue, Jun 21, 2022, 3:40 PM UTC

If you need to query against multiple CSVs, e.g. using joins, you could use QHS: https://github.com/itchyny/qhs

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

#36

Post by mrfusion » Tue, Jun 21, 2022, 3:41 PM UTC

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.

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

#37

Post by adamgordonbell » Tue, Jun 21, 2022, 3:45 PM UTC

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

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

#38

Post by mytherin » Tue, Jun 21, 2022, 3:51 PM UTC

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 the file name contains no special characters.

DuckDB uses the SQLite shell, so all of the commands that are mentioned in the article with SQLite will also work for DuckDB.

[1] https://github.com/duckdb/duckdb

Disclaimer: Developer of DuckDB

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

#39

Post by simonw » Tue, Jun 21, 2022, 3:52 PM UTC

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.

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

#40

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

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