级联物化视图

此示例演示了如何创建物化视图，以及如何将第二个物化视图级联到第一个。在本页中，您将看到如何执行此操作，以及许多可能性和限制。通过使用第二个物化视图作为来源创建物化视图，可以解决不同的使用案例。

示例

我们将使用一个假数据集，其中包含一组域名每小时的浏览次数。

我们的目标

我们需要按每个域名的每月汇总数据，
我们还需要按每个域名的每年汇总数据。

您可以选择以下选项之一

编写查询，在 SELECT 请求期间读取和汇总数据
在数据导入时准备成新的格式
在数据导入时准备成特定的汇总。

使用物化视图准备数据将使您能够限制 ClickHouse 需要执行的数据量和计算量，从而使您的 SELECT 请求更快。

物化视图的源表

创建源表，因为我们的目标涉及报告汇总数据而不是单个行，我们可以解析它，将信息传递给物化视图，并丢弃实际传入的数据。这满足了我们的目标并节省了存储空间，因此我们将使用 Null 表引擎。

CREATE DATABASE IF NOT EXISTS analytics;

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

注意

您可以在 Null 表上创建物化视图。因此，写入表的数据最终会影响视图，但原始原始数据仍将被丢弃。

每月汇总表和物化视图

对于第一个物化视图，我们需要创建 目标 表，在此示例中，它将是 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)

将数据转发到目标表的物化视图将如下所示

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 表，这意味着数据流将是

数据进入 hourly_data 表。
ClickHouse 会将接收到的数据转发到第一个物化视图 monthly_aggregated_data 表，
最后，步骤 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

注意

在处理物化视图时，一个常见的误解是数据是从表中读取的。这不是 物化视图 的工作方式；转发的数据是插入的块，而不是表中的最终结果。

让我们假设在此示例中，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);

如果您 SELECT analytics.hourly_data 的内容，您将看到以下内容，因为表引擎是 Null，但数据已处理。

SELECT * FROM analytics.hourly_data

Ok.

0 rows in set. Elapsed: 0.002 sec.

我们已经使用了一个小数据集来确保我们能够遵循并与我们预期的结果进行比较，一旦您的流程在小数据集上正确，您就可以直接迁移到大量数据。

结果

如果您尝试通过选择 sumCountViews 字段来查询目标表，您将看到二进制表示形式（在某些终端中），因为该值不是存储为数字，而是存储为 AggregateFunction 类型。要获得汇总的最终结果，您应该使用 -Merge 后缀。

您可以使用此查询查看存储在 AggregateFunction 中的特殊字符

SELECT sumCountViews FROM analytics.monthly_aggregated_data

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

3 rows in set. Elapsed: 0.003 sec.

相反，让我们尝试使用 Merge 后缀来获取 sumCountViews 值

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

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

1 row in set. Elapsed: 0.003 sec.

在 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

现在我们可以检查物化视图是否回答了我们定义的目标。

现在，我们已将数据存储在目标表 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 │
└────────────┴────────────────┴───────────────┘

3 rows in set. Elapsed: 0.004 sec.

按每个域名每年汇总的数据

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.

将多个源表合并到单个目标表

物化视图还可以用于将多个源表合并到同一个目标表中。这对于创建类似于 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)
;

然后，使用合并的指标集创建 目标 表

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)

创建两个指向同一个 目标 表的物化视图。您无需显式包含缺失的列

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         ---<<<--- if you omit this, it will be the same 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    ---<<<--- if you omit this, it will be the same 0
FROM
    analytics.clicks
GROUP BY
    toDate(event_time) AS on_date,
    domain_name
;

现在，当您插入值时，这些值将被汇总到 目标 表中相应的列

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

目标 表中组合的展示次数和点击次数

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 │
└────────────┴────────────────┴─────────────┴────────┘

3 rows in set. Elapsed: 0.018 sec.

级联物化视图

物化视图的源表​

每月汇总表和物化视图​

每年汇总表和物化视图​

示例数据​

结果​

将多个源表合并到单个目标表​

物化视图的源表

每月汇总表和物化视图

每年汇总表和物化视图

示例数据

结果

将多个源表合并到单个目标表