Creating a Materialized View

Basic 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, default is the currently selected database. In this context, ck_cluster is used as an example.
`Materialized_name`	The name of the materialized view.
`TO[db.]name`	Write the data of the materialized view into a new table. If you want to write the data of the materialized view into a new table, the `POPULATE` keyword cannot be used.
`[ON CLUSTER cluster]`	Create a materialized view on every node, fixed as `ON CLUSTER ck_cluster`.
`ENGINE = engine_name()`	Table engine type. For more details, please refer to Table Engine.
`[POPULATE]`	The `POPULATE` keyword. If the `POPULATE` keyword is specified when creating the materialized view, the source table data specified by the `SELECT` clause will be inserted into the materialized view upon creation. If the `POPULATE` keyword is not specified, the materialized view will only include the new data written to the source table after the view is created. Generally, it is not recommended to use the `POPULATE` keyword because the data written to the source table during the creation of the materialized view will not be included in the view.
`SELECT ...`	The `SELECT` clause. When data is written into the source table specified by the `SELECT` clause, the inserted data is transformed by the query in the `SELECT` clause, and the final result is inserted into the materialized view. The `SELECT` query can include `DISTINCT`, `GROUP BY`, `ORDER BY`, and `LIMIT`, etc., but the respective transformations are executed independently on each inserted data block.

Example:

Create the source table specified by the SELECT clause.


CREATE TABLE ck_test.lineorder_local on cluster ck_cluster
(
    LO_ORDERKEY             UInt32,
    LO_LINENUMBER           UInt8,
    LO_CUSTKEY              UInt32,
    LO_PARTKEY              UInt32,
    LO_SUPPKEY              UInt32,
    LO_ORDERDATE            Date,
    LO_ORDERPRIORITY        LowCardinality(String),
    LO_SHIPPRIORITY         UInt8,
    LO_QUANTITY             UInt8,
    LO_EXTENDEDPRICE        UInt32,
    LO_ORDTOTALPRICE        UInt32,
    LO_DISCOUNT             UInt8,
    LO_REVENUE              UInt32,
    LO_SUPPLYCOST           UInt32,
    LO_TAX                  UInt8,
    LO_COMMITDATE           Date,
    LO_SHIPMODE             LowCardinality(String)
)
ENGINE = ReplicatedMergeTree(
  '/clickhouse/ck_test/tables/{layer}-{shard}/lineorder',
  '{replica}'
) PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY)

Insert data into the source table.

Refer to Quick Start

Create a materialized view based on the source table.


CREATE MATERIALIZED VIEW lineorder_view ON CLUSTER ck_cluster
ENGINE = MergeTree()
ORDER BY (id) AS SELECT * FROM lineorder_local;

Query the materialized view to verify whether data written to the source table before the view was created can be queried, when the POPULATE keyword is not specified.
```
SELECT * FROM lineorder_view;
```
The query returns empty, indicating that without specifying the POPULATE keyword, data written to the source table prior to the creation of the materialized view is not accessible.
Insert data into the source table.

Refer to Quick Start
Query the materialized view.
```
SELECT * FROM lineorder_view;
```

Official Documentation

For more information on creating a materialized view, please see Create Materialized View .