Final Lab: OpenTelemetry Collector to ClickHouse and SQL analyses with correction

What you will learn in this TP

• Configure the ClickHouse exporter
• Write SQL analyses
• Speed up with a materialised view

Introduction #

What you will learn in this section

• Configure the ClickHouse exporter
• Write SQL analyses
• Speed up with a materialised view

Context. Your data team wants to combine telemetry with its own ClickHouse tables. You set up direct OpenTelemetry ingestion and demonstrate the value of SQL.

Prerequisites: Docker Compose  ·  Estimated time: 50 min

Telemetry in ClickHouse, queryable with SQL.

Setup #

Guided steps, with their solutions.

1. 1. Write the Compose file

   Why? ClickHouse and a contrib Collector are all you need.

   Create the Compose file and the Collector configuration.
   See the correction

   services:
     clickhouse:
       image: clickhouse/clickhouse-server:latest
       environment: { CLICKHOUSE_USER: otel, CLICKHOUSE_PASSWORD: otel, CLICKHOUSE_DB: otel }
       ports: ["8123:8123"]
     otelcol:
       image: otel/opentelemetry-collector-contrib:latest
       command: [--config=/etc/otelcol/config.yaml]
       volumes: ["./otelcol.yaml:/etc/otelcol/config.yaml"]
       depends_on: [clickhouse]

   # otelcol.yaml
   receivers:
     otlp: { protocols: { grpc: { endpoint: 0.0.0.0:4317 } } }
   processors:
     batch: { send_batch_size: 5000, timeout: 5s }
   exporters:
     clickhouse:
       endpoint: tcp://clickhouse:9000?dial_timeout=10s
       database: otel
       username: otel
       password: otel
       ttl: 72h
       create_schema: true
   service:
     pipelines:
       traces: { receivers: [otlp], processors: [batch], exporters: [clickhouse] }
       logs:   { receivers: [otlp], processors: [batch], exporters: [clickhouse] }

   docker compose up -d

2. 2. Generate data

   Why? Traces from two services and a few errors give you something to analyse.

   Send traces for two services, some of them with errors.
   See the correction

   NET=$(basename $PWD)_default
   for s in panier paiement; do
     docker run --rm --network $NET ghcr.io/open-telemetry/opentelemetry-collector-contrib/telemetrygen:latest traces \
       --otlp-endpoint otelcol:4317 --otlp-insecure --duration 20s --rate 50 --service $s
   done
   docker run --rm --network $NET ghcr.io/open-telemetry/opentelemetry-collector-contrib/telemetrygen:latest traces \
     --otlp-endpoint otelcol:4317 --otlp-insecure --duration 10s --rate 10 --service paiement --status-code Error

3. 3. Explore the schema

   Why? Know the columns before writing queries.

   List the tables created and describe otel_traces.
   See the correction

   ch() { curl -s 'http://localhost:8123/?user=otel&password=otel' --data-binary "$1"; }
   ch "SHOW TABLES FROM otel"
   ch "DESCRIBE otel.otel_traces" | cut -f1,2 | head -20

4. 4. Analyse in SQL

   Why? Analytical queries answer operational questions in a single line.

   Calculate, per service, the number of spans, the error rate and the p95 latency.
   See the correction

   ch "SELECT ServiceName, count() AS spans,
          round(100 * countIf(StatusCode = 'Error') / count(), 2) AS pct_erreurs,
          round(quantile(0.95)(Duration) / 1e6, 2) AS p95_ms
   FROM otel.otel_traces GROUP BY ServiceName FORMAT PrettyCompact" 

5. 5. Materialised view and compression

   Why? A pre-aggregated view makes dashboards instant; compression justifies choosing ClickHouse.

   Create a per-minute materialised view, send traces again, query the view, then measure compression.
   See the correction

   ch "CREATE MATERIALIZED VIEW otel.red_minute ENGINE = SummingMergeTree ORDER BY (ServiceName, minute) AS
   SELECT ServiceName, toStartOfMinute(Timestamp) AS minute, count() AS spans, countIf(StatusCode = 'Error') AS erreurs
   FROM otel.otel_traces GROUP BY ServiceName, minute"
   # ... send traces again with telemetrygen, then:
   ch "SELECT ServiceName, minute, sum(spans), sum(erreurs) FROM otel.red_minute GROUP BY ServiceName, minute ORDER BY minute FORMAT PrettyCompact"
   ch "SELECT table, formatReadableSize(sum(data_compressed_bytes)) AS compresse,
       round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 1) AS ratio
   FROM system.parts WHERE database = 'otel' AND active GROUP BY table FORMAT PrettyCompact" 

   The view only contains data inserted after it was created; the compression ratio for traces is typically between 5 and 15.

Difficulty level : ●●●●○ (4/5)