sqlite-parquet-vtable

tasette and datasette-lite could support xarray and thus NetCDF-style multidimensional arrays in WASM in the browser with HTTP Content Range requests to fetch and cache just the data requested "The NetCDF header": https://climateestimate.net/content/netcdfs-and-basic-coding... : > The header can also be used to verify the order of dimensions that a variable is saved in (which you will have to know to use, unless you’re using a tool like xarray that lets you refer to dimensions by name) - for a 3-dimensional variable, `lon,lat,time` is common, but some files will have the `time` variable first. "Loading a subset of a NetCDF file": https://climateestimate.net/content/netcdfs-and-basic-coding... From https://news.ycombinator.com/item?id=42260094 : > xeus-sqlite-kernel > "Loading SQLite databases from a remote URL" https://github.com/jupyterlite/xeus-sqlite-kernel/issues/6#i... %FETCH <url> <filename>

ovide 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 ## Sqlite query optimization `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/