One-liner for running queries against CSV files with SQLite

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://wiki.tcl-lang.org/page/Sqawk

- "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.