Materialized View in ClickHouse?

  

1. Materialized Views

A Materialized View is a persistent view that:

 

·      Stores the result of a SELECT query physically.

·      Automatically updates when new data is inserted into the source table.

·      Can be queried just like a regular table.

 

Syntax

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]mv_name
[TO target_table]
ENGINE = <engine>
[ORDER BY ...]
AS SELECT ...
FROM source_table
[WHERE ...];

   

When you create a materialized view, you have two options for where the resulting data will be stored.

 

Option 1: Using TO target_table (External Table)

 

CREATE MATERIALIZED VIEW my_mv
TO my_target_table
AS SELECT ... FROM source_table;

Here:

·      my_mv is just a link.

·      Data is written into my_target_table.

·      You must create my_target_table yourself first.

·      Useful when you want full control over storage format, keys, engine, etc.

 

Option 2: Without TO (Internal Target Table)

 

CREATE MATERIALIZED VIEW my_mv
ENGINE = MergeTree()
ORDER BY id
AS SELECT ... FROM source_table;

   

Here:

·      ClickHouse creates an internal table behind the scenes.

·      You don’t create or manage the target table, it’s owned and named after the view.

·      It's quicker to set up, but less flexible.

 

Follow below step-by-step procedure to experiment with Materialized views.

 

Step 1: Create database and source table

 

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.users (
    id UInt32,
    name String,
    age UInt8,
    country String
) ENGINE = MergeTree()
ORDER BY id;

krishna :) CREATE DATABASE IF NOT EXISTS demo_db;

CREATE DATABASE IF NOT EXISTS demo_db

Query id: bdf2812c-05c1-4da7-920e-3e546870e272

Ok.

0 rows in set. Elapsed: 0.004 sec. 

krishna :) ;

Empty query

krishna :) CREATE TABLE demo_db.users (
    id UInt32,
    name String,
    age UInt8,
    country String
) ENGINE = MergeTree()
ORDER BY id;

CREATE TABLE demo_db.users
(
    `id` UInt32,
    `name` String,
    `age` UInt8,
    `country` String
)
ENGINE = MergeTree
ORDER BY id

Query id: b78973bc-4d34-485e-b522-dd60308f7561

Ok.

0 rows in set. Elapsed: 0.012 sec.

   

Step 2: Insert some data into users table.

 

INSERT INTO demo_db.users VALUES
(1, 'Ram', 30, 'India'),
(2, 'Krishna', 35, 'Canada'),
(3, 'Chamu', 25, 'India'),
(4, 'Sailu', 38, 'UK'),
(5, 'Gopi', 40, 'USA'),
(6, 'Joel', 45, 'India'),
(7, 'Jaideep', 35, 'USA'),
(8, 'Kishore', 38, 'India');

Step 3: Create a Materialized View with Internal Table.

CREATE MATERIALIZED VIEW demo_db.india_users_mv
ENGINE = MergeTree()
ORDER BY id
AS
SELECT id, name, age
FROM demo_db.users
WHERE country = 'India';

This creates a hidden internal table behind the view, storing India user data only.

krishna :) CREATE MATERIALIZED VIEW demo_db.india_users_mv
ENGINE = MergeTree()
ORDER BY id
AS
SELECT id, name, age
FROM demo_db.users
WHERE country = 'India';

CREATE MATERIALIZED VIEW demo_db.india_users_mv
ENGINE = MergeTree
ORDER BY id
AS SELECT
    id,
    name,
    age
FROM demo_db.users
WHERE country = 'India'

Query id: 47c3ef76-e36e-43d6-8acb-6101ab4c05e7

Ok.

0 rows in set. Elapsed: 0.017 sec. 

krishna :) ;

Empty query

krishna :) SELECT * FROM demo_db.india_users_mv;

SELECT *
FROM demo_db.india_users_mv

Query id: 9c7f49f0-029f-42c9-8765-1f1d261e6e3c

Ok.

0 rows in set. Elapsed: 0.002 sec.

   

Step 4: Query the Materialized View.

 

SELECT * FROM demo_db.india_users_mv;

krishna :) SELECT * FROM demo_db.india_users_mv;

SELECT *
FROM demo_db.india_users_mv

Query id: 4312b60f-2723-4b8c-b80e-76ce1b28fa24

Ok.

0 rows in set. Elapsed: 0.003 sec. 

   

Oops, I do not see any data, why is it like this?

It is because, Material view does NOT capture existing data already present in the source table at the time of creation.

 

When you create a materialized view in ClickHouse:

·      It only starts capturing data from new INSERT operations on the source table after the view is created.

·      It does not backfill the view with existing rows already in the source table.

 

Let’s add new records to users table and check this behavior.

 

INSERT INTO demo_db.users VALUES
(9, 'Govind', 30, 'India'),
(10, 'Sita', 35, 'Canada'),
(11, 'Laxman', 25, 'India'),
(12, 'Raheem', 38, 'UK'),
(13, 'Rudra', 40, 'USA'),
(14, 'Siva', 45, 'India'),
(15, 'Parvathi', 35, 'USA'),
(16, 'Brahma', 38, 'India');

   

Let’s query the materialized view again.

 

krishna :) SELECT * FROM demo_db.india_users_mv;

SELECT *
FROM demo_db.india_users_mv

Query id: 97ca6060-04a4-4462-bba9-d103bccbad2e

   ┌─id─┬─name───┬─age─┐
1. │  9 │ Govind │  30 │
2. │ 11 │ Laxman │  25 │
3. │ 14 │ Siva   │  45 │
4. │ 16 │ Brahma │  38 │
   └────┴────────┴─────┘

4 rows in set. Elapsed: 0.004 sec.

   

2. How to handle existing data?

If you want the materialized view to include old data that was already in the source table:

 

Option 1: Manually insert old data into the source table again

 

INSERT INTO source_table
SELECT * FROM source_table;

   

Option 2: Insert directly into the materialized view's target table

(Use with caution, especially with aggregation views)

 

INSERT INTO mv_target_table
SELECT ... FROM source_table;

   

3. Do Materialized Views in ClickHouse capture UPDATE and DELETE operations?

No, they do not. ClickHouse materialized views only react to INSERT operations on the source table. It is because:

·      ClickHouse is designed for append-only, high-speed OLAP workloads.

·      UPDATE and DELETE are supported but are relatively expensive and less common.

·      Materialized views are meant for streaming inserts and real-time aggregations, not full mutation tracking.

 

4. Categories Of Materialized Views

ClickHouse supports two types of Materialized views.

 

4.1 Incremental Materialized View

It captures the data incrementally as it is inserted into the source table. The view updates immediately (at insert time). In this model, computation cost is paid during insert, so queries are very fast.

 

Benefits

·      Super-fast SELECT queries on the view.

·      Ideal for real-time analytics (e.g., streaming dashboards).

·      Always up-to-date for new data.

 

Limitations

·      Does not reflect UPDATE or DELETE on the source table.

·      No automatic backfill, only new inserts are captured.

 

4.2 Refreshable Materialized View (manually or periodically refreshed)

Refreshable materialized views function similarly to those in traditional OLTP databases by storing the results of a predefined query to enable faster access and avoid repeated execution of heavy computations. Unlike ClickHouse's incremental materialized views, which update automatically on inserts, refreshable views require periodic re-execution of the full query over the entire dataset. The resulting output is saved to a target table, which is typically smaller and more efficient to query than the original source table.

 

Benefits

·      Can reflect updates/deletes because the view re-runs on the full dataset.

·      More flexible for mutable or slowly changing data.

 

Limitations

·      Costly during refresh (needs to scan full source data).

·      Not real-time unless frequently refreshed.

 

 

  

Previous                                                    Next                                                    Home