ianI
Convex Community•2y ago
ian

How to write SQL against a Convex data snapshot

You can use DuckDB to write SQL queries against a data snapshot. If you want to do some analytics against your current dataset, and don't want to set up streaming export, and want more than the interactive dashboard query[^1], you can install duckdb 
$ brew install duckdb # Installs duckdb (mac)
$ npx convex export --prod --path ./snapshot.zip
$ unzip ./snapshot.zip
$ duckdb
D install 'json';
D load 'json';
D SELECT * from 'snapshot/myTable/documents.jsonl' LIMIT 1;
┌────────────────────┬─────────────┬───────────┬─────────────────────────────────┐
│   _creationTime    │     _id     │ someField │          otherTableId           │
│       double       │   varchar   │  varchar  │             varchar             │
├────────────────────┼─────────────┼───────────┼─────────────────────────────────┤
│ 1705522240446.3655 │ js700yfk... │ foo       │ 3cqbsxb8cexh8stz73be6f9w9hjqa28 │
└────────────────────┴─────────────┴───────────┴─────────────────────────────────┘
D SELECT someField, otherField from 'snapshot/myTable/documents.jsonl' as myTable
    JOIN 'snapshot/otherTable/documents.jsonl' as otherTable
    ON myTable.otherTableId = otherTable._id LIMIT 1;
┌───────────┬────────────┐
│ someField │ otherField │
│  varchar  │  boolean   │
├───────────┼────────────┤
│ foo       │ true       │
└───────────┴────────────┘


[^1]: https://vimeo.com/925663954
DuckDB
An in-process SQL OLAP database management system
DuckDB is an in-process SQL OLAP database management system. Simple, feature-rich, fast & open source.
An in-process SQL OLAP database management system
VimeoJamie Turner
Interactive query in the dashboard
This is "Interactive query in the dashboard" by Jamie Turner on Vimeo, the home for high quality videos and the people who love them.
Interactive query in the dashboard
Was this page helpful?