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.
My progress
Sign in to track your progress and take notes. Sign in
💬 Questions about this lesson
No questions yet: be the first to ask for help or share your configuration.
Recommended Articles
SigNoz, ClickStack and ClickHouse best practices for observability
Best practices for observability on ClickHouse with SigNoz or ClickStack: OpenTelemetry instrumen...
Sending telemetry to ClickStack: OpenTelemetry SDKs, collectors, logs, Kubernetes and browser sessions
Feeding ClickStack: ingestion key, OpenTelemetry SDK variables, forwarding through an existing co...
Observability on ClickHouse: why a columnar database changes the game
Why more and more observability platforms store logs, traces and metrics in ClickHouse: columnar ...
Operating an observability platform on ClickHouse and choosing a tool
Operate ClickHouse for telemetry (replication, disks, merges, object storage, monitoring) and com...
Operating ClickHouse for ClickStack: sizing, replication, monitoring, security and backup
Running ClickHouse in production for observability: estimating volume and disk, replicating with ...
ClickStack: architecture, components and offerings (ClickHouse, HyperDX, OpenTelemetry)
ClickStack, ClickHouse's official observability stack: role of ClickHouse, the UI (HyperDX), the ...
Exploring with the ClickStack UI: sources, search, traces, sessions and SQL
Using the ClickStack UI (HyperDX): data sources, property and full-text search syntax, SQL mode, ...
Using SigNoz: APM, traces, logs, dashboards and alerts
Use SigNoz day to day: APM view of services, traces and logs explorers, query builder, ClickHouse...
Alerts and dashboards in ClickStack
Building charts and dashboards in ClickStack, creating alerts on a search or a chart, sending the...