Pivot Table

A high-performance pivot table for ClickHouse. Built for debugging analytical marts and exploring large datasets interactively.

The table is optimized to handle millions of aggregated rows in the browser. Memory is the only limit — if the result fits in browser memory, it renders.

Ideal for ClickHouse users who need to quickly validate data pipelines, debug complex aggregations, or explore materialized views without writing SQL.

Quick Start

Docker (recommended)

docker compose up --build

Open http://localhost:3000. A demo sales dataset is included.

Manual

Backend:

cd backend
uv sync
uv run init-demo
uv run dev

Frontend:

cd frontend
pnpm install
pnpm dev

Backend runs on http://localhost:8001, frontend on http://localhost:5173

Connect Your Table

Just point to any ClickHouse table and it works. Configure in backend/config/adapters.yaml:

adapters:
  - id: 1
    name: "My Data"
    description: "Sales data"
    jdbc_url: "clickhouse://localhost:8123/default"
    table: "my_table"

Numeric columns become metrics, others become dimensions. No extra setup required.

Connection Types

Embedded (chdb):

chdb://data/chdb

Remote ClickHouse:

clickhouse://host:8123/database
clickhouse://host:8123/database?user=admin&password=secret

Column Naming

Columns are grouped by __ separator in names:

CREATE TABLE sales (
    time__year UInt16,
    time__month UInt8,
    location__region String,
    location__country String,
    metrics__revenue Float64
)

This creates groups: Time (Year, Month), Location (Region, Country), Metrics (Revenue).

Labels are taken from column comments:

CREATE TABLE sales (
    time__year UInt16 COMMENT 'Year',
    metrics__revenue Float64 COMMENT 'Total Revenue'
)

For more control, use JSON in comments:

COMMENT COLUMN revenue '{"label": "Revenue", "groups": ["Finance", "KPIs"], "description": "Total revenue in USD"}'

Default View

Set a default pivot configuration that loads automatically:

adapters:
  - id: 1
    name: "Sales"
    jdbc_url: "chdb://data/chdb"
    table: "sales"
    default_state:
      rows: ["location__region", "location__country"]
      cols: ["product__category"]
      metrics:
        - name: "metrics__revenue"
          aggregation: "sum"
      conditionalFormats:
        - column: "metrics__revenue"
          type: "greater"
          value1: 50000
          backgroundColor: "#dcfce7"
          textColor: "#166534"

Features

• Configure rows, columns, and metrics
• Aggregation: sum, count, avg, min, max
• Filters with various operators
• Sorting by dimensions or metrics
• Subtotals and grand totals
• Conditional formatting with color rules
• Transpose rows and columns
• Shareable URL with state

Tech Stack

• Backend: FastAPI, chdb, clickhouse-connect
• Frontend: React, Vite, Tailwind CSS, Glide Data Grid