Part 8: Materialized Views
Real-time analytics made easy with ClickHouse Materialized Views.
Materialized Views (MVs) in ClickHouse are not like standard SQL views. They are triggers that insert data into a target table whenever data is inserted into a source table.
The Concept
Think of an MV as a real-time ETL pipeline that lives inside the database. It processes data as it arrives, pre-aggregating it for lightning-fast queries.
Creating a Materialized View
The most common pattern is to use an AggregatingMergeTree engine for the target table.
— 1. Create the target table CREATE TABLE daily_sales ( day Date, total_revenue SimpleAggregateFunction(sum, UInt64) ) ENGINE = AggregatingMergeTree() ORDER BY day;
— 2. Create the Materialized View CREATE MATERIALIZED VIEW daily_sales_mv TO daily_sales AS SELECT toDate(timestamp) AS day, sum(revenue) AS total_revenue FROM sales_raw GROUP BY day;
POPULATE Keyword
By default, MVs only process new data. If you want
to process existing data, you can use the POPULATE keyword,
but be careful—it blocks inserts during creation. A better approach
for backfilling is to create the MV without populate, and then run
an INSERT INTO … SELECT statement.
Why Use Them?
Speed: Queries run against pre-aggregated data, often scanning 1000x fewer rows.
Storage: Aggregated data takes up much less space than raw logs.
Simplicity: No need for external stream processors like Kafka Streams or Spark for simple aggregations.
Conclusion
Materialized Views are the secret weapon for building real-time dashboards. In the next part, we’ll look at how to tune ClickHouse for maximum performance.