demo D371

Reading ClickHouse live, filter to SQL: JavaScript Data Grid Demo

A grid over ClickHouse’s own public playground: filter, sort, page and count are pushed as SQL, and a grouped average lands in a KPI tile

createPushdownSource · clickhouseAdapter · lastPlan() · source.aggregate()

This grid reads ClickHouse's own public playground live: filtering, sorting, paging, counting, filter-menu values and pivoting are all pushed down as SQL, shown beside the grid as it runs. Group by County and the totals for all 132 counties return as one grouped query, not a guess from loaded rows. Rows still arriving show a brief loading treatment. Read-only and keyless against play.clickhouse.com; availability is ClickHouse's own.

Building…

Loading a live grid…

The configuration

<link rel="stylesheet" href="https://cdn.latticegrid.dev/lattice-grid@1.104.0/lattice-grid.min.css">
<script src="https://cdn.latticegrid.dev/lattice-grid@1.104.0/lattice-grid.min.js"></script>

<pre id="sql" style="margin-bottom:12px"></pre>
<div id="tiles" style="margin-bottom:12px"></div>
<div id="grid" style="height:520px"></div>

<script type="module">
  import { createKPI } from '@toclocoinc/lattice-grid/modules/kpi';

  const sqlEl = document.getElementById('sql');
  const adapter = LatticeGrid.clickhouseAdapter({
    url: 'https://play.clickhouse.com',
    table: 'uk_price_paid',
    headers: { 'X-ClickHouse-User': 'play' },   // ClickHouse's own read-only playground user
  });
  const source = LatticeGrid.createPushdownSource({ adapter, compute: LatticeGrid, pageSize: 100 });

  const kpi = createKPI(document.getElementById('tiles'), {
    rowKey: 'town',
    columns: 2,
    tiles: [
      { id: 'avgPrice', label: 'Average price · busiest town', aggregation: 'avg', field: 'avg_price', format: { style: 'currency', currency: 'GBP', notation: 'compact' } },
      { id: 'sales', label: 'Sales counted there', aggregation: 'sum', field: 'n' },
    ],
  });

  let currentFilters = null;
  const refreshKpi = async () => {
    const result = await source.aggregate(
      { filters: currentFilters, sort: [], range: null, groupBy: ['town'] },
      [{ id: 'avg_price', col: 'price', fn: 'avg' }, { id: 'n', col: 'price', fn: 'count' }],
    );
    const top = (result.groups ?? [])
      .filter((g) => g.keys?.[0])
      .sort((a, b) => (b.values.n ?? 0) - (a.values.n ?? 0))[0];
    if (top) kpi.rows.apply({ add: [{ town: String(top.keys[0]), avg_price: top.values.avg_price ?? 0, n: top.values.n ?? 0 }] });
  };

  // Every filter, sort and page becomes one statement; sqlFor(query) shows it
  // without running it twice, and the grouped average is a second statement
  // ClickHouse itself answers over the whole matching set, not just the page.
  const inner = adapter.execute.bind(adapter);
  adapter.execute = async (query, request) => {
    const result = await inner(query, request);
    currentFilters = query.filters ?? null;
    // uk_price_paid carries no column that uniquely identifies a row; stamp
    // one from each page's own offset, enough for the grid's own diffing
    // since nothing here is written back.
    const offset = query.range?.offset ?? 0;
    result.rows = (result.rows ?? []).map((r, i) => ({ ...r, __k: offset + '-' + i }));
    const f = adapter.sqlFor(query);
    sqlEl.textContent = f.sql + (f.params?.length ? '\n-- bound: ' + JSON.stringify(f.params) : '');
    void refreshKpi();
    return result;
  };

  const grid = LatticeGrid.createGrid(document.getElementById('grid'), {
    rowKey: '__k',
    toolPanel: { side: 'left', panels: ['filters', 'columns'] },
    columns: [
      { field: 'date', title: 'Date', type: 'dateString' },
      { field: 'price', title: 'Price', type: 'number', format: { style: 'currency', currency: 'GBP', notation: 'compact' }, filter: { type: 'number' } },
      { field: 'type', title: 'Type', filter: { type: 'set' } },
      { field: 'town', title: 'Town', filter: { type: 'set' } },
      { field: 'county', title: 'County', filter: { type: 'set' } },
    ],
    source,
  });
</script>