Quickly Build an Observability Solution with ClickHouse + Grafana: Distributed Tracing Guide

Introduction

ClickHouse treats observability as a real‑time analytics problem. Its high‑performance columnar engine and rich SQL functions make it suitable for storing logs, metrics, and especially trace data. This guide shows how to collect, store, and query trace data using OpenTelemetry and visualize it with Grafana.

What Are Traces?

Telemetry data includes logs, metrics, and traces. A trace consists of multiple spans that form a tree, each span representing a unit of work with timing, metadata, and optional log messages. The article includes a diagram of a trace tree.

What Is OpenTelemetry?

OpenTelemetry is a vendor‑neutral open‑source framework that provides SDKs, APIs, and components for ingesting, transforming, and exporting observability data. Core components include a set of specifications, language‑specific libraries/SDKs, and the OTEL Collector, which receives data via protocols like OTLP and can export to backends such as ClickHouse.

Why Choose ClickHouse?

Trace data maps naturally to a table where each span is a row. ClickHouse offers high compression, a powerful SQL interface, and low‑latency queries. It powers many commercial observability solutions (e.g., Signoz, Highlight, qryn, BetterStack) and large‑scale internal platforms at Uber, Cloudflare, and GitLab.

Instrumentation Libraries

The article focuses on trace capture. It shows a minimal Python Flask example that acquires a tracer, creates a span, records an attribute, and returns a random dice roll:

# These are the necessary import declarations
from opentelemetry import trace
from random import randint
from flask import Flask, request

# Acquire a tracer
tracer = trace.get_tracer(__name__)

app = Flask(__name__)

@app.route("/rolldice")
def roll_dice():
    return str(do_roll())

def do_roll():
    # This creates a new span that's the child of the current one
    with tracer.start_as_current_span("do_roll") as rollspan:
        res = randint(1, 6)
        rollspan.set_attribute("roll.value", res)
        return res

Readers are encouraged to consult language‑specific documentation for more details.

OTEL Collector

The collector receives data via receivers (e.g., OTLP over gRPC/HTTP), processes it with processors (batching, span‑metrics), and exports it to destinations like ClickHouse. The article provides a simplified pipeline diagram and notes that the collector can run as a gateway or an agent.

ClickHouse Support in the Exporter

The ClickHouse exporter is part of the contrib image. Users must pre‑create the target database and tables because the exporter’s schema is hard‑coded. The exporter is in alpha, so it is distributed as an extension of the core collector image.

Example Application

The OpenTelemetry demo is a multi‑service e‑commerce app (catalog, recommendation, payment, etc.) that generates logs, traces, and metrics. The article forks the demo, adds ClickHouse support, and provides Docker‑Compose and Helm deployment instructions.

Local Deployment

git clone https://github.com/ClickHouse/opentelemetry-demo.git
cd opentelemetry-demo/
docker compose up --no-build

The provided docker‑compose.yml includes a ClickHouse service named clickhouse.

Kubernetes Deployment

Deploy the demo with Helm, using a custom values.yaml that points the collector’s exporter to a ClickHouse Cloud instance.

helm install -f values.yaml my-otel-demo open-telemetry/opentelemetry-demo

Integrating ClickHouse

Trace data is sent to ClickHouse by adding an exporter block to otel-config-extras.yaml:

exporters:
  clickhouse:
    endpoint: tcp://clickhouse:9000?dial_timeout=10s&compress=lz4
    database: default
    ttl_days: 3
    traces_table_name: otel_traces
    timeout: 5s
    retry_on_failure:
      enabled: true
      initial_interval: 5s
      max_interval: 30s
      max_elapsed_time: 300s

processors:
  batch:
    timeout: 5s
    send_batch_size: 100000

service:
  pipelines:
    traces:
      receivers: [otlp]
      processors: [spanmetrics, batch]
      exporters: [logging, clickhouse]

Key settings include the endpoint (TCP port 9000) and optional TLS parameters for secure connections.

Schema Design

The main table otel_traces stores each span with columns such as Timestamp, TraceId, SpanId, ParentSpanId, SpanName, ServiceName, ResourceAttributes (Map), SpanAttributes (Map), Duration, and event arrays. Example DDL:

CREATE TABLE otel_traces (
    `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    `TraceId` String CODEC(ZSTD(1)),
    `SpanId` String CODEC(ZSTD(1)),
    `ParentSpanId` String CODEC(ZSTD(1)),
    `TraceState` String CODEC(ZSTD(1)),
    `SpanName` LowCardinality(String) CODEC(ZSTD(1)),
    `SpanKind` LowCardinality(String) CODEC(ZSTD(1)),
    `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
    `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `SpanAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `Duration` Int64 CODEC(ZSTD(1)),
    `StatusCode` LowCardinality(String) CODEC(ZSTD(1)),
    `StatusMessage` String CODEC(ZSTD(1)),
    `Events.Timestamp` Array(DateTime64(9)) CODEC(ZSTD(1)),
    `Events.Name` Array(LowCardinality(String)) CODEC(ZSTD(1)),
    `Events.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
    `Links.TraceId` Array(String) CODEC(ZSTD(1)),
    `Links.SpanId` Array(String) CODEC(ZSTD(1)),
    `Links.TraceState` Array(String) CODEC(ZSTD(1)),
    `Links.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
    INDEX idx_trace_id TraceId TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_res_attr_key mapKeys(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_res_attr_value mapValues(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_span_attr_key mapKeys(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_span_attr_value mapValues(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_duration Duration TYPE minmax GRANULARITY 1
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toUnixTimestamp(Timestamp), TraceId)
TTL toDateTime(Timestamp) + toIntervalDay(3)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

The ORDER BY clause optimizes queries that filter first by ServiceName. The article discusses trade‑offs of ORDER BY order, PARTITION BY (daily partitions aid recent‑data queries but may affect long‑range scans), and the cost of using Map columns versus materialized scalar columns.

Materialized View for Trace‑ID Time Range

A materialized view otel_traces_trace_id_ts aggregates min/max timestamps per TraceId to accelerate time‑range lookups:

CREATE MATERIALIZED VIEW otel_traces_trace_id_ts_mv TO otel_traces_trace_id_ts (
    `TraceId` String,
    `Start` DateTime64(9),
    `End` DateTime64(9)
) AS
SELECT
    TraceId,
    min(Timestamp) AS Start,
    max(Timestamp) AS End
FROM otel_traces
WHERE TraceId != ''
GROUP BY TraceId;

Tests on ~2 billion spans (≈125 GB) show modest speed gains (≈0.1 s vs 0.2 s) because the primary ORDER BY already includes Timestamp. The view is useful for targeted trace inspection.

Querying Trace Data

Two query patterns are compared:

Direct query on otel_traces filtering by TraceId (≈0.197 s).

Using the materialized view to fetch the time window first, then querying otel_traces with the narrowed Timestamp range (≈0.110 s).

The article also demonstrates aggregations over Map keys, e.g., counting spans per hour per language, and extracting distinct Map keys with groupArrayDistinctArray(mapKeys(ResourceAttributes)).

Visualization with Grafana

Grafana’s ClickHouse datasource plugin (recently upgraded) supports a trace panel. A query that produces columns required by the panel is provided, converting Duration to milliseconds and mapping SpanAttributes and ResourceAttributes into tags and serviceTags arrays.

WITH 'ec4cff3e68be6b24f35b4eef7e1659cb' AS trace_id,
     (SELECT min(Start) FROM otel_traces_trace_id_ts WHERE TraceId = trace_id) AS start,
     (SELECT max(End)+1 FROM otel_traces_trace_id_ts WHERE TraceId = trace_id) AS end
SELECT
    TraceId AS traceID,
    SpanId AS spanID,
    SpanName AS operationName,
    ParentSpanId AS parentSpanID,
    ServiceName AS serviceName,
    Duration/1000000 AS duration,
    Timestamp AS startTime,
    arrayMap(k -> map('key', k, 'value', SpanAttributes[k]), mapKeys(SpanAttributes)) AS tags,
    arrayMap(k -> map('key', k, 'value', ResourceAttributes[k]), mapKeys(ResourceAttributes)) AS serviceTags
FROM otel_traces
WHERE TraceId = trace_id AND Timestamp >= start AND Timestamp <= end
ORDER BY startTime ASC;

Grafana variables and data‑link features enable interactive dashboards showing request volume, 99th‑percentile latency, error rates, and trace lists. The article includes screenshots of the dashboard and trace panel.

Parameterized Views

To simplify repeated trace queries, a parameterized view trace_view is created that accepts trace_id as a parameter:

CREATE VIEW trace_view AS
SELECT
    TraceId AS traceID,
    SpanId AS spanID,
    SpanName AS operationName,
    ParentSpanId AS parentSpanID,
    ServiceName AS serviceName,
    Duration/1000000 AS duration,
    Timestamp AS startTime,
    arrayMap(k -> map('key', k, 'value', SpanAttributes[k]), mapKeys(SpanAttributes)) AS tags,
    arrayMap(k -> map('key', k, 'value', ResourceAttributes[k]), mapKeys(ResourceAttributes)) AS serviceTags
FROM otel_traces
WHERE TraceId = {trace_id:String};

-- Example usage
SELECT * FROM trace_view(trace_id = '1f12a198ac3dd502d5201ccccad52967');

When used in Grafana’s Explore view with Format = Trace, the result renders a full trace diagram.

Compression

ClickHouse achieves roughly 9‑10× compression on the demo dataset. Sample query shows compressed vs. uncompressed sizes per column, highlighting that ResourceAttributes and SpanAttributes dominate storage.

Future Work

Schema refinements : Re‑evaluate Bloom filters (they add ~1 % storage) and consider materializing frequently queried Map keys.

Memory usage : The collector’s batch processor can consume significant RAM under high load; tuning timeout and send_batch_size or enabling async inserts may mitigate this.

End‑to‑end delivery guarantees : Currently, the collector does not guarantee persistence on crash. Users can employ Kafka or the collector’s persistent queue for stronger guarantees.

Scalability : Deploy multiple collectors behind a load balancer and use the load‑balancing exporter to keep spans of the same trace on the same collector.

Sampling : Implement tail‑sampling to reduce stored volume while preserving useful traces.

Conclusion

The guide demonstrates end‑to‑end collection, storage, and visualization of distributed tracing data with OpenTelemetry, ClickHouse, and Grafana. It provides a reproducible demo, discusses schema choices, query optimizations, and outlines next steps for scaling, memory management, and data lifecycle.