How to Create a Materialized View in ClickHouse

Galaxy Glossary

How do I create a materialized view in ClickHouse?

CREATE MATERIALIZED VIEW stores query results on disk and keeps them in sync with source tables.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is a ClickHouse materialized view used for?

Reduce query latency by pre-computing results. ClickHouse stores the output table on disk and refreshes it automatically when source data changes.

How do I create a materialized view with aggregation?

Use CREATE MATERIALIZED VIEW ... TO ... AS SELECT. The TO clause names the destination table. Include POPULATE to backfill existing data.

Example use case: daily revenue

Aggregate Orders rows into a daily summary so dashboards read from a small lookup table instead of scanning every order.

Will the view stay current as new orders arrive?

Yes. ClickHouse updates the destination table whenever inserts hit the source table, using the SELECT logic you define.

Can I refresh or rebuild the view?

Drop and recreate it, or truncate the destination table and run ALTER TABLE ... MATERIALIZE for specific partitions.

Best practices

Partition the destination table on the same key used in queries, keep SELECT deterministic, and avoid heavy joins that nullify performance gains.

Why How to Create a Materialized View in ClickHouse is important

How to Create a Materialized View in ClickHouse Example Usage


-- 1. Destination table
CREATE TABLE daily_customer_revenue (
    customer_id UInt32,
    order_day    Date,
    daily_revenue Decimal(12,2)
) ENGINE = MergeTree()
PARTITION BY order_day
ORDER BY (customer_id, order_day);

-- 2. Materialized view that fills the above table
CREATE MATERIALIZED VIEW mv_daily_revenue
TO daily_customer_revenue AS
SELECT
    customer_id,
    toDate(order_date)  AS order_day,
    sum(total_amount)   AS daily_revenue
FROM Orders
GROUP BY customer_id, order_day;

How to Create a Materialized View in ClickHouse Syntax


CREATE MATERIALIZED VIEW [IF NOT EXISTS] mv_name
    [POPULATE]
    [ENGINE = destination_engine]
    [ORDER BY expr]
    [PARTITION BY expr]
TO dest_table
AS SELECT
    customer_id,
    toDate(order_date)  AS order_day,
    sum(total_amount)   AS daily_revenue
FROM Orders
GROUP BY customer_id, order_day;

Common Mistakes

Omitting the TO clause and forgetting that ClickHouse will then create its own destination table with a generic engine. Fix: explicitly supply TO and control engine, partitioning, and order-by keys.
Using POPULATE on huge tables without enough resources, causing long blocking operations. Fix: backfill manually with INSERT SELECT or run POPULATE during low-traffic windows.