Help Center/ CloudTable Service/ Developer Guide/ ClickHouse Application Development Guide/ SQL Syntax/ CREATE MATERIALIZED VIEW
Updated on 2024-04-29 GMT+08:00
View PDF
Share

CREATE MATERIALIZED VIEW

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

Creating a Materialized View

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

Parameter

Description

db

Name of the database. The default value is the selected database.

Materialized_name

Name of the materialized view.

TO[db.]name

This parameter specifies that the data of the materialized view is inserted into a new table.

[ON CLUSTER ClickHouse cluster name]

This parameter specifies that a materialized view is created on each node. The parameter format is ON CLUSTER ClickHouse cluster name.

ENGINE = engine_name()

Table engine type.

[POPULATE]

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. Otherwise, the materialized view contains only the data that is inserted into the source table after the materialized view is created.

NOTE:

The POPULATE keyword is not recommended 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 ...

SELECT clause. When you insert data into the source table that is specified in the SELECT clause in the materialized view, the inserted data is transformed 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 transformations are performed independently on each block of the data that is inserted.

Examples:

  1. Create a source table. 
    create table DB.table1 ON CLUSTER default_cluster (id Int16,name String) ENGINE = MergeTree() ORDER BY (id);
  2. Insert data. 
    insert into DB.table1 values(1,'X'),(2,'Y'),(3,'Z');
  3. Create a materialized view based on the source table. 
    CREATE MATERIALIZED VIEW demo_view ON CLUSTER default_cluster ENGINE = MergeTree() ORDER BY (id) AS SELECT * FROM DB.table1;
  4. Query the materialized view. 
    SELECT * FROM demo_view;

    If the query result is empty, the data that is written to the source table before the materialized view is created cannot be queried if the POPULATE keyword is not specified.

  5. Insert data to the DB.table1 table. 
    insert into demo_view values(4,'x'),(5,'y'),(6,'z');
  6. Query the materialized view. 
    SELECT * FROM demo_view;

    The following query result is returned:

    ┌─id─┬─name─┐
│  4 │ x    │
│  5 │ y    │
│  6 │ z    │
└────┴──────┘
Parent topic: SQL Syntax