ClickHouse创建物化视图 - ApsaraDB for ClickHouse - Alibaba Cloud Documentation Center

This topic describes how to create a materialized view in ApsaraDB for ClickHouse.

Create a materialized view

Syntax:

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

Parameter description

Parameter	Description
`db`	The name of the database. The default value is the name of the current database. In this example, default is used.
`Materialized_name`	The name of the materialized view.
`TO[db.]name`	Specifies that the data of the materialized view is written to a new table. Note If you want to write the data of a materialized view to a new table, you cannot use the `POPULATE` keyword.
`[ON CLUSTER cluster]`	Specifies that a materialized view is created on each node. Set the value to `ON CLUSTER default`.
`ENGINE = engine_name()`	The type of the table engine. For more information, see Table engines.
`[POPULATE]`	The `POPULATE` keyword. If you specify the `POPULATE` keyword when you create a materialized view, the data in the source table that is specified in the `SELECT` clause is inserted into the materialized view when the materialized view is being created. If you do not specify the `POPULATE` keyword, the materialized view contains only the data that is written to the source table after the materialized view is created. Note We recommend that you do not use the `POPULATE` keyword because the data that is written to the source table when the materialized view is being created is not inserted into the materialized view.
`SELECT ...`	The `SELECT` clause. When data is written to the source table that is specified in the `SELECT` clause of the materialized view, the inserted data is converted by the `SELECT` query and the final result is inserted into the materialized view. Note A `SELECT` query can contain `DISTINCT`, `GROUP BY`, `ORDER BY`, and `LIMIT`. The corresponding conversion is performed independently on each block of the data that is inserted.

Example:

Create a source table that is specified by the SELECT clause.

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

Write data to the source table.

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

Create a materialized view based on the source table.

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

Query the materialized view to check whether the data that is written to the source table before the materialized view is created can be queried if the POPULATE keyword is not specified.
```
SELECT * FROM test_view;
```
The query result is empty. This indicates that the data written to the source table before the materialized view is created cannot be queried if the POPULATE keyword is not specified.

Write data to the source table.

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

Query the materialized view.

SELECT * FROM test_view;

The following query result is returned:

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

References

For more information about how to create a materialized view, see Create Materialized View.