Free Clickhouse

ClickHouse OLAP MCP Free tier

Symptom

You connect an assistant to your analytics data and ask what should be a two-second question: which acquisition channel had the best week-one retention last month? It runs a SELECT with a LIMIT 1000, gets a thousand rows of raw pageviews, decides that isn't enough, raises the limit, and either fills its context window with event rows or gives you an answer computed from a sample it chose arbitrarily.

The model isn't being stupid. It's doing the only thing available when the database it's been handed is a row store holding raw events, or when the tool it's been given returns rows rather than answers.

The fix is not a better prompt. It's putting the aggregation on the database side of the wire. Ask ClickHouse the retention question and it reads three columns out of a hundred million rows, does the grouping in vectorised batches, and hands back nine numbers. Nine numbers fit in a context window. A hundred million rows never will.

SELECT
    channel,
    uniqExact(user_id)                                          AS cohort,
    uniqExactIf(user_id, days_since_signup BETWEEN 7 AND 13)    AS week_one,
    round(100 * week_one / cohort, 1)                           AS pct
FROM events_enriched
WHERE signup_month = '2024-02-01'
GROUP BY channel
ORDER BY pct DESC;

That query is the entire payload. This repo is about making it the thing your agent actually runs.

Install

Create an instance at freebase.cloud, choose the ClickHouse engine, then Settings → MCP → New Token and copy the URL. The token lives in the URL path, so no client needs custom headers.

claude mcp add --transport http events https://freebase.cloud/api/mcp/YOUR_TOKEN

The connection is called events throughout this README; your tool names will follow whatever you named yours.

// Claude Code .mcp.json — omitting "type" is a startup error
{ "mcpServers": { "events": { "type": "http", "url": "https://freebase.cloud/api/mcp/YOUR_TOKEN" } } }

// Cursor .cursor/mcp.json — no "type" key at all
{ "mcpServers": { "events": { "url": "https://freebase.cloud/api/mcp/YOUR_TOKEN" } } }

// VS Code / Copilot Chat .vscode/mcp.json — "servers", not "mcpServers"
{ "servers": { "events": { "type": "http", "url": "https://freebase.cloud/api/mcp/YOUR_TOKEN" } } }

// Windsurf ~/.codeium/windsurf/mcp_config.json — "serverUrl"
{ "mcpServers": { "events": { "serverUrl": "https://freebase.cloud/api/mcp/YOUR_TOKEN" } } }

// Warp — paste into Settings > AI > Manage MCP servers, no wrapper object
{ "events": { "url": "https://freebase.cloud/api/mcp/YOUR_TOKEN" } }

from langchain_mcp_adapters.client import MultiServerMCPClient
client = MultiServerMCPClient({"events": {"transport": "http", "url": "https://freebase.cloud/api/mcp/YOUR_TOKEN"}})
tools = await client.get_tools()

The table

Web analytics: one row per event, a few million a week, queried by date range and by property. The DDL runs unchanged on a free ClickHouse 24.1 instance.

CREATE TABLE events (
    event_time   DateTime,
    event_date   Date DEFAULT toDate(event_time),
    site         LowCardinality(String),
    event_type   LowCardinality(String),   -- page_view | click | signup | purchase
    user_id      UInt64,
    session_id   UUID,
    path         String,
    referrer     String,
    channel      LowCardinality(String),
    country      LowCardinality(String),
    device       LowCardinality(String),
    duration_ms  UInt32,
    revenue_cents UInt32 DEFAULT 0
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (site, event_date, event_type, user_id);

Three decisions carry all the weight:

ORDER BY is the primary index. ClickHouse stores rows sorted by that tuple and builds a sparse index over it. Put the columns you filter on first, in decreasing order of how often you filter on them. Getting this wrong doesn't produce an error — it produces a table that reads ten times more data than it needs to, forever.

PARTITION BY is for dropping data, not for speed. Monthly partitions mean ALTER TABLE events DROP PARTITION '202401' is instant. Partitioning by day on a small table just creates thousands of tiny parts and makes merges worse.

LowCardinality(String) on anything with a bounded set of values. Country, channel, device, event type. It's a dictionary encoding, and on a column with a few hundred distinct values it's a large reduction in bytes read.

Five questions, five queries

Full versions with sample output are in examples/analytics.sql; each one runs as written against a hosted instance.

1. Traffic by day, last fortnight. The WHERE clause hits the sort key, so ClickHouse skips whole granules rather than scanning.

SELECT event_date, count() AS events, uniq(session_id) AS sessions
FROM events
WHERE site = 'shop' AND event_date >= today() - 14
GROUP BY event_date ORDER BY event_date;

2. Top pages, but only one row per section. LIMIT n BY expr has no standard-SQL equivalent and replaces a window function plus a subquery.

SELECT splitByChar('/', path)[2] AS section, path, count() AS views
FROM events WHERE event_type = 'page_view' AND event_date >= today() - 7
GROUP BY section, path
ORDER BY section, views DESC
LIMIT 3 BY section;

3. A conversion funnel. windowFunnel returns, per user, how deep into an ordered sequence of conditions they got within a time window.

SELECT level, count() AS users FROM (
    SELECT user_id,
           windowFunnel(3600)(event_time,
               event_type = 'page_view',
               event_type = 'click',
               event_type = 'purchase') AS level
    FROM events WHERE event_date >= today() - 30
    GROUP BY user_id
) GROUP BY level ORDER BY level;

4. The last thing each user did. argMax picks the value of one column at the row where another is maximal — one pass, no self-join.

SELECT user_id, argMax(path, event_time) AS last_path, max(event_time) AS seen
FROM events WHERE event_date >= today() - 1
GROUP BY user_id ORDER BY seen DESC LIMIT 20;

5. Revenue with a rolling average. Window functions work as you'd expect from Postgres.

SELECT event_date, revenue,
       avg(revenue) OVER (ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM (
    SELECT event_date, sum(revenue_cents) / 100 AS revenue
    FROM events WHERE event_type = 'purchase'
    GROUP BY event_date
)
ORDER BY event_date;

Materialized views for the questions you ask daily

A ClickHouse materialized view is an insert trigger, not a cached query. Rows arriving in events are transformed and written into the target table as a side effect of the insert. It never re-reads history, so creating one does nothing to existing data — you backfill that yourself with a single INSERT INTO … SELECT.

CREATE TABLE daily_traffic (
    event_date  Date,
    site        LowCardinality(String),
    channel     LowCardinality(String),
    events      UInt64,
    sessions    AggregateFunction(uniq, UUID),
    revenue     UInt64
) ENGINE = AggregatingMergeTree
ORDER BY (site, event_date, channel);

CREATE MATERIALIZED VIEW daily_traffic_mv TO daily_traffic AS
SELECT event_date, site, channel,
       count()             AS events,
       uniqState(session_id) AS sessions,
       sum(revenue_cents)  AS revenue
FROM events
GROUP BY event_date, site, channel;

Reading it back requires the matching -Merge combinator, and this is where people get a wrong number and blame the view:

SELECT site, channel, sum(events) AS events, uniqMerge(sessions) AS sessions
FROM daily_traffic
WHERE event_date >= today() - 30
GROUP BY site, channel;

uniqState stores the partial sketch; uniqMerge combines the sketches. Selecting the raw sessions column without uniqMerge returns unreadable binary state. Put that fact in the table's annotation — it's the single most common ClickHouse mistake a model makes.

ClickHouse SQL that trips agents up

Most of these belong in events_annotate_table rather than in a system prompt, because the annotation is stored server-side with the connection itself and comes back on every reconnect.

Tool reference

Four tools per connection — the same set every freebase.cloud engine exposes, prefixed with the name you chose:

An annotation worth writing on day one:

{
  "table": "daily_traffic",
  "description": "Pre-aggregated daily rollup, populated by daily_traffic_mv as a side effect of inserts into events. It contains no history from before the view was created. `sessions` is an AggregateFunction column: you MUST read it with uniqMerge(sessions), never as a plain column. Prefer this table over `events` for anything at daily granularity.",
  "format": {
    "structure": "event_date, site, channel, events UInt64, sessions AggregateFunction(uniq, UUID), revenue UInt64 (cents)"
  }
}

The last sentence steers the model to the cheap table without you having to say so in every conversation.

What the free tier is for

Development, prototyping and small production analytics on the free ClickHouse tier. That covers a genuine amount of work — a product analytics prototype, a dashboard behind an internal tool, a place to land a few million events and actually explore them — and it does not cover a warehouse. Nothing here promises availability targets or a retention policy; no such figures exist to quote.

Practical notes:

• You reach the data through the HTTP query API and through MCP. The clickhouse-client and port 9000 references above describe which SQL dialect you are writing — ClickHouse 24.1.5 — and should not be read as an open native-protocol socket.
• Bulk loading works best in batches. ClickHouse writes a new part per insert and merges in the background, so ten thousand single-row inserts is the pathological case. examples/load_events.mjs batches at 5,000 rows.
• A query with a bad WHERE will read the whole table here exactly as it would anywhere. If something is slow, check whether the filter touches the ORDER BY prefix before assuming the free tier is the problem.

FAQ

Is this real ClickHouse? ClickHouse 24.1.5. SELECT version() will confirm it.

Can I point Grafana or Metabase at it? Both speak the HTTP interface, which is available. Grafana's official ClickHouse plugin is the usual route.

Do I have to run anything from this repo? No. There is no server here — freebase.cloud hosts the MCP endpoint. These files are the schema, the queries and the loader.

Can I import CSV or JSON in bulk? Yes — INSERT INTO … FORMAT CSV and FORMAT JSONEachRow both work over the HTTP interface. The Node loader uses JSONEachRow.

How does this compare to ClickHouse Cloud? ClickHouse Cloud is the managed product from the company that builds the database, with separated storage and compute, autoscaling and support. This is a free instance for development and small workloads. Different jobs.

Links

• Free ClickHouse cloud instance
• Connect Claude to ClickHouse
• MergeTree engine documentation
• Aggregate function reference
• MCP specification

MIT. freebase.cloud is an independent service and is not affiliated with ClickHouse, Inc., Anthropic, Grafana Labs, or Microsoft.