CREATE MATERIALIZED VIEW

Updated on 2024-04-29 GMT+08:00

View PDF

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:

Create a source table.

create table DB.table1 ON CLUSTER default_cluster (id Int16,name String) ENGINE = MergeTree() ORDER BY (id);

Insert data.

insert into DB.table1 values(1,'X'),(2,'Y'),(3,'Z');

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;

Query the materialized view.
```
SELECT * FROM demo_view;
```
NOTE:

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.

Insert data to the DB.table1 table.

insert into demo_view values(4,'x'),(5,'y'),(6,'z');

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

Previous topic: CREATE VIEW

Next topic: INSERT INTO

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot