demo D296
Half a million transactions in Parquet
Half a million card transactions queried by DuckDB in the tab: every filter, sort and page becomes one statement, and only the hundred rows on screen come back
duckdbAdapter · createPushdownSource
Half a million card transactions sit in a Parquet file that never enters the grid. Every filter, sort and scroll becomes one statement DuckDB answers, and only the hundred rows on screen come back, with the statement, its bound values and its measured time printed beside them.
The configuration
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.50.0/lattice-grid.min.css">
<script src="https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.50.0/lattice-grid.min.js"></script>
<div id="transactions" style="height: 620px"></div>
<script type="module">
// DuckDB is your engine, not ours: the grid imports none of it. Swap this
// block for a connection to your own warehouse and nothing below changes.
import * as duckdb from 'https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm/+esm';
const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
const worker = await duckdb.createWorker(bundle.mainWorker);
const db = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(), worker);
await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
const connection = await db.connect();
// The engine resolves the URL, so give it an absolute one.
const file = new URL('/demo-data/transactions.parquet', location.origin).href;
const adapter = LatticeGrid.duckdbAdapter({
connection,
from: `read_parquet('${file}')`,
});
// Windowed: the source turns the grid's filter, sort and scroll position into
// one statement, and only the page on screen comes back. The match count
// rides along inside that same statement, so the total is never a second read.
const source = LatticeGrid.createPushdownSource({
adapter,
compute: LatticeGrid,
pageSize: 100,
// sum and count are verified identical, so a total over the whole matching
// set is computed by DuckDB rather than over the loaded page.
aggregates: { default: 'engine-if-identical' },
});
const grid = LatticeGrid.createGrid(document.getElementById('transactions'), {
rowKey: 'transaction_id',
toolPanel: { side: 'left', panels: ['filters', 'columns'] },
columns: [
{ field: 'ts', title: 'Time (UTC)', type: 'timestamp', typeOptions: { timeZone: 'UTC' } },
{ field: 'merchant', title: 'Merchant', filter: { type: 'set' } },
{ field: 'category', title: 'Category', filter: { type: 'set' } },
{ field: 'country', title: 'Country', filter: { type: 'set' } },
{ field: 'amount', title: 'Amount', type: 'number', format: { style: 'currency', currency: 'GBP' } },
{ field: 'status', title: 'Status', filter: { type: 'set' } },
{ field: 'risk_score', title: 'Risk', type: 'number' },
],
source,
});
// Any filter is an ordinary filter tree, and the adapter binds every value
// rather than pasting it into the statement.
grid.filters.set({
op: 'and',
conditions: [
{ col: 'country', op: 'eq', value: 'GB' },
{ col: 'risk_score', op: 'gt', value: 70 },
],
});
grid.sort.set([{ col: 'amount', dir: 'desc' }]);
// adapter.sqlFor(query) returns the statement and its bound values, which is
// what the panel beside the demo prints.
</script>The grid holds a page, not a dataset
A client-side grid has to be given its rows before it can do anything with them, which is fine until the dataset is larger than a browser tab should ever hold. This screen never receives the dataset. Half a million transactions stay in the Parquet file; DuckDB reads it in the tab, and the grid receives the hundred rows it is about to paint. Scroll and it receives the next hundred. The readout under the statement is that arrangement in two numbers: how many rows match, and how many came back.
Set the country to GB and the risk score above 70 and the numbers separate sharply: a few thousand match, a hundred return. Nothing about the grid changed. The WHERE moved into the query, DuckDB counted the matches in the same statement that produced the page, and the ratio is the answer to “how much data would a client-side grid have had to move to do this”.
Every interaction is one statement
The chips above the grid are ordinary filter trees, set with grid.filters.set(), which is exactly the call a header filter makes. Sorting a column is ORDER BY. Scrolling is LIMIT and OFFSET. Combining three chips is one WHERE with three conditions, not three passes. Each of those is a single statement, and the panel beside the grid prints the one DuckDB was actually given, with the values bound to it and the time it took in this browser.
Values are bound, never pasted in. A country arrives as a parameter, a date range arrives as a typed pair the engine casts for itself, and a list of statuses arrives as an IN list of parameters. That matters the moment the same code points at a real warehouse rather than a file in a tab.
Where the numbers come from
QUERY is the wall time of that statement, measured here, not a figure written into the page. RETURNED is the number of rows the grid was handed. DATASET is the row count of the file. The match count and the value of the matching set are both DuckDB’s own: the count rides along inside the rows statement, and the value is a sum the source pushes to the engine, so both describe everything that matches rather than the page that happens to be loaded.
How do I put a grid over a Parquet file or a warehouse?
Give duckdbAdapter a connection your page already made and a from clause, hand the adapter to createPushdownSource, and give the source to the grid. The grid then asks the source for a window of rows, the source turns the grid’s filter, sort and range into one statement, and the adapter runs it. Nothing about the columns, the filters or the rendering changes from a grid over an in-memory array. Point from at a table instead of read_parquet(...) and the same screen runs against your warehouse.