[hypequery](https://hypequery.com/) › [Examples](https://hypequery.com/examples) › Embedded ClickHouse analytics with chDB: typed datasets, a CLI and an API, no server
Index for agents: https://hypequery.com/llms.txt

# Embedded ClickHouse analytics with chDB: typed datasets, a CLI and an API, no server (https://hypequery.com/examples/chdb-embedded-analytics)

Run a ClickHouse semantic layer inside your Python process with chDB: no Docker, no server, then point the same datasets at ClickHouse Cloud.

## Run it

```bash
npx @hypequery/cli create chdb-embedded-analytics -y
cd chdb-embedded-analytics
uv sync
uv run python -m hypequery.serve.dev app:app
```

- Packages: hypequery (tested from hypequery 0.1.0)
- Stack: python, chdb, clickhouse
- Source: https://github.com/hypequery/hypequery/tree/main/examples/chdb-embedded-analytics
- Test: `uv run pytest -q`
- Verify: `uv run python report.py --by tenant.plan`

| Env var | Required | Description |
| --- | --- | --- |
| ANALYTICS_BACKEND | no | chdb (default: embedded, no server) or clickhouse (the same datasets on a server, read from CLICKHOUSE_*) |
| CHDB_PATH | no | chDB database directory (default ./analytics.chdb, built on first run) |
| HYPEQUERY_DEV_TOKEN | no | Bearer token the demo API accepts. Defaults to local-dev-token; replace with real auth before deploying. |
| CLICKHOUSE_URL | no | Only with ANALYTICS_BACKEND=clickhouse: ClickHouse HTTP endpoint (default http://localhost:8123) |
| CLICKHOUSE_USERNAME | no | Only with ANALYTICS_BACKEND=clickhouse: ClickHouse user (default: default) |
| CLICKHOUSE_PASSWORD | no | Only with ANALYTICS_BACKEND=clickhouse: ClickHouse password (default: hypequery, the seed's password) |
| CLICKHOUSE_DATABASE | no | Only with ANALYTICS_BACKEND=clickhouse: database holding events and tenants (default hypequery_examples) |

## Demo

```bash
uv run python report.py --by tenant.plan
```

```json
{
  "backend": "chdb",
  "by": "tenant.plan",
  "data": [
    {"tenant.plan": "enterprise", "events": 72883, "unique_users": 602, "p95_latency_ms": 1665.95, "error_rate": 0.0572},
    {"tenant.plan": "growth",     "events": 47166, "unique_users": 504, "p95_latency_ms": 1628.6,  "error_rate": 0.0560},
    {"tenant.plan": "starter",    "events": 42949, "unique_users": 726, "p95_latency_ms": 1655.45, "error_rate": 0.0554},
    {"tenant.plan": "free",       "events": 37002, "unique_users": 948, "p95_latency_ms": 1757.7,  "error_rate": 0.0591}
  ]
}
```

The first run builds `analytics.chdb/`, a ClickHouse database directory next to the code, with 200,000 events and 24 tenants generated from `numbers()` and `cityHash64`. Every machine gets the same rows, so the counts above are what you will see (p95 moves by a few percent because ClickHouse's `quantile` samples). `--by event_type`, `--by device` or `--by tenant.region` slice it differently.

The same datasets over HTTP:

```bash
uv run python -m hypequery.serve.dev app:app      # 127.0.0.1:8000

curl -s -X POST localhost:8000/datasets/events/query \
  -H 'authorization: Bearer local-dev-token' -H 'content-type: application/json' \
  -d '{"dimensions":["tenant.plan"],"measures":["events","p95_latency_ms","error_rate"],"orderBy":[{"field":"events","direction":"desc"}]}'
# {"data":[{"tenant.plan":"enterprise","events":"72883","p95_latency_ms":"1399.35","error_rate":"0.0571..."}, ...]}
```

## Install

```bash
npx @hypequery/cli create chdb-embedded-analytics -y --no-seed
cd chdb-embedded-analytics
uv sync
uv run python report.py
```

`uv` is the only requirement (`pip install uv`, or see astral.sh/uv). No Docker, no ClickHouse server, no credentials. chDB ships wheels for Linux and macOS.

New to chDB? The [chDB Python tutorial](https://hypequery.com/blog/chdb-python-tutorial) walks through the engine itself; this example is the app you build on it.

## What it shows

- **ClickHouse in the process** (`analytics/backend.py`): `create_chdb_executor("analytics.chdb")` opens an embedded ClickHouse database directory. It runs the same compiled, parameterized, read-only SQL hypequery sends to a server.
- **Typed datasets, defined once** (`analytics/datasets.py`): dimensions, a `count` with a `status_code >= 500` filter, `count_distinct`, a p95 `percentile`, a derived `error_rate` that stays correct at any grouping, and a `belongs_to` relationship that makes `tenant.plan` a dimension through a join.
- **Deterministic data with no download** (`analytics/sample_data.py`): the seed's `events` and `tenants` shape, generated in chDB with integer hashing, so tests pin exact numbers.
- **One model, three surfaces**: a CLI report (`report.py`), an HTTP API (`app.py`, `create_api`), and a test suite (`tests/test_smoke.py`) that checks measures against raw chDB SQL.
- **Portable to a server by changing the executor**: `ANALYTICS_BACKEND=clickhouse` runs the same report on ClickHouse, and the smoke test proves it against the shared seed when one is reachable.

## Key file

`analytics/backend.py` is the only place that knows where ClickHouse runs:

```python
def open_executor(backend: str | None = None) -> ChdbExecutor | ClickHouseExecutor:
    if (backend or backend_name()) == "clickhouse":
        return create_clickhouse_executor(connection_from_env())
    # chDB: the engine runs in this process, on a directory next to the code.
    from analytics.sample_data import ensure_sample_data

    path = os.environ.get("CHDB_PATH", DEFAULT_CHDB_PATH)
    ensure_sample_data(path)
    return create_chdb_executor(path)


def open_client(executor: ChdbExecutor | ClickHouseExecutor) -> DatasetClient:
    # The registry resolves the `tenant` relationship for `tenant.plan` queries.
    return create_dataset_client(executor=executor, registry=create_dataset_registry(events, tenants))
```

The datasets it serves, from `analytics/datasets.py`:

```python
events = dataset(
    "events",
    source="events",
    time_key="occurred_at",
    dimensions={"event_type": dimension.string(), "device": dimension.string(), ...},
    measures={
        "events": measure.count("event_id"),
        "unique_users": measure.count_distinct("user_id"),
        "errors": measure.count("event_id", filters=(filter.gte("status_code", 500),)),
        "p95_latency_ms": measure.percentile("latency_ms", 0.95),
        "error_rate": measure.derived(divide("errors", null_if_zero("events"))),
    },
    relationships={"tenant": belongs_to(tenants, from_field="tenant_id", to_field="tenant_id")},
)
```

## Architecture

```text
report.py --by tenant.plan ─┐
                            ├─▶ dataset client ─▶ planner ─▶ SELECT ... LEFT ANY JOIN tenants ... ─┐
POST /datasets/events/query ┘   (datasets.py)                (typed params, readonly SETTINGS)     │
  (app.py, create_api)                                                                             ▼
                                         ANALYTICS_BACKEND=chdb (default)   ─▶ chDB in this process, ./analytics.chdb
                                         ANALYTICS_BACKEND=clickhouse       ─▶ ClickHouse server / Cloud over HTTP
```

## Adapt it

- **Switch to ClickHouse Cloud or a server.** Load your tables (or the repo's seed, which has the same `events` and `tenants` columns), then:

  ```bash
  export ANALYTICS_BACKEND=clickhouse
  export CLICKHOUSE_URL=https://<your-service>.clickhouse.cloud:8443
  export CLICKHOUSE_USERNAME=default CLICKHOUSE_PASSWORD=... CLICKHOUSE_DATABASE=default
  uv run python report.py --by tenant.plan
  ```

  `datasets.py`, `report.py` and `app.py` do not change; delete `sample_data.py` once the server is the only backend.
- **Your own files.** chDB reads Parquet, CSV and S3 through ClickHouse table functions. `CREATE VIEW events AS SELECT * FROM file('events.parquet')` in `sample_data.py` and the dataset's `source` stays `events`.
- **In-memory for tests and serverless.** `create_chdb_executor()` with no path opens `:memory:`; tables vanish when the last connection closes. A file-backed directory survives restarts.
- **One process per database.** chDB holds one path per process, so stop `pnpm dev` before opening `analytics.chdb` from another script (the tests use their own temporary directory). `hypequery.serve.dev --reload` already hands the database to the reloaded worker.
- **chDB or DuckDB?** Both embed in Python. chDB is the ClickHouse engine itself, so the SQL, functions (`quantile`, `uniqExact`, `multiIf`) and table engines you prototype locally are the ones production ClickHouse runs.
- **Real auth.** `HYPEQUERY_DEV_TOKEN` is a demo. Swap `authenticate` for `jwt_strategy(...)` and add `tenant_key="tenant_id"` to the dataset for per-tenant scoping, as in the FastAPI example.

## Related recipes

- [p95-latency](../../recipes/p95-latency): the `p95_latency_ms` measure, explained.

## Related examples

- [fastapi-analytics-api](https://hypequery.com/examples/fastapi-analytics-api): the same kind of datasets served from FastAPI on a ClickHouse server, with tenant-scoped tokens.
- [mcp-agent-analytics](https://hypequery.com/examples/mcp-agent-analytics): the semantic layer exposed to agents over MCP.

Tested on chDB 4.4.0 (ClickHouse engine embedded) and ClickHouse 25.8 with `hypequery` 0.1.0b3 and Python 3.12.

## Key file: analytics/backend.py

```python
"""One switch between embedded and server ClickHouse. The datasets, the CLI
and the HTTP API never change; only the executor does."""

from __future__ import annotations

import os
from pathlib import Path
from urllib.parse import urlsplit

from hypequery.datasets import DatasetClient, create_dataset_client, create_dataset_registry
from hypequery.execution import (
    ChdbExecutor,
    ClickHouseConnection,
    ClickHouseExecutor,
    create_chdb_executor,
    create_clickhouse_executor,
)

from analytics.datasets import events, tenants

DEFAULT_CHDB_PATH = str(Path(__file__).resolve().parent.parent / "analytics.chdb")


def backend_name() -> str:
    return os.environ.get("ANALYTICS_BACKEND", "chdb")


def connection_from_env() -> ClickHouseConnection:
    """The CLICKHOUSE_* variables every example in this repository shares."""

    url = urlsplit(os.environ.get("CLICKHOUSE_URL", "http://localhost:8123"))
    return ClickHouseConnection(
        host=url.hostname or "localhost",
        port=url.port or (8443 if url.scheme == "https" else 8123),
        secure=url.scheme == "https",
        database=os.environ.get("CLICKHOUSE_DATABASE", "hypequery_examples"),
        username=os.environ.get("CLICKHOUSE_USERNAME", url.username or "default"),
        password=os.environ.get("CLICKHOUSE_PASSWORD", url.password or "hypequery"),
    )


def open_executor(backend: str | None = None) -> ChdbExecutor | ClickHouseExecutor:
    if (backend or backend_name()) == "clickhouse":
        return create_clickhouse_executor(connection_from_env())
    # chDB: the engine runs in this process, on a directory next to the code.
    from analytics.sample_data import ensure_sample_data

    path = os.environ.get("CHDB_PATH", DEFAULT_CHDB_PATH)
    ensure_sample_data(path)
    return create_chdb_executor(path)


def open_client(executor: ChdbExecutor | ClickHouseExecutor) -> DatasetClient:
    # The registry resolves the `tenant` relationship for `tenant.plan` queries.
    return create_dataset_client(executor=executor, registry=create_dataset_registry(events, tenants))
```

## Related

- [A FastAPI analytics API on ClickHouse with tenant-scoped datasets](https://hypequery.com/examples/fastapi-analytics-api): Serve governed ClickHouse analytics from FastAPI: datasets defined once in Python, query endpoints mounted beside your routes, tenant fixed by the token.
- [A governed ClickHouse MCP server: agents query the semantic layer, not the database](https://hypequery.com/examples/mcp-agent-analytics): Give Claude Code, Cursor or any MCP client ClickHouse analytics through four governed tools: only published datasets, no SQL, one tenant per server.
- [p95 latency per event type in ClickHouse with a typed dataset](https://hypequery.com/recipes/p95-latency): Define p50, p95 and p99 latency once as dataset measures and get the quantile() SQL, the result on a million events, and the exactness trade-off.
- [All examples](https://hypequery.com/examples)
- [/clickhouse-python](https://hypequery.com/clickhouse-python)
- [/clickhouse-python-analytics](https://hypequery.com/clickhouse-python-analytics)