How to Create a Materialized View in ClickHouse
Last updated July 25, 2026 · By the SaturnSQL team
Use CREATE MATERIALIZED VIEW ... TO target AS SELECT. A ClickHouse materialized view is an insert trigger: it transforms rows as they arrive in the source table and writes them to the target table. It does not backfill existing data, so populate history with a manual INSERT ... SELECT.
CREATE TABLE daily_pageviews
(
day Date,
pageviews UInt64
)
ENGINE = SummingMergeTree
ORDER BY day;
CREATE MATERIALIZED VIEW daily_pageviews_mv TO daily_pageviews AS
SELECT toDate(event_time) AS day, count() AS pageviews
FROM events
WHERE event_type = 'page_view'
GROUP BY day;Backfill existing rows
INSERT INTO daily_pageviews
SELECT toDate(event_time) AS day, count() AS pageviews
FROM events
WHERE event_type = 'page_view'
GROUP BY day;Prefer the TO table form over the implicit inner table: it keeps the storage visible, lets you choose the engine, and makes backfills and schema changes straightforward.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free