ClickHouse 物化视图学习总结

物化视图

物化视图源表--根底数据源

创立源表，由于咱们的方针触及陈述聚合数据而不是单条记载，所以咱们能够解析它，将信息传递给物化视图，并丢掉实践传入的数据。这契合咱们的方针并节省了存储空间，因而咱们将运用Null表引擎。

CREATE DATABASE IF NOT EXISTS analytics;

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

留意：能够在Null表上创立物化视图。因而，写入表的数据终究会影响视图，但原始原始数据仍将被丢掉

月度汇总表和物化视图

关于第一个物化视图，需求创立 Target 表(本比方中为analytics.monthly_aggregated_data），例中将按月份和域名存储视图的总和。

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

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

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;

年度汇总表和物化视图

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

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

然后创立物化视图，此过程界说级联。FROM 句子将运用monthly_aggregated_data表，这意味着数据流将是：
1.数据抵达hourly_data表。
2.ClickHouse会将收到的数据转发到第一个物化视图monthly_aggregated_data 表
3.终究，过程2中接收到的数据将被转发到 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;

留意：

在运用物化视图时，一个常见的误解是数据是从表中读取的，这不是Materialized views的工作方式；转发的数据是刺进的数据块，而不是表中的终究成果。

幻想一下，在这个比方中，monthly_aggregated_data中运用的引擎是一个折叠兼并树(CollapsingMergeTree)，转发到第二个物化视图year_aggregated_data_mv 的数据将不是折叠表的终究成果，它将转发具有正如SELECT… GROUP BY中界说的字段的数据块。

假如末正在运用CollapsingMergeTree、ReplacingMergeTree，乃至SummingMergeTree，而且方案创立级联物化视图，则需求了解此处描绘的约束。

收集数据

现在是时分经过刺进一些数据来测验咱们的级联物化视图了:

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);

查询analytics.hourly_data的内容，将查不到任何记载，由于表引擎为Null，但数据已被处理

 SELECT * FROM analytics.hourly_data

输出：

domain_name|event_time|count_views|
-----------+----------+-----------+

成果

假如测验查询方针表的sumCountViews字段值，将看到字段值以二进制表明（在某些终端中），由于该值不是以数字的方式存储，而是以AggregateFunction类型存储的。要取得聚合的终究成果，应该运用-Merge后缀。

经过以下查询，sumCountViews字段值无法正常显现：

SELECT sumCountViews FROM analytics.monthly_aggregated_data

输出：

sumCountViews|
-------------+
             |
             |
             |

运用 Merge后缀获取 sumCountViews 值:

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

输出：

sumCountViews|
-------------+
           12|

在AggregatingMergeTree 中将AggregateFunction 界说为sum，因而能够运用sumMerge。当在AggregateFunction上运用函数avg时，则将运用avgMerge，以此类推。

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|

现在咱们能够检查物化视图是否契合咱们界说的方针。

现在现已将数据存储在方针表monthly_aggregated_data中，能够按月聚合每个域名的数据：

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|

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

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|

组合多个源表来创立单个方针表

物化视图还能够用于将多个源表组合以到一个方针表中。这关于创立相似于 UNION ALL逻辑的物化视图十分有用。

首要，创立两个代表不同方针集的源表:

CREATE TABLE analytics.impressions
(
    `event_time` DateTime,
    `domain_name` String
) ENGINE = MergeTree ORDER BY (domain_name, event_time);

CREATE TABLE analytics.clicks
(
    `event_time` DateTime,
    `domain_name` String
) ENGINE = MergeTree ORDER BY (domain_name, event_time);

然后运用组合的方针集创立 Target表：

CREATE TABLE analytics.daily_overview
(
    `on_date` Date,
    `domain_name` String,
    `impressions` SimpleAggregateFunction(sum, UInt64),
    `clicks` SimpleAggregateFunction(sum, UInt64)
) ENGINE = AggregatingMergeTree ORDER BY (on_date, domain_name);

创立两个指向同一Target表的物化视图。不需求显式地包含短少的列：

CREATE MATERIALIZED VIEW analytics.daily_impressions_mv
TO analytics.daily_overview
AS                                                
SELECT
    toDate(event_time) AS on_date,
    domain_name,
    count() AS impressions,
    0 clicks   --<<<--- 假如去掉该列，则默以为 clicks为0
FROM                                              
    analytics.impressions
GROUP BY toDate(event_time) AS on_date, domain_name;

CREATE MATERIALIZED VIEW analytics.daily_clicks_mv
TO analytics.daily_overview
AS
SELECT
    toDate(event_time) AS on_date,
    domain_name,
    count() AS clicks,
    0 impressions    --<<<---假如去掉该列，则默以为 impressions 为0
FROM
    analytics.clicks
GROUP BY toDate(event_time) AS on_date, domain_name;

现在，当刺进值时，这些值将被聚合到Target表中的相应列中：

INSERT INTO analytics.impressions (domain_name, event_time)
VALUES ('clickhouse.com', '2019-01-01 00:00:00'),
       ('clickhouse.com', '2019-01-01 12:00:00'),
       ('clickhouse.com', '2019-02-01 00:00:00'),
       ('clickhouse.com', '2019-03-01 00:00:00')
;

INSERT INTO analytics.clicks (domain_name, event_time)
VALUES ('clickhouse.com', '2019-01-01 00:00:00'),
       ('clickhouse.com', '2019-01-01 12:00:00'),
       ('clickhouse.com', '2019-03-01 00:00:00')
;

查询方针表 the Target table:

SELECT
    on_date,
    domain_name,
    sum(impressions) AS impressions,
    sum(clicks) AS clicks
FROM
    analytics.daily_overview
GROUP BY
    on_date,
    domain_name
;

输出：

on_date   |domain_name   |impressions|clicks|
----------+--------------+-----------+------+
2019-01-01|clickhouse.com|          2|     2|
2019-03-01|clickhouse.com|          1|     1|
2019-02-01|clickhouse.com|          1|     0|

参看链接

https://clickhouse.com/docs/en/guides/developer/cascading-materialized-views

AggregateFunction

聚合函数有一个完成界说的中间状况，能够序列化为AggregateFunction(...)数据类型，并一般经过物化视图存储在表中。生成聚合函数状况的常见办法是运用State后缀调用聚合函数。为了今后能取得聚合的终究成果，有必要运用带有-Merge后缀的相同聚合函数。

AggregateFunction(name, types_of_arguments...) — 参数数据类型。

参数阐明：

• 聚合函数称号。假如称号对应的聚合函数鞋带参数，则还需求为其它指定参数。
• 聚合函数参数类型。

示例

CREATE TABLE testdb.aggregated_test_tb
(   
    `__name__` String, 
    `count` AggregateFunction(count),
    `avg_val` AggregateFunction(avg, Float64),
    `max_val` AggregateFunction(max, Float64),
    `time_max` AggregateFunction(argMax, DateTime, Float64),
    `mid_val` AggregateFunction(quantiles(0.5, 0.9), Float64) 
) ENGINE = AggregatingMergeTree() 
ORDER BY (__name__);

补白：假如上述SQL未增加ORDER BY (__name__, create_time)，履行会报相似如下过错：

SQL 过错 [42]: ClickHouse exception, code: 42, host: 192.168.88.131, port: 8123; Code: 42, e.displayText() = DB::Exception: Storage AggregatingMergeTree requires 3 to 4 parameters: 
name of column with date,
[sampling element of primary key],
primary key expression,
index granularity

创立数据源表并刺进测验数据

CREATE TABLE testdb.test_tb 
(
    `__name__` String, 
    `create_time` DateTime, 
    `val` Float64
) ENGINE = MergeTree() 
PARTITION BY toStartOfWeek(create_time) 
ORDER BY (__name__, create_time);

INSERT INTO testdb.test_tb(`__name__`, `create_time`, `val`) VALUES
('xiaoxiao', now(), 80.5),
('xiaolin', addSeconds(now(), 10), 89.5),
('xiaohong', addSeconds(now(), 20), 90.5),
('lisi', addSeconds(now(), 30), 79.5),
('zhangshang', addSeconds(now(), 40), 60),
('wangwu', addSeconds(now(), 50), 65);

刺进数据

运用以State后缀的聚合函数的INSERT SELECT 以刺进数据--比方期望获取方针列数据均值，即avg(target_column)，那么刺进数据时运用的聚合函数为avgState，*State聚合函数回来状况(state)，而不是终究值。换句话说，回来一个 AggregateFunction 类型的值。

INSERT INTO testdb.aggregated_test_tb (`__name__`, `count`, `avg_val`, `max_val`, `time_max`, `mid_val`)
SELECT `__name__`,
countState() AS count,
avgState(val) AS avg_val, 
maxState(val) AS max_val,
argMaxState(create_time, val) AS time_max,
quantilesState(0.5, 0.9)(val) AS `mid_val`
FROM testdb.test_tb
GROUP BY `__name__`, toStartOfMinute(create_time);

留意：SELECT句子中的字段，要么运用聚合函数调用(比方上述val字段)，要么坚持原字段不变(比方上述__name__字段)，坚持原字段不变时，该字段有必要包含于GROUP BY子句中，不然会报相似如下过错：

SQL 过错 [215]: ClickHouse exception, code: 215, host: 192.168.88.131, port: 8123; Code: 215, e.displayText() = DB::Exception: Column `__name__` is not under aggregate function and not in GROUP BY (version 20.3.5.21 (official build))

查询数据

从AggregatingMergeTree表中查询数据时，运用GROUP BY子句和与刺进数据时相同的聚合函数，但运用Merge后缀，比方刺进数据时运用的聚合函数为avgState，那么查询时运用的聚合函数为avgMerge。

后缀为Merge的聚合函数承受一组状况，将它们组合在一起，并回来完好数据聚合的成果。

例如，以下两个查询回来相同的成果

SELECT `__name__`, 
create_time,
avgMerge(avg_val) AS avg_val, 
maxMerge(max_val) AS max_val
FROM ( 
SELECT `__name__`, 
toStartOfMinute(create_time) AS create_time,
avgState(val) AS avg_val, 
maxState(val) AS max_val
FROM testdb.test_tb
GROUP BY `__name__`, create_time
)
GROUP BY `__name__`, create_time;

SELECT `__name__`, 
toStartOfMinute(create_time) AS create_time,
avg(val) AS avg_val, 
max(val) AS max_val
FROM testdb.test_tb
GROUP BY `__name__`, create_time;

比方：

SELECT `__name__`, 
countMerge(`count`), 
avgMerge(`avg_val`), 
maxMerge(`max_val`),
argMaxMerge(`time_max`),
quantilesMerge(0.5, 0.9)(`mid_val`)
FROM testdb.aggregated_test_tb
GROUP BY `__name__`;

参看链接

https://clickhouse.com/docs/en/sql-reference/data-types/aggregatefunction

AggregatingMergeTree

引擎承继自MergeTree，更改了数据块兼并的逻辑。ClickHouse运用一条存储了聚合函数状况组合的单条记载(在一个数据块中)替换带有相同主键(或更精确地说，用相同的排序键)的一切行

阐明：数据块是指ClickHouse存储数据的基本单位

能够运用 AggregatingMergeTree 表进行增量数据聚合，包含聚合物化视图。

引擎处理以下类型的一切列：

• AggregateFunction

• SimpleAggregateFunction

  假如能削减有序行数，则运用AggregatingMergeTree是适宜的

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...]

有关恳求参数的描绘，参看恳求描绘

查询句子

创立AggregatingMergeTree表与创立MergeTree表的子句相同。

查询和刺进

要刺进数据，运用INSERT SELECT运用aggregateState函数进行查询。从AggregatingMergeTree表中查询数据时，运用GROUP BY子句和与刺进数据时相同的聚合函数，但运用Merge后缀。

在SELECT查询的成果中，AggregateFunction类型的值对一切ClickHouse输出格局都有特定于完成的二进制表明。例如，假如你能够运用SELECT查询将数据转储为TabSeparated格局，则能够运用INSERT查询将此转储从头加载。

一个物化视图示例

CREATE DATABASE testdb;

创立寄存原始数据的testdb.visits表:

CREATE TABLE testdb.visits
(
    StartDate DateTime64, 
    CounterID UInt64,
    Sign Nullable(Int32),
    UserID Nullable(Int32)
) ENGINE = MergeTree 
ORDER BY (StartDate, CounterID);

阐明：上述StartDate DateTime64, 假如写成StartDate DateTime64 NOT NULL, 运转会报错，如下：

Expected one of: CODEC, ALIAS, TTL, ClosingRoundBracket, Comma, DEFAULT, MATERIALIZED, COMMENT, token (version 20.3.5.21 (official build))

接下来，创立一个AggregatingMergeTree表，该表将存储AggregationFunction，用于盯梢拜访总数和仅有用户数。

创立一个AggregatingMergeTree 物化视图，用于监督testdb.revisits表，并运用AggregateFunction 类型：

CREATE TABLE testdb.agg_visits (
    StartDate DateTime64,
    CounterID UInt64,
    Visits AggregateFunction(sum, Nullable(Int32)),
    Users AggregateFunction(uniq, Nullable(Int32))
)
ENGINE = AggregatingMergeTree() ORDER BY (StartDate, CounterID);

SQL 过错 [70]: ClickHouse exception, code: 70, host: 192.168.88.131, port: 8123; Code: 70, e.displayText() = DB::Exception: Conversion from AggregateFunction(sum, Int32) to AggregateFunction(sum, Nullable(Int32)) is not supported: while converting source column Visits to destination column Visits: while pushing to view testdb.visits_mv (version 20.3.5.21 (official build))

CREATE TABLE testdb.agg_visits (
    StartDate DateTime64,
    CounterID UInt64,
    Visits AggregateFunction(sum, Int32),
    Users AggregateFunction(uniq, Int32)
)
ENGINE = AggregatingMergeTree() ORDER BY (StartDate, CounterID);

创立一个物化视图，从testdb.revisits填充testdb.agg_visits：

CREATE MATERIALIZED VIEW testdb.visits_mv TO testdb.agg_visits
AS SELECT
    StartDate,
    CounterID,
    sumState(Sign) AS Visits,
    uniqState(UserID) AS Users
FROM testdb.visits
GROUP BY StartDate, CounterID;

刺进数据到 testdb.visits 表:

INSERT INTO testdb.visits (StartDate, CounterID, Sign, UserID)
 VALUES (1667446031000, 1, 3, 4), (1667446031000, 1, 6, 3);

数据被一起刺进到testdb.revisits和testdb.agg_visits中。

履行比如 SELECT ... GROUP BY ...的句子查询物化视图test.mv_visits以获取聚合数据

SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM testdb.agg_visits
GROUP BY StartDate
ORDER BY StartDate;

输出：

StartDate          |Visits|Users|
-------------------+------+-----+
2022-11-03 11:27:11|     9|    2|

在testdb.revisits中增加别的2条记载，但这次测验对其间一条记载运用不同的时刻戳:

INSERT INTO testdb.visits (StartDate, CounterID, Sign, UserID)
 VALUES (1669446031000, 2, 5, 10), (1667446031000, 3, 7, 5);

再次查询，输出如下：

StartDate          |Visits|Users|
-------------------+------+-----+
2022-11-03 11:27:11|    16|    3|
2022-11-26 15:00:31|     5|    1|

参看链接

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/aggregatingmergetree