v.24.12Improvement

Create Materialized View Now Supports UNION ALL with Insert Behavior Consistent with JOIN

Official CH changelog text
Now it's allowed to CREATE MATERIALIZED VIEW with UNION [ALL] in query. Behavior is the same as for matview with JOIN: only the first table in SELECT expression will work as a trigger for insert, all other tables will be ignored. However, if there are many references to the first table (e.g., UNION with itself), all of them will be processed as the inserted block of data. #72347 (alesapin).
ClickHouse now supports creating MATERIALIZED VIEW with UNION [ALL] in the query, enabling union-based transformations within materialized views.

Why it matters

This feature allows users to define materialized views that aggregate data from multiple tables or repeated references to the same table using UNION. It solves the limitation where only simple queries or JOINs were supported, expanding flexibility for data transformations and incremental processing triggered by inserts into the first table in the SELECT clause.

How to use it

Create a MATERIALIZED VIEW with a query containing UNION [ALL]. Inserts will trigger processing based on the first table referenced in the SELECT statement, including all its references if repeated (e.g., UNION with itself). Other tables involved in the union are ignored as triggers. Example syntax:

CREATE MATERIALIZED VIEW view_name TO target_table AS
SELECT  FROM table1
UNION ALL
SELECT  FROM table2


Only inserts into table1 will trigger the materialized view refresh.

Related resources

PreviousNext