29 ms·

SQLite's virtual table API (https://www.sqlite.org/vtab.html https://www.sqlite.org/vtab.html) makes it possible to access other data structures through the que

SQLite's virtual table API (https://www.sqlite.org/vtab.html https://www.sqlite.org/vtab.html) makes it possible to access other data structures through the query engine. You don't need to know much if anything about how the database engine executes queries, you only need to implement the callbacks it needs to do its job. A few years ago I wrote an extension to let me search through serialized Protobufs which were stored as blobs in a regular database. https://github.com/rgov/sqlite_protobuf https://github.com/rgov/sqlite_protobuf

electroly 4y ago
I like the virtual table API a lot but it has some serious drawbacks. You don't need to know much and indeed, you can't know much about the execution engine, even if that knowledge would help you. Many parts of the query are not pushed down into the virtual table. 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.
- lazypenguin 4y ago
  You also can’t add new columns using alter statements. I really like virtual tables in SQLite but It would be nice if documentation included some limitations and reasons not to use.
- petercooper 4y ago
  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. Is it possible to wait until all of the queries are in and then do some query planning at your end to resolve this? Or is it entirely synchronous with no way to do that?
  - electroly 4y ago
    Unfortunately it's synchronous. The interface requires you to return the rows for the first key before it gives you the second one, and so on.
westurner 4y ago
## /? sqlite arrow - "Comparing SQLite, DuckDB and Arrow with UN trade data" (2021) https://news.ycombinator.com/item?id=29010103 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-flight-sql/ 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 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 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 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 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 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/ https://h2oai.github.io/db-benchmark/
pstuart 4y ago
And in fact there is a CSV virtual table available from SQLite but it's not built in the normal client: https://www.sqlite.org/csv.html https://www.sqlite.org/csv.html It really should be, as the code is tiny and this functionality is not overly exotic.
- cldellow 4y ago
  In my experience [1], the CSV virtual table was really slow. Doing some analysis on a 1,291 MB file, a query took 24.7 seconds using the virtual table vs 3.4 seconds if you imported the file first. The CSV virtual table source code is a good pedagogical tool for teaching how to build a virtual table, though. [1]: https://cldellow.com/2018/06/22/sqlite-parquet-vtable.html https://cldellow.com/2018/06/22/sqlite-parquet-vtable.html
  - pstuart 4y ago
    Dang, that's a bummer -- thanks for the intel, as well as the article / Parquet code.
  - mattewong 4y ago
    https://github.com/liquidaty/zsv/blob/main/app/external/sqlite3/sqlite3_csv_vtab-zsv.c https://github.com/liquidaty/zsv/blob/main/app/external/sqli... modifies the sqlite3 virtual table engine to use the faster zsv parser. have not quantified the difference, but in all tests I have run, `zsv sql` runs faster (sometimes much faster) than other sqlite3-on-CSV solutions mentioned in this entire discussion (unless you include those that cache their indexes and then measure against a post-cached query). Disclaimer: I'm the main zsv author
    - westurner 4y ago
      What are the differences between the zsv and csv parsers? Is csvw with linked data URIs also doable?
      - mattewong 4y ago

electroly 4y ago

I like the virtual table API a lot but it has some serious drawbacks. You don't need to know much and indeed, you can't know much about the execution engine, even if that knowledge would help you. Many parts of the query are not pushed down into the virtual table. 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.

lazypenguin 4y ago
You also can’t add new columns using alter statements. I really like virtual tables in SQLite but It would be nice if documentation included some limitations and reasons not to use.
petercooper 4y ago
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. Is it possible to wait until all of the queries are in and then do some query planning at your end to resolve this? Or is it entirely synchronous with no way to do that?
- electroly 4y ago
  Unfortunately it's synchronous. The interface requires you to return the rows for the first key before it gives you the second one, and so on.

westurner 4y ago

## /? sqlite arrow - "Comparing SQLite, DuckDB and Arrow with UN trade data" (2021) https://news.ycombinator.com/item?id=29010103 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-flight-sql/ 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 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 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 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 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 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/ https://h2oai.github.io/db-benchmark/

pstuart 4y ago

And in fact there is a CSV virtual table available from SQLite but it's not built in the normal client: https://www.sqlite.org/csv.html https://www.sqlite.org/csv.html It really should be, as the code is tiny and this functionality is not overly exotic.

cldellow 4y ago
In my experience [1], the CSV virtual table was really slow. Doing some analysis on a 1,291 MB file, a query took 24.7 seconds using the virtual table vs 3.4 seconds if you imported the file first. The CSV virtual table source code is a good pedagogical tool for teaching how to build a virtual table, though. [1]: https://cldellow.com/2018/06/22/sqlite-parquet-vtable.html https://cldellow.com/2018/06/22/sqlite-parquet-vtable.html
- pstuart 4y ago
  Dang, that's a bummer -- thanks for the intel, as well as the article / Parquet code.
- mattewong 4y ago
  https://github.com/liquidaty/zsv/blob/main/app/external/sqlite3/sqlite3_csv_vtab-zsv.c https://github.com/liquidaty/zsv/blob/main/app/external/sqli... modifies the sqlite3 virtual table engine to use the faster zsv parser. have not quantified the difference, but in all tests I have run, `zsv sql` runs faster (sometimes much faster) than other sqlite3-on-CSV solutions mentioned in this entire discussion (unless you include those that cache their indexes and then measure against a post-cached query). Disclaimer: I'm the main zsv author
  - westurner 4y ago
    What are the differences between the zsv and csv parsers? Is csvw with linked data URIs also doable?
    - mattewong 4y ago