ClickHouse for Network Telemetry: Schema and Queries

Interface counters, gNMI samples and flow records are append-only, high-cardinality and queried by time window — a workload ClickHouse handles far better than a relational database tuned for transactions. The catch is schema design: get the primary key wrong and queries that should scan a few thousand rows scan a hundred million. This guide covers a workable table layout for telemetry, TTL-based retention, and the query patterns that keep dashboards responsive.

Table design for interface telemetry

CREATE TABLE telemetry.interfaces
(
    ts            DateTime CODEC(Delta, ZSTD(1)),
    device        LowCardinality(String),
    site          LowCardinality(String),
    if_name       LowCardinality(String),
    if_index      UInt32,
    in_octets     UInt64,
    out_octets    UInt64,
    in_errors     UInt32,
    out_errors    UInt32,
    in_discards   UInt32,
    oper_status   Enum8('up' = 1, 'down' = 2, 'testing' = 3),
    speed_bps     UInt64
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (device, if_name, ts)
TTL ts + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;

Guidance that matters more than the exact syntax:

• ORDER BY is the primary index. Put the most selective equality columns first (device, interface) and time last, matching how queries filter.
• LowCardinality on device/site/interface names reduces storage substantially at scale.
• Partition by day. Monthly partitions make deletes expensive and merges heavy; daily partitions make retention cheap.
• Delta plus ZSTD codes monotonic counters efficiently — most telemetry counters never decrease.

Ingesting samples

INSERT INTO telemetry.interfaces FORMAT JSONEachRow
{"ts":"2026-09-27 10:15:00","device":"core-sw1","site":"hq","if_name":"Ethernet1","if_index":1,
 "in_octets":90123456789,"out_octets":12345678901,"in_errors":0,"out_errors":12,
 "in_discards":0,"oper_status":"up","speed_bps":10000000000};

Batch aggressively — hundreds of thousands of rows per insert. ClickHouse is designed for bulk part writes, not per-sample inserts; a collector pushing one row at a time will spend its life merging.

Queries that matter operationally

-- Utilisation for a specific interface over the last hour (rate from counters)
SELECT
    ts,
    round((in_octets - lagInFrame(in_octets) OVER (ORDER BY ts)) * 8 /
          dateDiff('second', lagInFrame(ts) OVER (ORDER BY ts), ts) / speed_bps * 100, 2) AS in_util_pct
FROM telemetry.interfaces
WHERE device = 'core-sw1' AND if_name = 'Ethernet1'
  AND ts > now() - INTERVAL 1 HOUR
ORDER BY ts;

-- Interfaces with errors in the last 24h, busiest first
SELECT device, if_name, sum(in_errors + out_errors) AS errs, max(in_discards) AS max_discards
FROM telemetry.interfaces
WHERE ts > now() - INTERVAL 24 HOUR AND (in_errors + out_errors) > 0
GROUP BY device, if_name
ORDER BY errs DESC
LIMIT 50;

-- Status transitions (flap detection) per interface
SELECT device, if_name, ts, oper_status
FROM telemetry.interfaces
WHERE device = 'core-sw1' AND ts > now() - INTERVAL 6 HOUR
ORDER BY device, if_name, ts
LIMIT 200;

Retention and rollups

CREATE TABLE telemetry.interfaces_5m
ENGINE = SummingMergeTree
ORDER BY (device, if_name, bucket)
AS SELECT
    toStartOfFiveMinute(ts) AS bucket,
    device, if_name,
    max(in_octets) AS in_octets, max(out_octets) AS out_octets,
    sum(in_errors) AS in_errors, sum(out_errors) AS out_errors
FROM telemetry.interfaces
GROUP BY bucket, device, if_name;

Keep raw samples for 30–90 days and rollups for a year. Dashboards should read rollups for wide windows; raw tables stay for incident forensics. Move rollups with a materialised view rather than a nightly batch job so the summary is always current.

Operational checks

SELECT table, formatReadableSize(sum(bytes_on_disk)) AS size,
       sum(rows) AS rows, count() AS parts
FROM system.parts WHERE active AND database='telemetry'
GROUP BY table ORDER BY sum(bytes_on_disk) DESC;

SELECT count() AS too_many_parts FROM system.parts
WHERE active AND database='telemetry' AND table='interfaces';

Part count climbing into the thousands per partition means inserts are too small or too frequent. That single metric explains most ClickHouse performance complaints in telemetry pipelines.

Related reading: gNMI and Kafka streaming telemetry pipeline, Prometheus snmp_exporter for switches and routers, and Thanos long-term Prometheus storage.

原文链接：https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree