Broadcast to ClickHouse | OpenRouter Observability | OpenRouter

ClickHouse is a fast, open-source columnar database for real-time analytics. OpenRouter can stream traces directly to your ClickHouse database for high-performance analytics and custom dashboards.

Step 1: Create the traces table

Before connecting OpenRouter, create the OPENROUTER_TRACES table in your ClickHouse database. You can find the exact SQL in the OpenRouter dashboard when configuring the destination:

ClickHouse Setup Instructions

Step 2: Set up permissions

Ensure your ClickHouse user has CREATE TABLE permissions:

1 GRANT CREATE TABLE ON your_database.* TO your_database_user;

Step 3: Enable Broadcast in OpenRouter

Go to Settings > Broadcast and toggle Enable Broadcast.

Enable Broadcast

Step 4: Configure ClickHouse

Click the edit icon next to ClickHouse and enter:

ClickHouse Configuration

Host: Your ClickHouse HTTP endpoint (e.g., https://clickhouse.example.com:8123)
Database: Target database name (default: default)
Table: Table name (default: OPENROUTER_TRACES)
Username: ClickHouse username for authentication (defaults to default)
Password: ClickHouse password for authentication

For ClickHouse Cloud, your host URL is typically https://{instance}.{region}.clickhouse.cloud:8443. You can find this in your ClickHouse Cloud console under Connect.

Step 5: Test and save

Click Test Connection to verify the setup. The configuration only saves if the test passes.

Step 6: Send a test trace

Make an API request through OpenRouter and query your ClickHouse table to verify the trace was received.

Example queries

Cost analysis by model

1 SELECT
2   toDate(TIMESTAMP) as day,
3   MODEL,
4   sum(TOTAL_COST) as total_cost,
5   sum(TOTAL_TOKENS) as total_tokens,
6   count() as request_count
7 FROM OPENROUTER_TRACES
8 WHERE TIMESTAMP >= now() - INTERVAL 30 DAY
9   AND STATUS = 'ok'
10   AND SPAN_TYPE = 'GENERATION'
11 GROUP BY day, MODEL
12 ORDER BY day DESC, total_cost DESC;

User activity analysis

1 SELECT
2   USER_ID,
3   uniqExact(TRACE_ID) as trace_count,
4   uniqExact(SESSION_ID) as session_count,
5   sum(TOTAL_TOKENS) as total_tokens,
6   sum(TOTAL_COST) as total_cost,
7   avg(DURATION_MS) as avg_duration_ms
8 FROM OPENROUTER_TRACES
9 WHERE TIMESTAMP >= now() - INTERVAL 7 DAY
10   AND SPAN_TYPE = 'GENERATION'
11 GROUP BY USER_ID
12 ORDER BY total_cost DESC;

Error analysis

1 SELECT
2   TRACE_ID,
3   TIMESTAMP,
4   MODEL,
5   LEVEL,
6   FINISH_REASON,
7   METADATA,
8   INPUT,
9   OUTPUT
10 FROM OPENROUTER_TRACES
11 WHERE STATUS = 'error'
12   AND TIMESTAMP >= now() - INTERVAL 1 HOUR
13 ORDER BY TIMESTAMP DESC;

Provider performance comparison

1 SELECT
2   PROVIDER_NAME,
3   MODEL,
4   avg(DURATION_MS) as avg_duration_ms,
5   quantile(0.5)(DURATION_MS) as p50_duration_ms,
6   quantile(0.95)(DURATION_MS) as p95_duration_ms,
7   count() as request_count
8 FROM OPENROUTER_TRACES
9 WHERE TIMESTAMP >= now() - INTERVAL 7 DAY
10   AND STATUS = 'ok'
11   AND SPAN_TYPE = 'GENERATION'
12 GROUP BY PROVIDER_NAME, MODEL
13 HAVING request_count >= 10
14 ORDER BY avg_duration_ms;

Usage by API key

1 SELECT
2   API_KEY_NAME,
3   uniqExact(TRACE_ID) as trace_count,
4   sum(TOTAL_COST) as total_cost,
5   sum(PROMPT_TOKENS) as prompt_tokens,
6   sum(COMPLETION_TOKENS) as completion_tokens
7 FROM OPENROUTER_TRACES
8 WHERE TIMESTAMP >= now() - INTERVAL 30 DAY
9   AND SPAN_TYPE = 'GENERATION'
10 GROUP BY API_KEY_NAME
11 ORDER BY total_cost DESC;

Accessing JSON columns

ClickHouse stores JSON data as strings. Use JSONExtract functions to query nested fields:

1 SELECT
2   TRACE_ID,
3   JSONExtractString(METADATA, 'custom_field') as custom_value,
4   JSONExtractString(ATTRIBUTES, 'gen_ai.request.model') as requested_model
5 FROM OPENROUTER_TRACES
6 WHERE JSONHas(METADATA, 'custom_field');

To parse input messages:

1 SELECT
2   TRACE_ID,
3   JSONExtractString(
4     JSONExtractRaw(INPUT, 'messages'),
5     1, 'role'
6   ) as first_message_role,
7   JSONExtractString(
8     JSONExtractRaw(INPUT, 'messages'),
9     1, 'content'
10   ) as first_message_content
11 FROM OPENROUTER_TRACES
12 WHERE SPAN_TYPE = 'GENERATION'
13 LIMIT 10;

Schema design

Typed columns

The schema extracts commonly-queried fields as typed columns for efficient filtering and aggregation:

Identifiers: TRACE_ID, USER_ID, SESSION_ID, etc.
Timestamps: DateTime64 for time-series analysis with millisecond precision
Model Info: For cost and performance analysis
Metrics: Tokens and costs for billing

String columns for JSON

Less commonly-accessed and variable-structure data is stored as JSON strings:

ATTRIBUTES: Full OTEL attribute set
INPUT/OUTPUT: Variable message structures
METADATA: User-defined key-values
MODEL_PARAMETERS: Model-specific configurations

Use ClickHouse’s JSONExtract* functions to query these fields.

1	SELECT
2	toDate(TIMESTAMP) as day,
3	MODEL,
4	sum(TOTAL_COST) as total_cost,
5	sum(TOTAL_TOKENS) as total_tokens,
6	count() as request_count
7	FROM OPENROUTER_TRACES
8	WHERE TIMESTAMP >= now() - INTERVAL 30 DAY
9	AND STATUS = 'ok'
10	AND SPAN_TYPE = 'GENERATION'
11	GROUP BY day, MODEL
12	ORDER BY day DESC, total_cost DESC;

1	SELECT
2	USER_ID,
3	uniqExact(TRACE_ID) as trace_count,
4	uniqExact(SESSION_ID) as session_count,
5	sum(TOTAL_TOKENS) as total_tokens,
6	sum(TOTAL_COST) as total_cost,
7	avg(DURATION_MS) as avg_duration_ms
8	FROM OPENROUTER_TRACES
9	WHERE TIMESTAMP >= now() - INTERVAL 7 DAY
10	AND SPAN_TYPE = 'GENERATION'
11	GROUP BY USER_ID
12	ORDER BY total_cost DESC;

1	SELECT
2	TRACE_ID,
3	TIMESTAMP,
4	MODEL,
5	LEVEL,
6	FINISH_REASON,
7	METADATA,
8	INPUT,
9	OUTPUT
10	FROM OPENROUTER_TRACES
11	WHERE STATUS = 'error'
12	AND TIMESTAMP >= now() - INTERVAL 1 HOUR
13	ORDER BY TIMESTAMP DESC;

1	SELECT
2	PROVIDER_NAME,
3	MODEL,
4	avg(DURATION_MS) as avg_duration_ms,
5	quantile(0.5)(DURATION_MS) as p50_duration_ms,
6	quantile(0.95)(DURATION_MS) as p95_duration_ms,
7	count() as request_count
8	FROM OPENROUTER_TRACES
9	WHERE TIMESTAMP >= now() - INTERVAL 7 DAY
10	AND STATUS = 'ok'
11	AND SPAN_TYPE = 'GENERATION'
12	GROUP BY PROVIDER_NAME, MODEL
13	HAVING request_count >= 10
14	ORDER BY avg_duration_ms;

1	SELECT
2	API_KEY_NAME,
3	uniqExact(TRACE_ID) as trace_count,
4	sum(TOTAL_COST) as total_cost,
5	sum(PROMPT_TOKENS) as prompt_tokens,
6	sum(COMPLETION_TOKENS) as completion_tokens
7	FROM OPENROUTER_TRACES
8	WHERE TIMESTAMP >= now() - INTERVAL 30 DAY
9	AND SPAN_TYPE = 'GENERATION'
10	GROUP BY API_KEY_NAME
11	ORDER BY total_cost DESC;

1	SELECT
2	TRACE_ID,
3	JSONExtractString(METADATA, 'custom_field') as custom_value,
4	JSONExtractString(ATTRIBUTES, 'gen_ai.request.model') as requested_model
5	FROM OPENROUTER_TRACES
6	WHERE JSONHas(METADATA, 'custom_field');

1	SELECT
2	TRACE_ID,
3	JSONExtractString(
4	JSONExtractRaw(INPUT, 'messages'),
5	1, 'role'
6	) as first_message_role,
7	JSONExtractString(
8	JSONExtractRaw(INPUT, 'messages'),
9	1, 'content'
10	) as first_message_content
11	FROM OPENROUTER_TRACES
12	WHERE SPAN_TYPE = 'GENERATION'
13	LIMIT 10;