--- title: "Keep clickhouse integration opentelemetry queryable" excerpt: "Export OpenTelemetry to ClickHouse® with Collector pipelines and per-signal schemas. clickhouse integration opentelemetry that queries fast." authors: "Tinybird" categories: "AI Resources" createdOn: "2026-08-04 00:00:00" publishedOn: "2026-08-04 00:00:00" updatedOn: "2026-08-04 00:00:00" status: "published" --- OpenTelemetry standardizes **how apps emit** traces, metrics, and logs. It does not standardize **where they should live for analytical queries**. ClickHouse{% sup %}®{% /sup %} 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. | Signal | Typical questions | Table shape | | --- | --- | --- | | Traces | p95 latency by route, error rate by dependency | Span facts, wide attributes map/JSON | | Metrics | CPU, request rate, queue depth | Time + labels + value (often AggregatingMergeTree) | | Logs | search + filter + rare error clustering | Timestamp + severity + body + resource attrs | Shared resources (`service.name`, `deployment.environment`) can be columns on all three. Everything else stays signal-specific. ### **Recommended database layout** ```text 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: ```text 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** | Concern | Prefer | | --- | --- | | Creating spans/metrics in app code | SDK | | Sampling policy for many services | Collector (central) | | Redaction of auth headers | Collector attributes processor | | Fan-out to multiple backends | Collector exporters | | Language-specific nuance | SDK instrumentation packs | Apps should emit; collectors should decide what survives. ## **Span schema that survives real traffic** ```sql 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: ```sql 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** ```sql 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: ```sql 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** ```sql 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: ```sql 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: | Attribute | Budget instinct | | --- | --- | | `service.name` | dozens to hundreds | | `http.route` | templated routes only | | `user_id` as metric label | no | | custom `experiment_id` on every span | sample or column carefully | | raw URLs with query strings | no | Review weekly top-cardinality attributes: ```sql 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): | Data | Hot retention | Notes | | --- | --- | --- | | Raw spans | 3–14 days | debug debugging window | | 1-minute service rollups | 6–13 months | capacity planning + SLOs | | Raw logs | 3–30 days | or externalize search | | High-card metrics | as short as possible | prefer rollups | ```sql 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: ```sql 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](https://www.tinybird.co/blog/real-time-data-ingestion) paths for ops data, not only product events. It also pairs cleanly with [user-facing analytics](https://www.tinybird.co/blog/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.