Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 35 ยท Projects and Practice

Project: IoT Metrics and Rollups

Model sensor time series in ClickHouse: compact schema with codecs, tiered rollups by minute and hour, anomaly and gap detection, retention, with SQL stand-ins to run.

Fleets of sensors, servers or vehicles report small readings every few seconds. Over time that becomes billions of rows, yet people mostly look at charts: averages per minute, the max per hour, which device went quiet. ClickHouse handles this with a compact schema, good compression and tiered rollups. In this project you design that stack, from raw readings down to long-lived hourly aggregates.

ClickHouse cannot run on this site; the DDL is read-only. The analysis queries have runnable portable versions on generated data. Related lessons: codecs, AggregatingMergeTree and TTL.

Requirements

  • 20,000 devices report temperature and battery every 10 seconds (about 2,000 rows/second).
  • Charts need per-device series for the last day at 10-second resolution, the last month at 1 minute and the last two years at 1 hour.
  • Raw data older than 7 days is deleted; minute data after 90 days.
  • Alerts: devices silent for more than 5 minutes, and temperature spikes.

Step 1: raw readings

SQL
CREATE TABLE iot.readings
(
    device_id  UInt32,
    ts         DateTime   CODEC(DoubleDelta, ZSTD(1)),
    temp       Float32    CODEC(Gorilla, ZSTD(1)),
    battery    UInt8      CODEC(T64, ZSTD(1)),
    fw         LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (device_id, ts)
TTL ts + INTERVAL 7 DAY DELETE
SETTINGS ttl_only_drop_parts = 1;

Notes:

  • ORDER BY (device_id, ts) makes a device's series contiguous, so a chart query for one device reads a narrow range. Timestamps within a device increase steadily, which is why DoubleDelta compresses them extremely well, and Gorilla suits slowly changing floats.
  • Daily partitions plus ttl_only_drop_parts = 1 mean old data is removed by dropping whole parts, which is nearly free.
  • Use the smallest types: UInt8 for a percentage, Float32 for sensor precision.

Step 2: minute and hour rollups

Chain two materialized views: raw to minute, minute to hour. Store states (or sums and counts) so averages remain exact.

SQL
CREATE TABLE iot.readings_1m
(
    device_id UInt32,
    minute    DateTime,
    temp_avg  AggregateFunction(avg, Float32),
    temp_max  SimpleAggregateFunction(max, Float32),
    temp_min  SimpleAggregateFunction(min, Float32),
    samples   SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (device_id, minute)
TTL minute + INTERVAL 90 DAY DELETE;

CREATE MATERIALIZED VIEW iot.readings_1m_mv TO iot.readings_1m AS
SELECT device_id, toStartOfMinute(ts) AS minute,
       avgState(temp) AS temp_avg, max(temp) AS temp_max, min(temp) AS temp_min,
       count() AS samples
FROM iot.readings
GROUP BY device_id, minute;

The hourly table is defined the same way with toStartOfHour and a view that reads from readings_1m using avgMergeState:

SQL
CREATE MATERIALIZED VIEW iot.readings_1h_mv TO iot.readings_1h AS
SELECT device_id, toStartOfHour(minute) AS hour,
       avgMergeState(temp_avg) AS temp_avg,
       max(temp_max) AS temp_max, min(temp_min) AS temp_min, sum(samples) AS samples
FROM iot.readings_1m
GROUP BY device_id, hour;

-MergeState merges states but keeps the result as a state, ready for another level. Charts choose the tier by time range, as the app code or a view decides.

flowchart LR
    R["readings (10 s, 7 days)"] --> M["readings_1m (90 days)"]
    M --> H["readings_1h (2+ years)"]
    H --> C["Charts"]
    M --> C
    R --> C

Each tier is smaller and lives longer; a chart reads the coarsest tier that gives enough detail.

Step 3: gaps and anomalies

ClickHouse offers ORDER BY ... WITH FILL to turn missing minutes into explicit zero or interpolated rows for charts:

SQL
SELECT minute, avgMerge(temp_avg) AS avg_temp
FROM iot.readings_1m
WHERE device_id = 17 AND minute >= now() - INTERVAL 1 HOUR
GROUP BY minute
ORDER BY minute WITH FILL STEP INTERVAL 1 MINUTE INTERPOLATE (avg_temp);

Silent devices are found with max(ts) per device and a comparison with now(). Spikes can be flagged by comparing each reading with a rolling average using window functions (avg(temp) OVER (PARTITION BY device_id ORDER BY ts ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING)).

Run the analysis on sample data

SQL Minute rollup and silent-device detection

Press Run (or Ctrl + Enter) to see the result here.

Only the last statement's result is shown; run each query alone to see both. Device 3 stopped at 1500, so it is marked SILENT.

SQL Spike detection against a rolling average

Press Run (or Ctrl + Enter) to see the result here.

The spike at sequence 20 is the only reading more than five degrees above its recent average.

Operations checklist

  • Insert batches from a gateway (one batch per second), not from each device.
  • Monitor part counts per partition; daily partitions over two years are fine only for the hourly tier, so use monthly partitions there.
  • Test a restore of the rollup tables: they are the only long-lived copy.
  • Replicate with two replicas per shard for availability; see replication.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is DoubleDelta a good codec for the ts column of regular sensor readings?

    Evenly spaced timestamps give delta values that repeat, and the second delta is near zero, which compresses very well.
  2. 2. What does avgMergeState do in the hourly materialized view?

    -MergeState combines states without finalizing them, so they can be stored again.
  3. 3. What is the benefit of ttl_only_drop_parts with daily partitions?

    When all rows in a part have expired the part can simply be removed.
  4. 4. Which clause fills in missing minutes for a chart?

    WITH FILL generates the missing rows of a sequence, optionally interpolating columns.

Summary

  • Sort by device then time, use DoubleDelta, Gorilla and small types, and drop expired daily partitions.
  • Chain materialized views into minute and hour tiers using states for exact averages.
  • Use WITH FILL, max(ts) and window functions for gaps, silence and spikes.
  • Retention differs per tier: seven days raw, 90 days minute, years hourly.

Next: Practice Questions and Interview Prep