ClickHouse Cheatsheet 03 — Materialized Views

ClickHouse materialized views.

Concept

MV is a trigger that runs on INSERT into source table. Result goes to destination table.

Basic MV

CREATE MATERIALIZED VIEW events_daily TO events_daily_target AS
SELECT
    toDate(ts) AS day,
    event,
    count() AS c
FROM events
GROUP BY day, event;

CREATE TABLE events_daily_target (
    day Date, event String, c UInt64
) ENGINE = SummingMergeTree() ORDER BY (day, event);

Each insert into events runs the SELECT on the INSERT batch and adds to events_daily_target.

Aggregating MV pattern

CREATE TABLE counts (
    day Date,
    event LowCardinality(String),
    count_state AggregateFunction(count)
) ENGINE = AggregatingMergeTree() ORDER BY (day, event);

CREATE MATERIALIZED VIEW counts_mv TO counts AS
SELECT toDate(ts) AS day, event, countState() AS count_state
FROM events GROUP BY day, event;

-- Query
SELECT day, event, countMerge(count_state)
FROM counts GROUP BY day, event;

Refreshable MVs (newer)

CREATE MATERIALIZED VIEW summary
REFRESH EVERY 1 HOUR
TO summary_target AS
SELECT ... FROM big_table GROUP BY ...;

Periodic full refresh instead of trigger.

Backfill

MVs only see new inserts. For historical:

INSERT INTO events_daily_target
SELECT toDate(ts), event, count()
FROM events
WHERE ts < now()
GROUP BY toDate(ts), event;

Run once, then MV handles new data.

Chained MVs

MV’s destination can be another source. Chain transformations.

Cascading

events → MV1 → table1 → MV2 → table2

Performance

MV is sync; large insert into source = large MV computation.
Use Buffer engine before source for batching.
Avoid heavy joins in MV.

When NOT

Real-time complex joins.
Frequently changing aggregation logic.
Data > can fit in destination.

Consider: external ETL (dbt, Airflow + INSERT SELECT).

DDL

DROP VIEW events_daily;
ATTACH MATERIALIZED VIEW ... ;     -- recover from metadata

Common mistakes

Forgetting backfill for historical.
Using JOIN in MV (expensive).
MV destination not aligned (wrong ORDER BY).
Mutating source ≠ propagated to MV.
Cross-database MVs (limited).

Concept#

Basic MV#

Aggregating MV pattern#

Refreshable MVs (newer)#

Backfill#

Chained MVs#

Cascading#

Performance#

When NOT#

DDL#

Common mistakes#

Read this next#