ClickHouse 物化视图学习总结

物化视图

物化视图源表--基础数据源

创建源表，因为我们的目标涉及报告聚合数据而不是单条记录，所以我们可以剖析它，将信息通报给物化视图，并抛弃现实传入的数据。这符合我们的目标并节省了存储空间，因此我们将使用Null表引擎。

1. CREATE DATABASE IF NOT EXISTS analytics;
3. CREATE TABLE analytics.hourly_data
4. (
5.     `domain_name` String,
6.     `event_time` DateTime,
7.     `count_views` UInt64
8. )
9. ENGINE = Null;

复制代码

留意：可以在Null表上创建物化视图。因此，写入表的数据终极会影响视图，但原始原始数据仍将被抛弃
月度汇总表和物化视图

对于第一个物化视图，需要创建 Target 表(本例子中为analytics.monthly_aggregated_data），例中将按月份和域名存储视图的总和。

1. CREATE TABLE analytics.monthly_aggregated_data
2. (
3.     `domain_name` String,
4.     `month` Date,
5.     `sumCountViews` AggregateFunction(sum, UInt64)
6. )
7. ENGINE = AggregatingMergeTree
8. ORDER BY (domain_name, month);

复制代码

将转发Target表上数据的物化视图如下：

1. CREATE MATERIALIZED VIEW analytics.monthly_aggregated_data_mv
2. TO analytics.monthly_aggregated_data
3. AS
4. SELECT
5.     toDate(toStartOfMonth(event_time)) AS month,
6.     domain_name,
7.     sumState(count_views) AS sumCountViews
8. FROM analytics.hourly_data
9. GROUP BY domain_name, month;

复制代码

年度汇总表和物化视图

现在，创建第二个物化视图，该视图将链接到之前的目标表monthly_aggregated_data。
首先，创建一个新的目标表，该表将存储每个域名每年汇总的视图总和。

1. CREATE TABLE analytics.year_aggregated_data
2. (
3.     `domain_name` String,
4.     `year` UInt16,
5.     `sumCountViews` UInt64
6. )
7. ENGINE = SummingMergeTree()
8. ORDER BY (domain_name, year);

复制代码

然后创建物化视图，此步骤定义级联。FROM 语句将使用monthly_aggregated_data表，这意味着数据流将是：
1.数据到达hourly_data表。
2.ClickHouse会将收到的数据转发到第一个物化视图monthly_aggregated_data 表
3.末了，步骤2中吸收到的数据将被转发到 year_aggregated_data。

1. CREATE MATERIALIZED VIEW analytics.year_aggregated_data_mv
2. TO analytics.year_aggregated_data
3. AS
4. SELECT
5.     toYear(toStartOfYear(month)) AS year,
6.     domain_name,
7.     sumMerge(sumCountViews) as sumCountViews
8. FROM analytics.monthly_aggregated_data
9. GROUP BY domain_name, year;

复制代码

留意：
在使用物化视图时，一个常见的误解是数据是从表中读取的，这不是Materialized views的工作方式；转发的数据是插入的数据块，而不是表中的终极结果。
想象一下，在这个例子中，monthly_aggregated_data中使用的引擎是一个折叠合并树(CollapsingMergeTree)，转发到第二个物化视图year_aggregated_data_mv 的数据将不是折叠表的终极结果，它将转发具有正如SELECT… GROUP BY中定义的字段的数据块。
如果末正在使用CollapsingMergeTree、ReplacingMergeTree，甚至SummingMergeTree，并且计划创建级联物化视图，则需要相识此处描述的限制。
采集数据

现在是时候通过插入一些数据来测试我们的级联物化视图了:

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

复制代码

查询analytics.hourly_data的内容，将查不到任何记录，因为表引擎为Null，但数据已被处置惩罚

1. SELECT * FROM analytics.hourly_data

复制代码

输出：

1. domain_name|event_time|count_views|
2. -----------+----------+-----------+

复制代码

结果

如果实验查询目标表的sumCountViews字段值，将看到字段值以二进制表示（在某些终端中），因为该值不是以数字的情势存储，而是以AggregateFunction类型存储的。要获得聚合的终极结果，应该使用-Merge后缀。
通过以下查询，sumCountViews字段值无法正常显示：

1. SELECT sumCountViews FROM analytics.monthly_aggregated_data

复制代码

输出：

1. sumCountViews|
2. -------------+
3.              |
4.              |
5.              |

复制代码

使用 Merge后缀获取 sumCountViews 值:

1. SELECT sumMerge(sumCountViews) as sumCountViews
2. FROM analytics.monthly_aggregated_data;

复制代码

输出：

1. sumCountViews|
2. -------------+
3.            12|

复制代码

在AggregatingMergeTree 中将AggregateFunction 定义为sum，因此可以使用sumMerge。当在AggregateFunction上使用函数avg时，则将使用avgMerge，以此类推。

1. SELECT month, domain_name, sumMerge(sumCountViews) as sumCountViews
2. FROM analytics.monthly_aggregated_data
3. GROUP BY domain_name, month

复制代码

输出：

1. month     |domain_name   |sumCountViews|
2. ----------+--------------+-------------+
3. 2020-01-01|clickhouse.com|            6|
4. 2019-01-01|clickhouse.com|            1|
5. 2019-02-01|clickhouse.com|            5|

复制代码

现在我们可以查看物化视图是否符合我们定义的目标。
现在已经将数据存储在目标表monthly_aggregated_data中，可以按月聚合每个域名的数据：

1. SELECT month, domain_name, sumMerge(sumCountViews) as sumCountViews
2. FROM analytics.monthly_aggregated_data
3. GROUP BY domain_name, month;

复制代码

输出：

1. month     |domain_name   |sumCountViews|
2. ----------+--------------+-------------+
3. 2020-01-01|clickhouse.com|            6|
4. 2019-01-01|clickhouse.com|            1|
5. 2019-02-01|clickhouse.com|            5|

复制代码

按年聚合每个域名的数据:

1. SELECT year, domain_name, sum(sumCountViews)
2. FROM analytics.year_aggregated_data
3. GROUP BY domain_name, year;

复制代码

输出：

1. year|domain_name   |sum(sumCountViews)|
2. ----+--------------+------------------+
3. 2019|clickhouse.com|                 6|
4. 2020|clickhouse.com|                 6|

复制代码

组合多个源表来创建单个目标表

物化视图还可以用于将多个源表组合以到一个目标表中。这对于创建类似于 UNION ALL逻辑的物化视图非常有效。
首先，创建两个代表差别指标集的源表:

1. CREATE TABLE analytics.impressions
2. (
3.     `event_time` DateTime,
4.     `domain_name` String
5. ) ENGINE = MergeTree ORDER BY (domain_name, event_time);
7. CREATE TABLE analytics.clicks
8. (
9.     `event_time` DateTime,
10.     `domain_name` String
11. ) ENGINE = MergeTree ORDER BY (domain_name, event_time);

复制代码

然后使用组合的指标集创建 Target表：

1. CREATE TABLE analytics.daily_overview
2. (
3.     `on_date` Date,
4.     `domain_name` String,
5.     `impressions` SimpleAggregateFunction(sum, UInt64),
6.     `clicks` SimpleAggregateFunction(sum, UInt64)
7. ) ENGINE = AggregatingMergeTree ORDER BY (on_date, domain_name);

复制代码

创建两个指向同一Target表的物化视图。不需要显式地包含缺少的列：
[code]CREATE MATERIALIZED VIEW analytics.daily_impressions_mvTO analytics.daily_overviewAS                                                SELECT    toDate(event_time) AS on_date,    domain_name,    count() AS impressions,    0 clicks   --