ian
ian•10mo ago

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       │
└───────────┴────────────┘
$ 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.
Jamie Turner
Vimeo
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.
0 Replies
No replies yetBe the first to reply to this messageJoin