RousselTMLEARNING

Observability

Telemetry in ClickHouse: schema, SQL, materialized views and retention

Telemetry in ClickHouse: otel_logs, otel_traces, otel_metrics_* and hyperdx_sessions tables, sort keys, partitions, codecs, Map columns, skip indexes, SQL recipes (error rates, traces, logs and traces joins), materialized views, TTL and tiered storage.

1. The tables #

What the collector creates.

Overview #

Available in: ✓ Open source ✓ Managed ClickStack

TableContentKey columns
otel_logslogsTimestamp, ServiceName, SeverityText, Body, TraceId, LogAttributes, ResourceAttributes
otel_tracesspansTraceId, SpanId, ParentSpanId, SpanName, SpanKind, Duration (ns), StatusCode, SpanAttributes, Events.*, Links.*
otel_metrics_gauge, _sum, _histogram, _exponential_histogram, _summarymetrics, one table per typeMetricName, Attributes, TimeUnix, Value (or buckets)
hyperdx_sessionsbrowser sessionsreplay events attached to a session

A table definition #

-- See the actual definition of a table created by the collector
SHOW CREATE TABLE otel_logs;
-- Typical excerpt (exporter versions change the details)
CREATE TABLE otel_logs (
    Timestamp          DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    TimestampTime      DateTime DEFAULT toDateTime(Timestamp),
    TraceId            String CODEC(ZSTD(1)),
    SpanId             String CODEC(ZSTD(1)),
    SeverityText       LowCardinality(String) CODEC(ZSTD(1)),
    ServiceName        LowCardinality(String) CODEC(ZSTD(1)),
    Body               String CODEC(ZSTD(1)),
    ResourceAttributes Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    LogAttributes      Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    INDEX idx_trace_id TraceId TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_body Body TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 8
) ENGINE = MergeTree
PARTITION BY toDate(TimestampTime)
ORDER BY (ServiceName, TimestampTime, Timestamp)
TTL TimestampTime + INTERVAL 30 DAY;
  • ORDER BY (ServiceName, time): filtering by service and period reads very little data. Queries filtering by neither service nor time are the most expensive.
  • PARTITION BY day: retention drops whole partitions, very quickly.
  • Codecs (Delta, ZSTD) and LowCardinality: the compression that makes telemetry affordable.
  • Skip indexes: a Bloom filter on TraceId and a token index on Body avoid reading blocks that do not contain the value searched.

2. Querying in SQL #

Reusable recipes.

Reading attributes #

Attributes live in Map columns: LogAttributes['http.route'], SpanAttributes['db.system'], ResourceAttributes['k8s.pod.name']. A missing key returns an empty string; convert numbers with toUInt16OrZero() or toFloat64OrNull().

Recipes #

-- 1. Error rate per service and per minute (logs)
SELECT toStartOfMinute(Timestamp) AS minute, ServiceName,
       countIf(SeverityText ILIKE 'err%') / count() AS error_rate
FROM otel_logs WHERE Timestamp > now() - INTERVAL 1 HOUR
GROUP BY minute, ServiceName ORDER BY minute;

-- 2. Rebuild a trace in order
SELECT SpanName, ServiceName, Duration / 1e6 AS ms, StatusCode, ParentSpanId
FROM otel_traces WHERE TraceId = '4bf92f3577b34da6a3ce929d0e0e4736' ORDER BY Timestamp;

-- 3. Error logs attached to slow traces (logs and traces join)
SELECT l.Timestamp, l.ServiceName, l.Body
FROM otel_logs AS l
INNER JOIN (SELECT TraceId FROM otel_traces
            WHERE Duration > 2e9 AND Timestamp > now() - INTERVAL 1 HOUR) AS slow USING (TraceId)
WHERE l.SeverityText ILIKE 'err%' AND l.Timestamp > now() - INTERVAL 1 HOUR LIMIT 50;

-- 4. Top 10 error messages, grouping variants
SELECT replaceRegexpAll(Body, '[0-9a-f]{8,}|\\d+', '?') AS pattern, count() AS n
FROM otel_logs WHERE SeverityText ILIKE 'err%' AND Timestamp > now() - INTERVAL 1 DAY
GROUP BY pattern ORDER BY n DESC LIMIT 10;
Best practice : Always add a time filter (Timestamp > now() - INTERVAL …) and, if possible, a service filter: they are the sort key columns.

3. Speeding up and retaining #

Materialized views, TTL, cold storage.

Materialized views #

-- Pre-aggregate RED metrics per minute: dashboards read a small table
CREATE TABLE red_1m (
    minute DateTime, ServiceName LowCardinality(String), route String,
    calls UInt64, errors UInt64, duration AggregateFunction(quantiles(0.5, 0.95, 0.99), UInt64)
) ENGINE = AggregatingMergeTree ORDER BY (ServiceName, route, minute)
TTL minute + INTERVAL 180 DAY;

CREATE MATERIALIZED VIEW red_1m_mv TO red_1m AS
SELECT toStartOfMinute(Timestamp) AS minute, ServiceName, SpanAttributes['http.route'] AS route,
       count() AS calls, countIf(StatusCode = 'Error') AS errors, quantilesState(0.5, 0.95, 0.99)(Duration) AS duration
FROM otel_traces WHERE SpanKind = 'Server'
GROUP BY minute, ServiceName, route;

-- Reading
SELECT minute, ServiceName, sum(calls) AS calls, quantilesMerge(0.5, 0.95, 0.99)(duration) AS q
FROM red_1m WHERE minute > now() - INTERVAL 1 DAY GROUP BY minute, ServiceName ORDER BY minute;

The view computes the aggregate on each insert: long-term dashboards read a few thousand rows instead of billions of spans, and the aggregate can be kept far longer than raw data.

Retention and tiered storage #

-- Different retention per table
ALTER TABLE otel_logs   MODIFY TTL TimestampTime + INTERVAL 14 DAY;
ALTER TABLE otel_traces MODIFY TTL toDateTime(Timestamp) + INTERVAL 7 DAY;

-- Move data older than 3 days to a cold volume (object storage), delete after 90 days
ALTER TABLE otel_logs MODIFY TTL
    TimestampTime + INTERVAL 3 DAY TO VOLUME 'cold',
    TimestampTime + INTERVAL 90 DAY DELETE;  -- requires a storage_policy with a “cold” volume

-- Space used and compression ratio per table
SELECT table, formatReadableSize(sum(data_compressed_bytes)) AS compressed,
       round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 1) AS ratio
FROM system.parts WHERE active AND database = currentDatabase() GROUP BY table ORDER BY sum(data_compressed_bytes) DESC;
Watch out : Changing a TTL triggers a background rewrite of existing data: do it outside peak hours on large volumes.

Commands to know #

Commands to know
  • SHOW CREATE TABLE otel_logs : actual table definition
  • LogAttributes['clé'] : read an attribute
  • ALTER TABLE … MODIFY TTL … : change retention
  • CREATE MATERIALIZED VIEW … TO … : pre-aggregate on insert
  • SELECT … FROM system.parts WHERE active : table size and compression

Sources #

Checked in November 2026: ClickStack evolves fast (Managed ClickStack is in beta), check the ClickHouse documentation before relying on a detail.

Related articles

The observability trilemma: cost, completeness and performance, and how solutions resolve it

Understanding the observability trilemma (cost, data completeness, query performance), its three trade-offs, …

Installing and deploying ClickStack: all-in-one, Docker Compose, Helm and Managed ClickStack

Deploying ClickStack to fit the need: all-in-one container with persistence, Docker Compose and its variables…

Sending telemetry to ClickStack: OpenTelemetry SDKs, collectors, logs, Kubernetes and browser sessions

Feeding ClickStack: ingestion key, OpenTelemetry SDK variables, forwarding through an existing collector, fil…

Keep learning

Support us

♥ SUPPORT US

Support RousselTM Learning

Courses, articles, labs and certificates stay free with no intrusive ads. A voluntary contribution, of any amount you choose, pays for hosting, lab environments and new content.

Contribute with PayPal Any amount · card or PayPal accountLearn more
QR code PayPal
Or scan this QR code with your phone

Thank you, every contribution counts!