1. The tables #
What the collector creates.
Overview #
Available in: ✓ Open source ✓ Managed ClickStack
| Table | Content | Key columns |
|---|---|---|
otel_logs | logs | Timestamp, ServiceName, SeverityText, Body, TraceId, LogAttributes, ResourceAttributes |
otel_traces | spans | TraceId, SpanId, ParentSpanId, SpanName, SpanKind, Duration (ns), StatusCode, SpanAttributes, Events.*, Links.* |
otel_metrics_gauge, _sum, _histogram, _exponential_histogram, _summary | metrics, one table per type | MetricName, Attributes, TimeUnix, Value (or buckets) |
hyperdx_sessions | browser sessions | replay 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
TraceIdand a token index onBodyavoid 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 definitionLogAttributes['clé']: read an attributeALTER TABLE … MODIFY TTL …: change retentionCREATE MATERIALIZED VIEW … TO …: pre-aggregate on insertSELECT … 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.
