> ## Documentation Index
> Fetch the complete documentation index at: https://learn.kubit.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Recipe: OTel traces in your own ClickHouse

> Instrument your app with OpenTelemetry, write the spans to ClickHouse with the OpenTelemetry Collector, check the otel_traces table and read it in place with a Kubit Warehouse workspace.

This recipe keeps your agent traces in your own ClickHouse. Your app sends OpenTelemetry spans to your own OpenTelemetry Collector, the Collector writes them to the `otel_traces` table, and a Kubit **Warehouse** workspace reads that table in place. Kubit never receives the spans. It takes about 30 minutes.

Kubit doesn't write traces it collects into your warehouse. To have Kubit collect the spans instead, follow the [Quickstart](https://learn.kubit.ai/agent-observability/get-started/quickstart.md). If your traces are already in MLflow, Cortex Agents or self-hosted Langfuse, use [MLflow Tracing](https://learn.kubit.ai/agent-observability/integrate/mlflow.md), [Cortex Agents](https://learn.kubit.ai/agent-observability/integrate/cortex-agents.md) or [Langfuse](https://learn.kubit.ai/agent-observability/integrate/langfuse.md). For Snowflake, Databricks or BigQuery, see [OTel spans in other warehouses](https://learn.kubit.ai/warehouses/overview.md#otel-spans-in-other-warehouses).

## Before you start

* [ ] Python 3.9 or later for the app examples, and Docker to run the Collector
* [ ] A ClickHouse server Kubit can reach. Kubit supports ClickHouse 26.8, with the session time zone in UTC.
* [ ] A ClickHouse user that can create a database and tables, for the Collector
* [ ] An organization connection to that ClickHouse in Kubit, set up with your own credentials. See [Connect ClickHouse](https://learn.kubit.ai/warehouses/connect/clickhouse.md).

## Prompt for your coding agent

Paste this into Claude Code, Cursor or Codex. It changes your app and leaves the Collector and Kubit to you:

```text theme={null}
Read https://learn.kubit.ai/agent-observability/cookbook/otel-clickhouse.md.
Instrument this app with OpenTelemetry as in the step "Instrument your app".
Send spans over OTLP/HTTP to the Collector at <collector url>, with no API key.
Set user.id and session.id on the root span of each request.
```

## Steps

### 1. Instrument your app

Install the OpenTelemetry SDK and the OTLP/HTTP exporter, and point the exporter at your Collector. Your own Collector needs no API key.

```bash theme={null}
pip install opentelemetry-api opentelemetry-sdk opentelemetry-exporter-otlp-proto-http
```

```python tracing.py theme={null}
import os

from opentelemetry import trace
from opentelemetry.exporter.otlp.proto.http.trace_exporter import OTLPSpanExporter
from opentelemetry.sdk.resources import Resource
from opentelemetry.sdk.trace import TracerProvider
from opentelemetry.sdk.trace.export import BatchSpanProcessor

exporter = OTLPSpanExporter(
    endpoint=os.environ.get("OTEL_COLLECTOR_URL", "http://localhost:4318") + "/v1/traces",
)

provider = TracerProvider(
    resource=Resource.create(
        {
            "service.name": "support-agent",
            "service.version": "1.0.0",
            "deployment.environment": "production",
        }
    )
)
provider.add_span_processor(BatchSpanProcessor(exporter))
trace.set_tracer_provider(provider)
```

Import `tracing` first in your entrypoint, before any LLM client is created. Then add the GenAI instrumentor for your LLM client with message content turned on, and set `user.id` and `session.id` on the root span of each request. [Trace LLM calls](https://learn.kubit.ai/agent-observability/cookbook/manual-otel.md#trace-llm-calls) and [Add user and session IDs](https://learn.kubit.ai/agent-observability/cookbook/manual-otel.md#add-user-and-session-ids) have the code. For Node, follow [Manual OpenTelemetry](https://learn.kubit.ai/agent-observability/cookbook/manual-otel.md#steps-for-node) with the exporter `url` set to `http://<collector>:4318/v1/traces` and no `x-api-key` header.

The Kubit SDKs, `kubit-otel` and `@kubit-ai/otel`, aren't needed here. They send to Kubit's endpoint by default, and their only addition is a span filter.

### 2. Run the Collector

The ClickHouse exporter ships in the Collector's **contrib** distribution, and it's beta for traces. Save this as `otel-collector.yaml`:

```yaml otel-collector.yaml theme={null}
receivers:
  otlp:
    protocols:
      grpc:
        endpoint: 0.0.0.0:4317
      http:
        endpoint: 0.0.0.0:4318

exporters:
  clickhouse:
    endpoint: tcp://clickhouse:9000?dial_timeout=10s
    database: otel
    username: ${env:CLICKHOUSE_USER}
    password: ${env:CLICKHOUSE_PASSWORD}
    traces_table_name: otel_traces
    create_schema: true
    ttl: 720h
    sending_queue:
      batch:
        min_size: 5000
        flush_timeout: 5s

service:
  pipelines:
    traces:
      receivers: [otlp]
      exporters: [clickhouse]
```

Set `endpoint` to your ClickHouse native port (`tcp://`, 9000) or HTTP port (`http://`, 8123), and add `?secure=true` for TLS. `ttl: 720h` drops spans after 30 days. Leave it out to keep them. Batching happens in the exporter's `sending_queue`, so you don't need a `batch` processor. The [ClickHouse exporter README](https://github.com/open-telemetry/opentelemetry-collector-contrib/blob/v0.165.0/exporter/clickhouseexporter/README.md) lists every option.

Run the Collector with the ClickHouse credentials in an env file:

```bash theme={null}
docker run -d --name otelcol \
  --env-file clickhouse.env \
  -v "$PWD/otel-collector.yaml:/etc/otelcol-contrib/config.yaml:ro" \
  -p 4317:4317 -p 4318:4318 \
  otel/opentelemetry-collector-contrib:0.165.0
```

When it's ready, `docker logs otelcol` ends with `Everything is ready. Begin running and processing data.` With `create_schema: true`, the Collector creates the `otel` database, the `otel_traces` table and a trace ID lookup table, `otel_traces_trace_id_ts`, on its first start.

### 3. Send a test trace

This script sends one agent run: a root span with a user and session, a model call with tokens and messages, and a tool call that failed. It sets the GenAI attributes by hand, so it needs no LLM key.

```python send_test_trace.py theme={null}
import json

import tracing  # sets up the provider
from opentelemetry import trace
from opentelemetry.trace import Status, StatusCode

tracer = trace.get_tracer("support-agent")

with tracer.start_as_current_span("invoke_agent support-agent") as root:
    root.set_attribute("gen_ai.operation.name", "invoke_agent")
    root.set_attribute("user.id", "user-123")
    root.set_attribute("session.id", "conversation-456")
    with tracer.start_as_current_span("chat gpt-4o") as chat:
        chat.set_attribute("gen_ai.operation.name", "chat")
        chat.set_attribute("gen_ai.request.model", "gpt-4o")
        chat.set_attribute("gen_ai.response.model", "gpt-4o-2024-08-06")
        chat.set_attribute("gen_ai.usage.input_tokens", 412)
        chat.set_attribute("gen_ai.usage.output_tokens", 38)
        chat.set_attribute("gen_ai.input.messages", json.dumps([{"role": "user", "parts": [{"type": "text", "content": "Where is my order 1042?"}]}]))
        chat.set_attribute("gen_ai.output.messages", json.dumps([{"role": "assistant", "parts": [{"type": "tool_call", "name": "lookup_order", "arguments": {"order_id": "1042"}}]}]))
    with tracer.start_as_current_span("execute_tool lookup_order") as tool:
        tool.set_attribute("gen_ai.operation.name", "execute_tool")
        tool.set_attribute("gen_ai.tool.name", "lookup_order")
        tool.set_attribute("gen_ai.tool.call.arguments", json.dumps({"order_id": "1042"}))
        tool.set_status(Status(StatusCode.ERROR, "TimeoutError: order service did not answer in 30 s"))

trace.get_tracer_provider().force_flush()
print("sent trace", format(root.get_span_context().trace_id, "032x"))
```

```text Output theme={null}
sent trace f828e90df988c4193b4236bd6faa55da
```

`force_flush()` sends the spans before the script exits. The exporter then writes them to ClickHouse within its 5-second `flush_timeout`.

### 4. Check the table

The exporter creates `otel_traces` with one row per span. These are the columns Kubit reads:

| **Column** | **ClickHouse type** | **What it holds** |
| - | - | - |
| `Timestamp` | `DateTime64(9)` | The span's start, in UTC, to the nanosecond |
| `TraceId`, `SpanId` | `String` | Hex trace and span IDs |
| `ParentSpanId` | `String` | The parent's span ID. Empty on a root span. |
| `SpanName` | `LowCardinality(String)` | The span name, such as `chat gpt-4o` |
| `ServiceName` | `LowCardinality(String)` | The resource's `service.name` |
| `ResourceAttributes`, `SpanAttributes` | `Map(LowCardinality(String), String)` | Resource and span attributes. Every value is stored as a string. |
| `Duration` | `UInt64` | The span's length in nanoseconds |
| `StatusCode` | `LowCardinality(String)` | `Unset`, `Ok` or `Error` |
| `StatusMessage` | `String` | The error message on a failed span |

The table also holds `TraceState`, `SpanKind`, `ScopeName`, `ScopeVersion`, and `Events` and `Links` as nested arrays. It's partitioned by day and ordered by `ServiceName`, `SpanName` and `Timestamp`. To see the full definition, run `SHOW CREATE TABLE otel.otel_traces`.

Check that the test trace landed whole:

```sql Spans per trace, last day theme={null}
SELECT
    TraceId,
    count() AS spans,
    countIf(ParentSpanId = '') AS roots,
    countIf(StatusCode = 'Error') AS error_spans,
    min(Timestamp) AS first_start,
    max(addNanoseconds(Timestamp, Duration)) AS last_end
FROM otel.otel_traces
WHERE Timestamp >= now() - INTERVAL 1 DAY
GROUP BY TraceId
ORDER BY first_start DESC
LIMIT 20;
```

```text Output theme={null}
   ┌─TraceId──────────────────────────┬─spans─┬─roots─┬─error_spans─┬───────────────────first_start─┬──────────────────────last_end─┐
1. │ f828e90df988c4193b4236bd6faa55da │     3 │     1 │           1 │ 2026-11-17 17:11:41.768067000 │ 2026-11-17 17:11:41.768285000 │
   └──────────────────────────────────┴───────┴───────┴─────────────┴───────────────────────────────┴───────────────────────────────┘
```

Each trace should have one root. More than one root means some parent spans are missing, often because the app exited before it flushed them.

### 5. Map the table in Kubit

Create a workspace and pick the **Warehouse** origin (`OTel traces in Databricks, Snowflake, BigQuery or ClickHouse`). Pick the organization connection to your ClickHouse. See [Set up your organization](https://learn.kubit.ai/start-here/admin.md).

![Create Workspace dialog with Workspace Name Support Agent, Workspace Timezone Europe/Athens (UTC+2\*) and Origin Warehouse, with the hint OTel traces in Databricks, Snowflake, BigQuery or ClickHouse, above the Continue button.](https://mintcdn.com/kubit/BtadhRbx_tTjeUxn/images/agent-observability/integrate/create-workspace-warehouse.png?fit=max\&auto=format\&n=BtadhRbx_tTjeUxn\&q=85\&s=7c04e9f677594f0e9159b7da6e620cb2)

The **Warehouse** origin has no preset, so you map `otel_traces` yourself on the **Schema** tab, with the Modeler agent or by hand. [Modeler](https://learn.kubit.ai/warehouses/modeler.md) has both flows. Map it like this:

| **Model part** | **Value** |
| - | - |
| **Fact table** | `otel.otel_traces` |
| **Event** | `SpanName` |
| **Timestamp** | `Timestamp` |
| **Subjects** | `TraceId` (Trace), `SpanId` (Span), and the **Session Id** and **User Id** derived fields below |

Then add derived fields with **Raw SQL**. In a raw SQL expression, reference a column with `@<column>`. Raw SQL needs a custom connection, one set up with your own warehouse credentials.

| **Derived field** | **Raw SQL** |
| - | - |
| End time | `addNanoseconds(@Timestamp, @Duration)` |
| Is error | `@StatusCode = 'Error'` |
| Model | `nullIf(@SpanAttributes['gen_ai.response.model'], '')` |
| Input tokens | `toInt64OrNull(@SpanAttributes['gen_ai.usage.input_tokens'])` |
| Output tokens | `toInt64OrNull(@SpanAttributes['gen_ai.usage.output_tokens'])` |
| Input messages | `nullIf(@SpanAttributes['gen_ai.input.messages'], '')` |
| Response | `nullIf(@SpanAttributes['gen_ai.output.messages'], '')` |
| Tool name | `nullIf(@SpanAttributes['gen_ai.tool.name'], '')` |
| Tool arguments | `nullIf(@SpanAttributes['gen_ai.tool.call.arguments'], '')` |
| Tool result | `nullIf(@SpanAttributes['gen_ai.tool.call.result'], '')` |
| Session ID | `nullIf(@SpanAttributes['session.id'], '')` |
| User ID | `nullIf(@SpanAttributes['user.id'], '')` |

To check the expressions before you save them, run them against the table without the `@`:

```sql Check the derived fields theme={null}
SELECT
    SpanName,
    addNanoseconds(Timestamp, Duration) AS end_time,
    StatusCode = 'Error' AS is_error,
    nullIf(SpanAttributes['gen_ai.response.model'], '') AS model,
    toInt64OrNull(SpanAttributes['gen_ai.usage.input_tokens']) AS input_tokens,
    nullIf(SpanAttributes['gen_ai.tool.name'], '') AS tool_name,
    nullIf(SpanAttributes['session.id'], '') AS session_id
FROM otel.otel_traces
WHERE TraceId = '<trace id>'
ORDER BY Timestamp;
```

```text Output theme={null}
   ┌─SpanName───────────────────┬──────────────────────end_time─┬─is_error─┬─model─────────────┬─input_tokens─┬─tool_name────┬─session_id───────┐
1. │ invoke_agent support-agent │ 2026-11-17 17:11:41.768285000 │        0 │ ᴺᵁᴸᴸ              │         ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ         │ conversation-456 │
2. │ chat gpt-4o                │ 2026-11-17 17:11:41.768201000 │        0 │ gpt-4o-2024-08-06 │          412 │ ᴺᵁᴸᴸ         │ ᴺᵁᴸᴸ             │
3. │ execute_tool lookup_order  │ 2026-11-17 17:11:41.768273000 │        1 │ ᴺᵁᴸᴸ              │         ᴺᵁᴸᴸ │ lookup_order │ ᴺᵁᴸᴸ             │
   └────────────────────────────┴───────────────────────────────┴──────────┴───────────────────┴──────────────┴──────────────┴──────────────────┘
```

### 6. Give the columns trace roles

On the **Fields** tab, click **Edit trace roles**, pick a role for each field from its roles menu, and click **Save roles**. Trace roles turn on agent **Activity**, the Evaluator and Flows for the workspace.

| **Role** | **Required** | **Field** |
| - | - | - |
| **Container** | Required. Must be an enabled subject. | `TraceId` |
| **Node** | Required | `SpanId` |
| **Parent** | Required | `ParentSpanId` |
| **Start Time** | Required | `Timestamp` |
| **End Time** | Required | End time |
| **Span Name** | Required | `SpanName` |
| **Is Error** | Required | Is error |
| **Model**, **Input Tokens**, **Output Tokens** | Optional | Model, Input tokens, Output tokens |
| **Input Messages**, **Response Text** | Optional | Input messages, Response |
| **Tool Name**, **Tool Arguments**, **Tool Result** | Optional | Tool name, Tool arguments, Tool result |
| **Session Id**, **User Id** | Optional | Session ID, User ID |

Set **Run completeness** to **Every run in the source is finished**. The Collector writes each span once, when it ends.

### 7. Label the traces

The Evaluator needs at least one messages, response or tool field with a role, which the mapping above gives it. On the **Evaluator** page, pick a schema under **Evaluator results**, where Polaris writes its labels and scores next to your traces. See [Where Polaris results are stored](https://learn.kubit.ai/warehouses/overview.md#where-polaris-results-are-stored).

Then pick a **Window** of days and click **Label conversations**. Nothing is scheduled, so start a run whenever you want new traces labelled. A run reads your warehouse, at your cost.

**Constraint:** One run labels at most 5,000 conversations. For more, label shorter windows.

## Verify

The setup works end to end when all of these are true:

* The [spans-per-trace query](#check-the-table) lists your app's traces, each with one root.
* The Kubit workspace no longer shows `No data yet. Finish setting up your model.`
* **Activity** lists the same traces, and an open trace shows its spans as a tree, with the failed tool call marked as an error.
* After a labelling run, the **Labels** column shows labels instead of `Not analysed yet`.

## If it fails

| **Symptom** | **Cause** | **Fix** |
| - | - | - |
| The app prints `Failed to export spans batch due to timeout, max retries or shutdown.` after `Transient error … Failed to establish a new connection` | The app can't reach the Collector | Check `OTEL_COLLECTOR_URL` and that port 4318 is open |
| The Collector stops with `failed to start "clickhouse" exporter: create database: dial tcp: lookup clickhouse … no such host` | The Collector can't reach ClickHouse at `endpoint` | Fix the host in `endpoint`, or put both containers on one network |
| `otel_traces` has rows but `SpanAttributes` has no `gen_ai.input.messages` | Message content capture is off | Set `OTEL_INSTRUMENTATION_GENAI_CAPTURE_MESSAGE_CONTENT=true` for the instrumentor |
| A trace has more than one root | Parent spans weren't flushed | Call `force_flush()` before short-lived processes exit |
| Kubit rejects the ClickHouse session | The session's time zone isn't UTC | Set the session to UTC |
| `Choose a Container field: the run each step belongs to. Agent roles need one.` | Agent roles are set without a **Container** | Give **Container** to `TraceId` |
| `Choose a Parent field: the ID of each row's parent.` | No field has the **Parent** role | Give **Parent** to `ParentSpanId` |
| `Set up this workspace's agent trace profile in the Modeler first.` | No trace roles are saved | Give the columns trace roles on the **Fields** tab |
| `Map a prompt, response, messages or tool field in the agent trace profile so the Evaluator has something to read.` | No messages, response or tool field has a role | Give **Input Messages** or **Response Text** to a field |
| `Choose where in your warehouse to keep Evaluator results first.` | No results schema is picked | Pick one under **Evaluator results** |
| `The window holds more than 5,000 conversations, the most one run can label. Choose a shorter window.` | The **Window** is too long | Label a shorter window |

## Next Steps

* [Modeler](https://learn.kubit.ai/warehouses/modeler.md): fact tables, derived fields and the Modeler agent.
* [Polaris](https://learn.kubit.ai/agent-observability/use/polaris.md): what Polaris adds to your traces.
* [Quickstart](https://learn.kubit.ai/agent-observability/get-started/quickstart.md): send traces to an Agent workspace instead.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.