Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 35 ยท Aggregation and Materialized Views

Materialized Views

Understand ClickHouse materialized views as insert-time triggers: how TO tables work, what happens on backfill, common pitfalls and a runnable trigger stand-in.

In many databases a materialized view is a stored query result that you refresh now and then. In ClickHouse the idea is different and, once understood, very powerful: a materialized view is an insert trigger. Every time a block of rows is inserted into a source table, ClickHouse runs the view's SELECT on that block only and writes the result into a target table. The view stores no data of its own beyond what lands in that target.

This model lets you keep pre-aggregated, re-sorted or filtered copies of a firehose table, updated in real time at almost no read cost. This lesson explains the mechanics, the syntax, and the pitfalls. The ClickHouse snippets are read-only (the server cannot run here); a SQLite trigger stand-in lets you watch the same flow happen.

The data flow

flowchart LR
    A["INSERT into events"] --> B["events (source table)"]
    A --> C["Materialized view runs SELECT on the inserted block"]
    C --> D["events_per_minute (target table)"]
    D --> E["Dashboard queries"]

The view transforms only the new block, never the whole table.

The key sentence: the SELECT sees only the newly inserted rows. A GROUP BY inside the view therefore aggregates one block at a time, and the target table must be able to combine partial results from many blocks later. That is why targets are usually SummingMergeTree or AggregatingMergeTree (see the next lesson).

Creating a view with a TO table

The recommended form creates the target table yourself and points the view at it with TO:

SQL
CREATE TABLE events
(
    ts       DateTime,
    site     LowCardinality(String),
    user_id  UInt64,
    bytes    UInt32
)
ENGINE = MergeTree
ORDER BY (site, ts);

CREATE TABLE events_per_minute
(
    minute  DateTime,
    site    LowCardinality(String),
    hits    UInt64,
    bytes   UInt64
)
ENGINE = SummingMergeTree
ORDER BY (site, minute);

CREATE MATERIALIZED VIEW events_per_minute_mv
TO events_per_minute
AS
SELECT
    toStartOfMinute(ts) AS minute,
    site,
    count()             AS hits,
    sum(bytes)          AS bytes
FROM events
GROUP BY minute, site;

Explicit TO tables are preferred because you control the schema and engine, can query or alter the target directly, can detach and recreate the view without losing data, and can have several views feed one table. The older form, CREATE MATERIALIZED VIEW name ENGINE = ... AS SELECT, creates a hidden .inner table and is harder to maintain.

The column names and types the SELECT returns must match the target table's columns by name, not position. Missing target columns get defaults.

What the view does and does not do

Event Effect on the target
INSERT into the source the view runs on the new block and inserts into the target
UPDATE or DELETE on the source nothing; views fire only on inserts
DROP PARTITION on the source nothing; the target keeps its rows
CREATE MATERIALIZED VIEW on a table with data existing rows are not processed
TRUNCATE source target is untouched

That fourth row surprises beginners. Creating a view does not backfill. To fill history you insert manually:

SQL
INSERT INTO events_per_minute
SELECT toStartOfMinute(ts), site, count(), sum(bytes)
FROM events
WHERE ts < now() - INTERVAL 1 HOUR
GROUP BY 1, 2;

For a live system, pick a cut-off time: create the view first (it captures new inserts), then backfill rows older than the moment you created it, taking care not to count the same rows twice.

A view is a trigger: consequences

Because the view fires per inserted block, design with these facts in mind:

  1. Blocks are small. An insert of 100 rows produces a 100-row block, and the target receives a small partial aggregate. Background merges later combine them, so always query a summing target with sum() and GROUP BY, never assuming one row per key.
  2. Failures cancel the insert. If the view's SELECT throws (for example a type conversion error), the whole INSERT into the source fails, unless you set materialized_views_ignore_errors. Test views before attaching them to a hot table.
  3. Cost adds up. Every view adds work to every insert. Ten views on a table mean ten extra writes per insert. Keep the view's SELECT cheap.
  4. Views can chain. A view's target can be the source of another view, but each hop adds latency to the insert.
  5. Joins only see the left side as the trigger. In a join inside the view, only inserts into the left table fire it, and the right table is read in full each time. Prefer dictionaries for lookups.

Refreshable materialized views

Newer versions add REFRESH, which behaves more like the traditional kind: the query runs on a schedule over the whole source and replaces (or appends to) the target.

SQL
CREATE MATERIALIZED VIEW top_pages_mv
REFRESH EVERY 10 MINUTE
TO top_pages
AS
SELECT url, count() AS hits
FROM events
GROUP BY url
ORDER BY hits DESC
LIMIT 100;

Use refreshable views for expensive queries (joins, top-N) where a slightly stale result is fine. Use classic insert-triggered views for incremental aggregation.

Watch it happen: a trigger stand-in

SQLite triggers behave like ClickHouse materialized views for this purpose: an insert into the source feeds a target table. This is a stand-in, but it shows that only new rows are processed and that history is not backfilled.

SQL Stand-in: an insert trigger acting like a materialized view

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

The target shows a.com with only the 3rd row (hits 1) because the first two rows predate the trigger. Now compare with a manual backfill:

SQL Stand-in: backfilling the history by hand

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

In ClickHouse the target would hold partial rows per block and merge them in the background, instead of updating one row in place as this trigger does. That is the main difference, and the reason you always re-aggregate when reading.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When does a classic ClickHouse materialized view run?

    It is an insert trigger: the SELECT is applied to each newly inserted block and the result is written to the target table.
  2. 2. You create a materialized view on a table that already holds a billion rows. What happens to the old rows?

    Views see only data inserted after creation. Historical rows require an explicit INSERT ... SELECT into the target.
  3. 3. Why is the TO form of CREATE MATERIALIZED VIEW preferred?

    With TO you choose the engine and schema, can query or alter the target directly and recreate the view without losing data.
  4. 4. What happens if a DELETE runs on the source table?

    Materialized views fire only on inserts, so deletes, updates and dropped partitions do not propagate.

Summary

  • A ClickHouse materialized view is an insert trigger that writes transformed blocks into a target table.
  • Prefer CREATE MATERIALIZED VIEW ... TO target with an explicit target table.
  • Existing data is not backfilled, and updates or deletes on the source are not propagated.
  • Targets hold partial results, so aggregate again when reading.
  • Refreshable views run a scheduled query for expensive, slightly stale results.

Next: AggregatingMergeTree and SummingMergeTree