Keep clickhouse integration opentelemetry queryable

OpenTelemetry standardizes how apps emit traces, metrics, and logs. It does not standardize where they should live for analytical queries. ClickHouse^® is a common answer when you outgrow "grep the collector" and need p95-by-service over weeks of spans.

A clickhouse integration opentelemetry setup is mostly an OpenTelemetry Collector design problem: receivers, processors, exporters, and a schema per signal. Get those right and ClickHouse® feels unfairly fast. Get them wrong and you have an expensive archive of untemplated routes and empty service.name values.

What success looks like

You are done when:

1. Each signal (traces, metrics, logs) has a clear table contract
2. Collectors batch enough to create healthy inserts
3. Sampling/filter rules are documented and tested
4. Cardinality budgets exist for promoted attributes
5. Dashboards/APIs query sort keys, not JSONExtract on every column
6. Retention differs for raw spans vs rollups

If your only goal is "OTLP somewhere," you will regret the schema in a month.

One collector pipeline per signal (on purpose)

Do not smash traces, metrics, and logs into one wide table "to keep it simple." Query patterns differ.

Shared resources (service.name, deployment.environment) can be columns on all three. Everything else stays signal-specific.

Recommended database layout

otel.spans
otel.spans_service_1m
otel.metrics_samples   (or rollup tables only)
otel.logs

Separate databases per environment (otel_prod, otel_staging) beat a environment column alone when access control matters.

Collector skeleton that lands in ClickHouse®

Conceptual Collector graph:

OTLP receiver (gRPC/HTTP)
  → processors (memory_limiter, resource, attributes, filter/tail_sampling, batch)
  → exporter(s) to ClickHouse® / Tinybird

Example processor order that ages well:

1. memory_limiter (fail closed under burst)
2. resource / attributes (normalize service.name, redact secrets)
3. filter / tail_sampling (drop noise, keep errors/slow traces)
4. batch (shape inserts for ClickHouse®)

Batch processor settings matter as much as DDL. Tiny flush intervals create insert storms. Start with a batch size/timeout that yields multi-thousand row inserts under load, then tighten for freshness.

Exporters you will see in the wild:

1. ClickHouse® exporter (community / vendor builds) writing OTLP-mapped tables
2. HTTP exporter posting JSON/NDJSON to ClickHouse® HTTP INSERT
3. Tinybird as the managed ClickHouse® sink (Events API or OTEL-oriented ingest paths)

Pin Collector and exporter versions. Config knobs move between releases. Treat collector config like application code: PR review, staging soak, then prod.

SDK vs Collector responsibilities

Apps should emit; collectors should decide what survives.

Span schema that survives real traffic

CREATE TABLE otel.spans (
    timestamp          DateTime64(9),
    trace_id           String,
    span_id            String,
    parent_span_id     String,
    service_name       LowCardinality(String),
    span_name          LowCardinality(String),
    span_kind          LowCardinality(String),
    duration_ns        UInt64,
    status_code        LowCardinality(String),
    http_method        LowCardinality(String),
    http_route         LowCardinality(String),
    http_status_code   UInt16,
    resource_attrs     String,
    span_attrs         String
)
ENGINE = MergeTree
PARTITION BY toDate(timestamp)
ORDER BY (service_name, http_route, timestamp, trace_id);

Why these choices:

• ORDER BY leads with service + route: that matches the dashboards people actually open
• Hot HTTP fields are columns; the long tail stays in span_attrs
• Nanosecond timestamps keep ordering honest inside a trace
• Daily partitions make TTL and deletes straightforward

Materialized view for RED-ish service metrics:

CREATE MATERIALIZED VIEW otel.spans_service_1m
ENGINE = SummingMergeTree
PARTITION BY toDate(minute)
ORDER BY (service_name, minute)
AS SELECT
    toStartOfMinute(timestamp) AS minute,
    service_name,
    count() AS spans,
    countIf(status_code = 'ERROR') AS errors,
    sum(duration_ns) AS duration_ns_sum
FROM otel.spans
GROUP BY minute, service_name;

Trace-level debugging query

SELECT
    timestamp,
    service_name,
    span_name,
    duration_ns,
    status_code,
    http_route
FROM otel.spans
WHERE trace_id = '00112233445566778899aabbccddeeff'
ORDER BY timestamp;

Put trace_id later in ORDER BY (as above) and accept that point lookups by trace scan more than a secondary index would. If trace lookup is your primary UX, add a bloom_filter / token index on trace_id for your ClickHouse® version, or store a narrower trace_id_lookup table.

Metrics: prefer aggregation engines

Raw OTLP gauge points at full cardinality will hurt.

Patterns that work:

• SummingMergeTree / AggregatingMergeTree for counter/histogram rollups
• Keep resource labels low-cardinality (service.name, region)
• Drop or hash high-cardinality labels (user_id on metrics is how Cloud bills rise)
• Prefer recording rules / Collector transforms for histograms before land when possible

Example counter rollup table:

CREATE TABLE otel.http_requests_1m (
    minute         DateTime,
    service_name   LowCardinality(String),
    http_route     LowCardinality(String),
    http_status    UInt16,
    requests       UInt64
)
ENGINE = SummingMergeTree
PARTITION BY toDate(minute)
ORDER BY (service_name, http_route, http_status, minute);

If you need exemplars linking metrics to traces, store trace_id on a sampled subset, not on every point.

Logs: search columns vs body

CREATE TABLE otel.logs (
    timestamp      DateTime64(9),
    service_name   LowCardinality(String),
    severity_text  LowCardinality(String),
    body           String,
    trace_id       String,
    resource_attrs String
)
ENGINE = MergeTree
PARTITION BY toDate(timestamp)
ORDER BY (service_name, severity_text, timestamp);

For text search at scale, decide early:

• ClickHouse® hasToken / ngram / inverted indexes for "find this error string"
• Or ship logs to a purpose-built search system and keep ClickHouse® for metrics/traces only

Mixed signals do not require mixed storage dogma. They require honest query goals. Many teams keep traces + metrics in ClickHouse® and treat logs as a separate product.

Correlating logs and traces

When SDKs inject trace_id into logs, this becomes easy:

SELECT timestamp, severity_text, body
FROM otel.logs
WHERE trace_id = '00112233445566778899aabbccddeeff'
ORDER BY timestamp;

If trace_id is missing on logs, fix instrumentation. Do not try to join on timestamps alone in production.

Processors worth enabling before the exporter

• memory_limiter: protect the collector under burst
• batch: insert shape for ClickHouse®
• filter / tail_sampling: drop health-check spans; sample successful GETs; keep errors and slow traces
• attributes: promote http.route, redact secrets, set deployment.environment
• resource: ensure service.name always exists (empty service names poison every dashboard)
• transform (where available): normalize route templates, drop banned attributes

Sampling belongs in the collector (or SDK), not as "we will filter in SQL later" on a 10× larger table.

Tail sampling starter policy

Keep:

• Spans with status ERROR
• Spans slower than a latency threshold
• A small percentage of successful traffic for baseline

Drop:

• /healthz, /ready, kube probes
• Static asset routes if they are instrumented by accident

Cardinality budgets

OpenTelemetry makes it easy to emit http.route with raw IDs embedded. That becomes millions of unique sort-key prefixes.

Budget examples:

Review weekly top-cardinality attributes:

SELECT
    http_route,
    count() AS spans,
    uniq(trace_id) AS traces
FROM otel.spans
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY http_route
ORDER BY spans DESC
LIMIT 50;

If you see /users/458921 instead of /users/{id}, fix the instrumentation or Collector transform before you buy more disk.

Retention and TTL

Suggested starting point (adjust to compliance):

ALTER TABLE otel.spans
MODIFY TTL toDate(timestamp) + INTERVAL 14 DAY;

Most "we need 13 months of raw spans" requests are unmet dashboard needs. Rollups usually satisfy them.

Multi-tenancy and security

• Separate write credentials per collector pool
• Do not let app pods talk to ClickHouse® directly if a Collector tier exists
• Redact Authorization, cookies, and PII attributes in Collector
• Restrict read access to raw spans if they contain end-user data
• Use TLS to ClickHouse® Cloud / Tinybird

Collectors are part of your security boundary. Treat their config as sensitive.

Tinybird for OTEL analytics APIs

If the goal is service health productized for internal tools (or customer-facing status views), landing OTEL in Tinybird gives you managed ClickHouse® plus HTTP:

NODE service_error_rate
SQL >
    SELECT
        service_name,
        count() AS spans,
        countIf(status_code = 'ERROR') AS errors,
        errors / spans AS error_rate,
        quantile(0.95)(duration_ns) AS p95_ns
    FROM spans
    WHERE timestamp >= {{ DateTime(start_time, '2026-08-01 00:00:00') }}
    GROUP BY service_name
    ORDER BY error_rate DESC

TYPE endpoint

Collector exports stay the same idea. The sink becomes Tinybird instead of a cluster you page on. Fits teams building real-time data ingestion paths for ops data, not only product events. It also pairs cleanly with user-facing analytics when you expose sanitized status views.

Local verification loop

1. Emit a synthetic trace with a fixed trace_id through your app
2. Confirm one row per span in ClickHouse® / Tinybird within the batch window
3. Break a dependency; confirm error spans and status codes
4. Load-test; watch collector queue and ClickHouse® insert latency together
5. Confirm sampling ratios match config (people forget and "lose" spans)
6. Verify health-check spans are absent
7. Confirm service.name is non-empty for ≥ 99.9% of rows

Load test tip

Generate realistic route cardinality. A load test with five routes will not reveal the cardinality fire waiting in production.

Operations and alerting

Alert on:

• Collector restart loops / OOM
• Exporter failure rate
• Insert errors into ClickHouse®
• Sudden drop in spans per service (instrumentation broke)
• Cardinality explosions on promoted columns
• Disk / TTL backlog

Do not alert only on "pipeline idle." Silent instrumentation loss looks like a healthy empty system.

Frequently Asked Questions (FAQ)

Should I use the ClickHouse® exporter or HTTP INSERT?

Use a maintained ClickHouse® exporter if it maps OTLP cleanly for your Collector version. HTTP INSERT is fine when you control the JSON shape and batching. Stability beats purity.

Can I store all three signals in one database?

Yes. Prefer separate tables (or databases) per signal, shared conventions for service_name and time.

How long should I retain spans?

Keep raw spans hot for days to weeks; roll up to 1-minute service metrics for months. Most "we need 13 months of raw spans" requests are unmet dashboard needs in disguise.

Is OpenTelemetry a substitute for a metrics SaaS?

It can be, if you invest in Collector ops, schema, and sampling. ClickHouse® makes the query layer affordable. It does not remove the need for good telemetry design.

Why are my p95s wrong after sampling?

Because you sampled away the distribution. Prefer tail sampling that keeps slow traces, or compute latency SLIs from metrics histograms designed for that purpose.

Can the app export OTLP straight to ClickHouse®?

Possible, usually unwise. Collectors give you batching, redaction, sampling, and fan-out in one place. Direct app→database couples every service to sink credentials and insert semantics.