Cascading materialized views

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/developer/cascading-materialized-views.md)

This example demonstrates how to create a materialized view, and then how to cascade a second materialized view on to the first. In this page, you will see how to do it, many of the possibilities, and the limitations. Different use cases can be answered by creating a Materialized view using a second Materialized view as the source.

  

Example:

We will use a fake dataset with the number of views per hour for a group of domain names.

Our Goal

1. We need the data aggregated by month for each domain name,
2. We also need the data aggregated by year for each domain name.

You could choose one of these options:

- Write queries that will read and aggregate the data during the SELECT request
- Prepare the data at the ingest time to a new format
- Prepare the data at the time of ingest to a specific aggregation.

Preparing the data using Materialized views will allow you to limit the amount of data and calculation ClickHouse needs to do, making your SELECT requests faster.

## Source table for the materialized views[​](#source-table-for-the-materialized-views "Direct link to Source table for the materialized views")

Create the source table, because our goals involve reporting on the aggregated data and not the individual rows, we can parse it, pass the information on to the Materialized Views, and discard the actual incoming data. This meets our goals and saves on storage so we will use the `Null` table engine.

```
CREATE DATABASE IF NOT EXISTS analytics;
```

```
CREATE TABLE analytics.hourly_data
(
    `domain_name` String,
    `event_time` DateTime,
    `count_views` UInt64
)
ENGINE = Null
```

Note

You can create a materialized view on a Null table. So the data written to the table will end up affecting the view, but the original raw data will still be discarded.

## Monthly aggregated table and materialized view[​](#monthly-aggregated-table-and-materialized-view "Direct link to Monthly aggregated table and materialized view")

For the first materialized view, we need to create the `Target` table, for this example, it will be `analytics.monthly_aggregated_data` and we will store the sum of the views by month and domain name.

```
CREATE TABLE analytics.monthly_aggregated_data
(
    `domain_name` String,
    `month` Date,
    `sumCountViews` AggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (domain_name, month)
```

The materialized view that will forward the data on the target table will look like this:

```
CREATE MATERIALIZED VIEW analytics.monthly_aggregated_data_mv
TO analytics.monthly_aggregated_data
AS
SELECT
    toDate(toStartOfMonth(event_time)) AS month,
    domain_name,
    sumState(count_views) AS sumCountViews
FROM analytics.hourly_data
GROUP BY
    domain_name,
    month
```

## Yearly aggregated table and materialized view[​](#yearly-aggregated-table-and-materialized-view "Direct link to Yearly aggregated table and materialized view")

Now we will create the second Materialized view that will be linked to our previous target table `monthly_aggregated_data`.

First, we will create a new target table that will store the sum of views aggregated by year for each domain name.

```
CREATE TABLE analytics.year_aggregated_data
(
    `domain_name` String,
    `year` UInt16,
    `sumCountViews` UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (domain_name, year)
```

This step defines the cascade. The `FROM` statement will use the `monthly_aggregated_data` table, this means the data flow will be:

1. The data comes to the `hourly_data` table.
2. ClickHouse will forward the data received to the first materialized view `monthly_aggregated_data` table,
3. Finally, the data received in step 2 will be forwarded to the `year_aggregated_data`.

```
CREATE MATERIALIZED VIEW analytics.year_aggregated_data_mv
TO analytics.year_aggregated_data
AS
SELECT
    toYear(toStartOfYear(month)) AS year,
    domain_name,
    sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
    domain_name,
    year
```

Note

A common misinterpretation when working with Materialized views is that data is read from the table, This isn't how `Materialized views` work; the data forwarded is the inserted block, not the final result in your table.

Let's imagine in this example that the engine used in `monthly_aggregated_data` is a CollapsingMergeTree, the data forwarded to our second Materialized view `year_aggregated_data_mv` won't be the final result of the collapsed table, it will forward the block of data with the fields defined as in the `SELECT ... GROUP BY`.

If you're using CollapsingMergeTree, ReplacingMergeTree, or even SummingMergeTree and you plan to create a cascade Materialized view you need to understand the limitations described here.

## Sample data[​](#sample-data "Direct link to Sample data")

Now is the time to test our cascade materialized view by inserting some data:

```
INSERT INTO analytics.hourly_data (domain_name, event_time, count_views)
VALUES ('clickhouse.com', '2019-01-01 10:00:00', 1),
       ('clickhouse.com', '2019-02-02 00:00:00', 2),
       ('clickhouse.com', '2019-02-01 00:00:00', 3),
       ('clickhouse.com', '2020-01-01 00:00:00', 6);
```

If you SELECT the contents of `analytics.hourly_data` you will see the following because the table engine is `Null`, but the data was processed.

```
SELECT * FROM analytics.hourly_data
```

```
Ok.

0 rows in set. Elapsed: 0.002 sec.
```

We have used a small dataset to be sure we can follow and compare the result with what we're expecting, once your flow is correct with a small data set, you could just move to a large amount of data.

## Results[​](#results "Direct link to Results")

If you try to query the target table by selecting the `sumCountViews` field, you will see the binary representation (in some terminals), as the value isn't stored as a number but as an AggregateFunction type.
To get the final result of the aggregation you should use the `-Merge` suffix.

You can see the special characters stored in AggregateFunction with this query:

```
SELECT sumCountViews FROM analytics.monthly_aggregated_data
```

```
┌─sumCountViews─┐
│               │
│               │
│               │
└───────────────┘

3 rows in set. Elapsed: 0.003 sec.
```

Instead, let's try using the `Merge` suffix to get the `sumCountViews` value:

```
SELECT
   sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data;
```

```
┌─sumCountViews─┐
│            12 │
└───────────────┘

1 row in set. Elapsed: 0.003 sec.
```

In the `AggregatingMergeTree` we have defined the `AggregateFunction` as `sum`, so we can use the `sumMerge`. When we use the function `avg` on the `AggregateFunction`, we will use `avgMerge`, and so forth.

```
SELECT
    month,
    domain_name,
    sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
    domain_name,
    month
```

Now we can review that the Materialized Views answer the goal we have defined.

Now that we have the data stored in the target table `monthly_aggregated_data` we can get the data aggregated by month for each domain name:

```
SELECT
   month,
   domain_name,
   sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
   domain_name,
   month
```

```
┌──────month─┬─domain_name────┬─sumCountViews─┐
│ 2020-01-01 │ clickhouse.com │             6 │
│ 2019-01-01 │ clickhouse.com │             1 │
│ 2019-02-01 │ clickhouse.com │             5 │
└────────────┴────────────────┴───────────────┘

3 rows in set. Elapsed: 0.004 sec.
```

The data aggregated by year for each domain name:

```
SELECT
   year,
   domain_name,
   sum(sumCountViews)
FROM analytics.year_aggregated_data
GROUP BY
   domain_name,
   year
```

```
┌─year─┬─domain_name────┬─sum(sumCountViews)─┐
│ 2019 │ clickhouse.com │                  6 │
│ 2020 │ clickhouse.com │                  6 │
└──────┴────────────────┴────────────────────┘

2 rows in set. Elapsed: 0.004 sec.
```