Create materialized views in ClickHouse®

Use materialized views to transform data automatically as it arrives. When a new block of data is inserted into a source table, the query defined by the materialized view runs on that block and writes the result to a destination table. This is the standard ClickHouse pattern for pre-aggregating data, maintaining rollups, or persisting transformed copies of raw events.

The destination of the data (for example, a MergeTree family table) is defined by the TO clause. This process is illustrated in the following diagram:

    flowchart LR
  a[Inserts into<br />source table]
  b{{Source table}}
  c[Materialized view]
  d{{MergeTree<br />family table}}
  a --> b --> c --> d

Create a materialized view

This example builds a daily rollup of the default.trips table loaded in the quick start. Any MergeTree table with a date column works the same way.

Create the destination table that stores the transformed data:

CREATE TABLE default.daily_totals (
    pickup_date Date,
    total UInt64
)
ENGINE = SummingMergeTree
ORDER BY pickup_date

Create the materialized view on top of the source table:

CREATE MATERIALIZED VIEW default.my_view TO default.daily_totals
AS SELECT pickup_date, count() AS total
FROM default.trips
GROUP BY pickup_date

From this point on, every insert into default.trips automatically updates default.daily_totals. The view only processes new blocks of data, so the two million rows already in the table are not aggregated yet. Backfill them once with the same query:

INSERT INTO default.daily_totals
SELECT pickup_date, count() AS total
FROM default.trips
GROUP BY pickup_date

Note

The two CREATE statements are DDL, and databases in Managed ClickHouse use the Replicated engine, so each one returns one row per node instead of the empty output stock ClickHouse prints. See reading the output of a DDL statement.

Important

A materialized view aggregates within each inserted block, not across the whole destination table, so it writes one partial row per key per insert. That is why the destination uses SummingMergeTree rather than plain MergeTree: the engine collapses rows sharing the same ORDER BY key. Collapsing happens during background merges, at an unpredictable time, so always re-aggregate at read time rather than trusting a single row to be final:

SELECT pickup_date, sum(total) AS total
FROM default.daily_totals
GROUP BY pickup_date

With a plain MergeTree destination the rows would never collapse at all, and a query reading total directly would silently return one partial value per insert.

Note

Tables declared with a MergeTree family engine are automatically replicated by the service, so the data written by the materialized view is exchanged and replicated across all the nodes.

Last updated on August 18, 2026

Manage Managed ClickHouse® users and roles Query Managed ClickHouse® databases