What is a Materialized View in ClickHouse?

Materialized View in ClickHouse is a special database object that stores the result of a pre-computed query. Unlike a regular view, which is computed each time it is accessed, a materialized view is automatically updated when new data is inserted into the base table. This allows for faster execution of complex analytical queries, as the data is already aggregated or transformed.

Example of creating a materialized view:

CREATE MATERIALIZED VIEW mv_example
ENGINE = SummingMergeTree
AS
SELECT user_id, sum(amount) AS total_amount
FROM payments
GROUP BY user_id;

In this example, when new records are added to the payments table, the materialized view mv_example will automatically update, summing payments by users.