Materialize view
First, materialized view definitions allow syntax similar to CREATE TABLE, which makes sense since this command will actually create a hidden target table to hold the view data. There are three important things to notice here. PARTITION BY toYYYYMM(day) ORDER BY (userid, day) CREATE MATERIALIZED VIEW download_daily_mv We can do exactly that with the following materialized view. It would therefore be better to have the results in a separate table that continuously tracks the sum of each user’s downloads by day. We could compute these daily totals interactively for applications by running the query each time, but for large tables it is faster and more resource efficient to compute them in advance. This will also work properly as new users are added. Next, let’s run a query to show daily downloads for that user. First, we need to add some data to the table for a single user. Let’s see how we could do this with a query. We would like to track daily downloads for each user. Suppose we have a table to record user downloads that looks like the following. They are like triggers that run queries over inserted rows and deposit the result in a second table. How Materialized Views Work: Computing SumsĬlickHouse materialized views automatically transform data between tables. Samples are completely self-contained, so you can copy/paste them into the clickhouse-client and run them yourself. Along the way we explore the exact meaning of syntax used to create views as well as give you insight into what ClickHouse is doing underneath. We’ll work a couple of detailed examples that you can adapt to your own uses. This 2-part article fills the gap by explaining exactly how materialized views work so that even beginners can use them effectively. They are one of the distinguishing features of ClickHouse.Īs usual in computing, great power implies at least a bit of complexity. Beyond these functional capabilities, materialized views scale well across large numbers of nodes and work on large datasets. Materialized views can compute aggregates, read data from Kafka, implement last point queries, and reorganize table primary indexes and sort order. Readers of the Altinity blog know we love ClickHouse materialized views.