CREATE MATERIALIZED VIEW

Updated at:

A materialized view in ApsaraDB for ClickHouse is an insert trigger, not a static snapshot. Each time data is inserted into the source table, the SELECT query defined in the view runs on that inserted block and writes the result to the view's storage. This makes materialized views useful for pre-aggregating data, filtering streams, or routing inserts to multiple tables—without re-scanning the entire source table.

Tip: For end-to-end examples of common patterns—aggregation, filtering, and cascading views—see Incremental materialized views.

Syntax

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]view_name [TO [db.]target_table] [ON CLUSTER cluster]
ENGINE = engine_name()
ORDER BY expr
[POPULATE]
AS SELECT ...

There are two ways to create a materialized view:

  • Without `TO [db.]target_table`: ClickHouse creates an internal storage table automatically. Specify ENGINE to define the table engine for that internal table.

  • With `TO [db.]target_table`: Data is written to an existing table you specify. In this mode, omit ENGINE and do not use POPULATE.

Parameters

ParameterDescription
dbDatabase name. Defaults to the current database.
view_nameName of the materialized view.
TO [db.]target_tableWrites view output to an existing table. Cannot be combined with POPULATE.
ON CLUSTER clusterCreates the view on every node in the cluster. Set to ON CLUSTER default.
ENGINE = engine_name()Table engine for the internal storage table (required when TO is omitted). See Table engines.
POPULATEIf specified, existing rows in the source table are inserted into the view at creation time. If omitted, the view captures only data inserted after creation.
AS SELECT ...Defines the transformation. Supports DISTINCT, GROUP BY, ORDER BY, and LIMIT. The query runs independently on each inserted block, not on the full table.
Important

Avoid POPULATE in production. Data written to the source table while the view is being created falls into a gap and is never captured. To backfill an existing table safely: 1. Pause writes to the source table. 2. Create the materialized view (without POPULATE). 3. Copy existing data into the target table manually. 4. Resume writes.

Example

The following example shows that a materialized view captures only data inserted after it is created.

Step 1. Create a source table.

CREATE TABLE test ON CLUSTER default (
  id   Int32,
  name String
) ENGINE = ReplicatedMergeTree()
ORDER BY (id);

Step 2. Insert initial rows into the source table.

INSERT INTO test VALUES(1,'a'),(2,'b'),(3,'c');

Step 3. Create a materialized view without POPULATE.

CREATE MATERIALIZED VIEW test_view ON CLUSTER default
ENGINE = MergeTree()
ORDER BY (id) AS SELECT * FROM test;

Step 4. Query the view. Because POPULATE was not specified, rows inserted before the view was created are not captured.

SELECT * FROM test_view;

The result is empty.

Step 5. Insert new rows.

INSERT INTO test VALUES(4,'a'),(5,'b'),(6,'c');

Step 6. Query the view again. Only rows inserted after the view was created appear.

SELECT * FROM test_view;

Result:

id │ name
───┼─────
 4 │ a
 5 │ b
 6 │ c

What's next