Using DuckDb as a SQL data source for Perspective

[stevesimmons]

Has anyone tried DuckDb as a data source to drive Perspective?
That could give simple Parquet input and SQL querying, with Arrow output into the Perspective grid.

Another potentially useful package here is node-duckdb, a TypeScript DuckDb implementation.

Thanks!

[texodus]

Sorry about the late reply @stevesimmons

It is pretty trivial to build an app that uses DuckDB's Arrow writing support to generate data suitable to be consumed by Perspective, in Browser/Wasm, Node or Python. You'd need to be careful what queries you generate, as Perspective cannot currently read all Arrow types.

However, Perspective is basically already a SQL engine for streaming queries, it just doesn't use SQL-the-language for queries (yet). DuckDB has some really nice static features itself, but I am skeptical the right solution to bridge the feature gap is to string these two query engines together end-to-end, at least as far as providing a coherent UX is concerned.

<perspective-viewer> is very tightly coupled to its wasm data engine, and replacing this outright with DuckDB or anything else is out-of-scope for the project as it stands today.

[AndreaGernone]

4 years passed. Is this answer still valid? Has anyone tried it?

[salahfh]

Check https://perspective-dev.github.io/guide/how_to/python/virtual_server/duckdb.html

[AndreaGernone]

Thank you! I saw this earlier. Since I'm thinking about using it I wanted to know if anyone else had feedback on that too

[texodus]

No, this answer is not still valid.

Perspective now has full support for DuckDB (and others) as a Virtual Server. This support fully replaces Perspective's built-in engine with DuckDB, including all calculation, aggregation, pivotting and even the Expression editor becomes DuckDB expressions (with highlighting and error validation). Like with the built-in engine, <perspective-viewer> never serializes this data in full (and uses Arrow IPC throughout where it serializes slices) so using DuckDB as a backend is never bottle-necked by Perspective ingest (there is no ingest). Tested in Python, Rust and Browser + Node.js (WASM).

Perspective DuckDB support is fast, supports the range of DuckDB plugins, and has a much higher headroom than Perspective's built-in engine due to its OPFS support (even in WASM64 mode). It does not natively support streaming updates yet, however.

DuckDB WASM Example with a logger that logs the internal SQL to another Perspective Table.

Another off-site DuckDB WASM Example with the duckdb-shell and a <perspective-workspace> (that has very cool DuckLake support!)

DuckDB Python example

[AndreaGernone]

Thank you so much for your answer!