One-liner for running queries against CSV files with SQLite

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 does the counting. That's great, unless you already know the count and could have reported it directly rather than actually returning every row in the table. You're forced to retrieve and return every row because you have no idea that it was actually just a count.

As another example, if the user query includes a join, you won't see the join. Instead, you will receive a series of N queries for individual IDs, even if you could have more efficiently retrieved them in a batch.

The join one is particularly nasty. If you're writing a virtual table that accesses a remote resource with some latency, any join will absolutely ruin your performance as you pay a full network roundtrip for each of those N queries.

I wrote a module that exposes remote SQL Server/PostgreSQL/MySQL servers as SQLite virtual tables, and joins basically don't work at all if your server is not on your local network. There's nothing I can do about it (other than heuristically guessing what IDs might be coming and request them ahead of time) because SQLite doesn't provide enough information to the virtual table layer. It's my understanding that PostgreSQL's foreign data wrappers (a similar feature to SQLite's virtual tables) push much more information about the query down to the wrapper layer, but I haven't used it myself.

- "Comparing SQLite, DuckDB and Arrow with UN trade data" (2021) https://news.ycombinator.com/item?id=29010103 ; partial benchmarks of query time and RAM requirements [relative to data size] would be

- "Introducing Apache Arrow Flight SQL: Accelerating Database Access" (2022) https://arrow.apache.org/blog/2022/02/16/introducing-arrow-f... :

> Motivation: While standards like JDBC and ODBC have served users well for decades, they fall short for databases and clients which wish to use Apache Arrow or columnar data in general. Row-based APIs like JDBC or PEP 249 require transposing data in this case, and for a database which is itself columnar, this means that data has to be transposed twice—once to present it in rows for the API, and once to get it back into columns for the consumer. Meanwhile, while APIs like ODBC do provide bulk access to result buffers, this data must still be copied into Arrow arrays for use with the broader Arrow ecosystem, as implemented by projects like Turbodbc. Flight SQL aims to get rid of these intermediate steps.

## "The Virtual Table Mechanism Of SQLite" https://sqlite.org/vtab.html :

> - One cannot create a trigger on a virtual table.

Just posted about eBPF a few days ago; opcodes have costs that are or are not costed: https://news.ycombinator.com/item?id=31688180

> - One cannot create additional indices on a virtual table. (Virtual tables can have indices but that must be built into the virtual table implementation. Indices cannot be added separately using CREATE INDEX statements.)

It looks like e.g. sqlite-parquet-vtable implements shadow tables to memoize row group filters. How does JOIN performance vary amongst sqlite virtual table implementations?

> - One cannot run ALTER TABLE ... ADD COLUMN commands against a virtual table.

Are there URIs in the schema? Mustn't there thus be a meta-schema that does e.g. nested structs with portable types [with URIs], (and jsonschema, [and W3C SHACL])? #nbmeta #linkedresearch

## /? sqlite arrow virtual table

- sqlite-parquet-vtable reads parquet with arrow for SQLite virtual tables https://github.com/cldellow/sqlite-parquet-vtable :

  $ sqlite/sqlite3
  sqlite> .eqp on
  sqlite> .load build/linux/libparquet
  sqlite> CREATE VIRTUAL TABLE demo USING parquet('parquet-generator/99-rows-1.parquet');
  sqlite> SELECT * FROM demo;
  //
  sqlite> SELECT * FROM demo WHERE foo = 123;
  sqlite> SELECT * FROM demo WHERE foo = '123'; // incurs a severe query plan performance regression without immediate feedback

`EXPLAIN QUERY PLAN` https://www.sqlite.org/eqp.html :

> The EXPLAIN QUERY PLAN SQL command is used to obtain a high-level description of the strategy or plan that SQLite uses to implement a specific SQL query. Most significantly, EXPLAIN QUERY PLAN reports on the way in which the query uses database indices. This document is a guide to understanding and interpreting the EXPLAIN QUERY PLAN output. [...] Table and Index Scans [...] Temporary Sorting B-Trees (when there's not an `INDEX` for those columns) ... `.eqp on`

The SQLite "Query Planner" docs https://www.sqlite.org/queryplanner.html list Big-O computational complexity bound estimates for queries with and without prexisting indices.

## database / csv benchmarks

- https://h2oai.github.io/db-benchmark/

It really should be, as the code is tiny and this functionality is not overly exotic.

https://godocs.io/flag

Personally I'm fine with it. The whole, "let's combine 5 letter options into one string", always smacked of excess code golf to me.

bazel build //:--foobar --//::\\

    sqlite3 :memory: '.mode csv' '.import taxi.csv taxi' \
    'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

Also for querying large CSV and Parquet files, I use DuckDB. It has a vectorized engine and is super fast. It can also query SQLite files directly. The SQL support is outstanding.

https://duckdb.org/

Just have start the DuckDB REPL and start querying e.g.

    Select * from ‘bob.CSV’ a
    Join ‘Mary.parquet’ b
    On a.Id = b.Id

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

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.

sqlite3 :memory: -csv -header -cmd '.import taxi.csv taxi' 'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count' | tv

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.

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'

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'

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

For example let's try changing/fixing sampling rate of a dataset (.resample() in Pandas).

Or something like .cumsum() -- easy with SQL windowing functions, but man they are cumbersome.

Or quickly store the result in .parquet.

But all the above doesn't matter, because I feel like 99% of Pandas work involves quickly drawing charts on the data look at it or show to teammates.

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 designed with the help of category theory, and it arrives at the same constructs.

However SQL is declarative, which provides a path for query optimizers to parse and create optimized plans. Whereas with chained methods, unless one implements lazy evaluation one misses out on look aheads and opportunities to do rewrites.

SQL is super expressive, but I think pandas gets a bad rap. At it's core the data model and language can be more expressive than relational databases (see [1]).

I co-authored a paper that explained these differences with a theoretical foundation[1].

[1] https://arxiv.org/abs/2001.00888

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

    % 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>

[0] https://csvkit.readthedocs.io/en/latest/scripts/csvsql.html

And this query:

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

Honestly I don't see a problem with the few posts I looked at. It's like recipes. You can't copyright recipes. At least it's not AI-generated blogspam, but a modicum of at least curating went in here.

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 figured something out - those issue threads often link back to other sources.

For that IPv6 one: https://til.simonwillison.net/networking/http-ipv6

I had tried and failed to figure this out using Google searches in the past. I wrote that up after someone told me the answer in a private Slack conversation - saying who told me didn't feel appropriate there.

My goal with that page was to ensure that future people (including myself) who tried to find this with Google would get a better result!

(I'm a bit upset about this comment to be honest, because attributing people is something of a core value for me - the bookmarks on my blog have a "via" mechanism for exactly that reason: https://simonwillison.net/search/?type=blogmark )

This is the author's notebook, which happens to be public. It's okay.