Storing and querying telemetry directly in ClickHouse

1. The ClickHouse exporter #

OpenTelemetry to SQL.

Configuration #

# Collector OpenTelemetry (distribution contrib)
exporters:
  clickhouse:
    endpoint: tcp://clickhouse:9000?dial_timeout=10s
    database: otel
    username: otel
    password: ${env:CLICKHOUSE_PASSWORD}
    ttl: 168h                  # rétention 7 jours
    create_schema: true        # crée les tables au démarrage
    logs_table_name: otel_logs
    traces_table_name: otel_traces
service:
  pipelines:
    logs:   { receivers: [otlp], processors: [batch], exporters: [clickhouse] }
    traces: { receivers: [otlp], processors: [batch], exporters: [clickhouse] }

The exporter creates otel_logs, otel_traces and otel_metrics_* tables (per metric type) in MergeTree format, sorted for queries by service and by time.

2. Querying in SQL #

The main columns.

Queries #

-- Latence p95 par opération sur la dernière heure
SELECT ServiceName, SpanName,
       quantile(0.95)(Duration) / 1e6 AS p95_ms, count() AS spans
FROM otel.otel_traces
WHERE Timestamp > now() - INTERVAL 1 HOUR AND SpanKind = 'Server'
GROUP BY ServiceName, SpanName
ORDER BY p95_ms DESC LIMIT 10;

-- Logs d'erreur d'une trace lente, en une jointure
SELECT l.Timestamp, l.ServiceName, l.Body
FROM otel.otel_logs AS l
WHERE l.TraceId IN (
  SELECT TraceId FROM otel.otel_traces
  WHERE Timestamp > now() - INTERVAL 1 HOUR AND Duration > 2e9 LIMIT 100)
  AND l.SeverityText = 'ERROR'
ORDER BY l.Timestamp;

-- Attribut à haute cardinalité : top clients en erreur
SELECT SpanAttributes['client.id'] AS client, countIf(StatusCode = 'Error') AS erreurs
FROM otel.otel_traces WHERE Timestamp > now() - INTERVAL 1 DAY
GROUP BY client ORDER BY erreurs DESC LIMIT 10;

3. Retention and performance #

TTL and materialized views.

Settings #

-- Modifier la rétention d'une table existante
ALTER TABLE otel.otel_logs MODIFY TTL toDateTime(Timestamp) + INTERVAL 14 DAY;

-- Pré-agréger les métriques RED par minute
CREATE MATERIALIZED VIEW otel.red_par_minute
ENGINE = SummingMergeTree ORDER BY (ServiceName, SpanName, minute) AS
SELECT ServiceName, SpanName, toStartOfMinute(Timestamp) AS minute,
       count() AS requetes, countIf(StatusCode = 'Error') AS erreurs
FROM otel.otel_traces WHERE SpanKind = 'Server'
GROUP BY ServiceName, SpanName, minute;

-- Place occupée et taux de compression
SELECT table, formatReadableSize(sum(data_compressed_bytes)) AS compresse,
       round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 1) AS ratio
FROM system.parts WHERE database = 'otel' AND active GROUP BY table;

Good practice: With Grafana's ClickHouse plugin, these queries become panels, with links to traces: an interesting option for a team already on Grafana.