万字长文讲解什么是 Doris 物化视图

学术 2024-11-09 23:02 内蒙古

物化视图是既包含计算逻辑也包含数据的实体。它不同于视图，因为视图仅包含计算逻辑，本身不存储数据。

物化视图的使用场景

物化视图根据 SQL 定义计算并存储数据，且根据策略进行周期性或实时性更新。物化视图可直接查询，也可以将查询透明改写。它可用于以下几个场景：

1.1 查询加速

在决策支持系统中，如 BI 报表、Ad-Hoc 查询等，这类分析型查询通常包含聚合操作，可能还涉及多表连接。由于计算此类查询结果较为消耗资源、响应时间可能长达分钟级，且业务场景往往要求秒级响应，可以构建物化视图，对常见查询进行加速。

1.2 轻量化 ETL（数据建模）

在数据分层场景中，可以通过嵌套物化视图来构建 DWD 和 DWM 层，利用物化视图的调度刷新能力。

1.3 湖仓一体

针对多种外部数据源，可以将这些数据源所使用的表进行物化视图构建，以此来节省从外部表导入到内部表的成本，并且加速查询过程。

物化视图的分类

按照数据时效性分类：同步 vs 异步

同步物化视图需要与基表的数据保持强一致性。
异步物化视图与基表的数据保持最终一致性，可能会有一定的延迟。它通常用于对数据时效性要求不高的场景，一般使用 T+1 或小时级别的数据来构建物化视图。如果时效性要求高，则考虑使用同步物化视图。

目前，同步物化视图不支持直接查询，而异步物化视图支持直接查询。

按照支持透明改写的 SQL 模式分类：单表 vs 多表

物化视图的定义 SQL 可以包含单表查询，也可以包含多表查询。从使用表的数量角度出发，可以划分物化视图为单表物化视图或多表物化视图。

对于异步物化视图，可以使用单表或多表。
对于同步物化视图，只能使用单表。

按照物化视图刷新分类：全量 vs 分区增量 vs 实时

对于异步物化视图

全量刷新：计算物化视图定义 SQL 的所有数据。

分区增量刷新：当物化视图基表的分区数据发生变化时，识别出物化视图对应变化的分区，并仅刷新这些分区，从而实现分区增量刷新，而无需刷新整个物化视图。

对于同步物化视图

可以理解为实时更新，保持与基表的数据一致。

这或许是一个对你有用的开源项目，data-warehouse-learning 项目是一套基于 MySQL + Kafka + Hadoop + Hive + Dolphinscheduler + Doris + Seatunnel + Paimon + Hudi + Iceberg + Flink + Dinky + DataRT + SuperSet 实现的实时离线数仓（数据湖）系统，以大家最熟悉的电商业务为切入点，详细讲述并实现了数据产生、同步、数据建模、数仓（数据湖）建设、数据服务、BI报表展示等数据全链路处理流程。
https://gitee.com/wzylzjtn/data-warehouse-learning
https://github.com/Mrkuhuo/data-warehouse-learning
https://bigdatacircle.top/
项目演示：

同步物化视图

3.1 什么是同步物化视图

同步物化视图是将预先计算（根据定义好的 SELECT 语句）的数据集，存储在 Doris 中的一个特殊的表。Doris 会自动维护同步物化视图的数据，无论是新的导入还是删除操作，都能保证 Base 表和物化视图表的数据同步更新、保持一致后，相关命令才会结束，无需任何额外的人工维护成本。查询时，Doris 会自动匹配到最优的物化视图，并直接从物化视图中读取数据。

3.2 适用场景

加速耗时的聚合运算
查询需要匹配不同的前缀索引
通过预先过滤减少需要扫描的数据量
通过预先完成复杂的表达式计算来加速查询

3.3 局限性

同步物化视图只支持针对单个表的 SELECT 语句，支持 WHERE、GROUP BY、ORDER BY 等子句，但不支持 JOIN、HAVING、LIMIT 子句和 LATERAL VIEW。
与异步物化视图不同，不能直接查询同步物化视图。
SELECT 列表中，不能包含自增列，不能包含常量，不能有重复表达式，也不支持窗口函数。
如果 SELECT 列表包含聚合函数，则聚合函数必须是根表达式（不支持 sum(a) + 1，支持 sum(a + 1)），且聚合函数之后不能有其他非聚合函数表达式（例如，SELECT x, sum(a) 可以，而 SELECT sum(a), x 不行）。
如果删除语句的条件列在物化视图中存在，则不能进行删除操作。如果确实需要删除数据，则需要先将物化视图删除，然后才能删除数据。
单表上过多的物化视图会影响导入的效率：导入数据时，物化视图和 Base 表的数据是同步更新的。如果一张表的物化视图表过多，可能会导致导入速度变慢，这就像单次导入需要同时导入多张表的数据一样。
物化视图针对 Unique Key 数据模型时，只能改变列的顺序，不能起到聚合的作用。因此，在 Unique Key 模型上不能通过创建物化视图的方式对数据进行粗粒度的聚合操作。

Doris 系统提供了一整套针对物化视图的 DDL 语法，包括创建、查看和删除。下面通过一个示例来展示如何使用物化视图加速聚合计算。假设用户有一张销售记录明细表，该表存储了每个交易的交易 ID、销售员、售卖门店、销售时间以及金额。建表语句和插入数据语句如下：

-- 创建一个 test_dbcreate database test_db;use test_db;
-- 创建表create table sales_records(    record_id int,     seller_id int,     store_id int,     sale_date date,     sale_amt bigint) distributed by hash(record_id) properties("replication_num" = "1");
-- 插入数据insert into sales_records values(1,1,1,'2020-02-02',1);

3.4 创建物化视图

如果用户经常需要分析不同门店的销售量，则可以为 sales_records 表创建一个物化视图，该视图以售卖门店分组，并对相同售卖门店的销售额进行求和。创建语句如下：

create materialized view store_amt as select store_id, sum(sale_amt) from sales_records group by store_id;

3.5 检查物化视图是否创建完成

由于创建物化视图是一个异步操作，用户在提交创建物化视图任务后，需要异步地通过命令检查物化视图是否构建完成。命令如下：

show alter table materialized view from test_db;

该命令的结果将显示该数据库的所有创建物化视图的任务。结果示例如下：

+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+| JobId  | TableName     | CreateTime          | FinishTime          | BaseIndexName | RollupIndexName | RollupId | TransactionId | State    | Msg  | Progress | Timeout |+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+| 494349 | sales_records | 2020-07-30 20:04:56 | 2020-07-30 20:04:57 | sales_records | store_amt       | 494350   | 133107        | FINISHED |      | NULL     | 2592000 |+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+

其中，TableName 指的是物化视图的数据来源表，RollupIndexName 指的是物化视图的名称。比较重要的指标是 State。当创建物化视图任务的 State 变为 FINISHED 时，就说明这个物化视图已经创建成功了。这意味着，在执行查询时有可能自动匹配到这张物化视图。

3.6 取消创建物化视图

如果创建物化视图的后台异步任务还未结束，可以通过以下命令取消任务：

cancel alter table materialized view from test_db.sales_records;

如果物化视图已经创建完毕，则无法通过该命令取消创建，但可以通过删除命令来删除物化视图。

3.7 查看物化视图的表结构

可以通过以下命令查看目标表上创建的所有物化视图及其表结构：

desc sales_records all;

该命令的结果如下：

+---------------+---------------+---------------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+| IndexName     | IndexKeysType | Field               | Type   | InternalType | Null | Key   | Default | Extra | Visible | DefineExpr | WhereClause |+---------------+---------------+---------------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+| sales_records | DUP_KEYS      | record_id           | INT    | INT          | Yes  | true  | NULL    |       | true    |            |             ||               |               | seller_id           | INT    | INT          | Yes  | true  | NULL    |       | true    |            |             ||               |               | store_id            | INT    | INT          | Yes  | true  | NULL    |       | true    |            |             ||               |               | sale_date           | DATE   | DATEV2       | Yes  | false | NULL    | NONE  | true    |            |             ||               |               | sale_amt            | BIGINT | BIGINT       | Yes  | false | NULL    | NONE  | true    |            |             ||               |               |                     |        |              |      |       |         |       |         |            |             || store_amt     | AGG_KEYS      | mv_store_id         | INT    | INT          | Yes  | true  | NULL    |       | true    | `store_id` |             ||               |               | mva_SUM__`sale_amt` | BIGINT | BIGINT       | Yes  | false | NULL    | SUM   | true    | `sale_amt` |             |+---------------+---------------+---------------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+

可以看到，sales_records有一个名叫store_amt的物化视图，这个物化视图就是前序步骤创建的。

3.8 查看物化视图的创建语句

可以通过以下命令查看物化视图的创建语句：

show create materialized view store_amt on sales_records;

输出如下：

+---------------+-----------+------------------------------------------------------------------------------------------------------------+| TableName     | ViewName  | CreateStmt                                                                                                 |+---------------+-----------+------------------------------------------------------------------------------------------------------------+| sales_records | store_amt | create materialized view store_amt as select store_id, sum(sale_amt) from sales_records group by store_id |+---------------+-----------+------------------------------------------------------------------------------------------------------------+

3.9 查询物化视图

当物化视图创建完成后，用户在查询不同门店的销售量时，Doris 会直接从刚才创建的物化视图store_amt中读取聚合好的数据，从而提升查询效率。用户的查询依旧指定查询sales_records表，比如：

select store_id, sum(sale_amt) from sales_records group by store_id;

上面的查询就能自动匹配到store_amt。用户可以通过下面的命令，检验当前查询是否匹配到了合适的物化视图。

explain select store_id, sum(sale_amt) from sales_records group by store_id;

结果如下：

+-----------------------------------------------------------------------------------------------+| Explain String(Nereids Planner)                                                               |+-----------------------------------------------------------------------------------------------+| PLAN FRAGMENT 0                                                                               ||   OUTPUT EXPRS:                                                                               ||     store_id[#11]                                                                             ||     sum(sale_amt)[#12]                                                                        ||   PARTITION: HASH_PARTITIONED: mv_store_id[#7]                                                ||                                                                                               ||   HAS_COLO_PLAN_NODE: false                                                                   ||                                                                                               ||   VRESULT SINK                                                                                ||      MYSQL_PROTOCAL                                                                           ||                                                                                               ||   3:VAGGREGATE (merge finalize)(145)                                                          ||   |  output: sum(partial_sum(mva_SUM__sale_amt)[#8])[#10]                                     ||   |  group by: mv_store_id[#7]                                                                ||   |  sortByGroupKey:false                                                                     ||   |  cardinality=1                                                                            ||   |  final projections: mv_store_id[#9], sum(mva_SUM__sale_amt)[#10]                          ||   |  final project output tuple id: 4                                                         ||   |  distribute expr lists: mv_store_id[#7]                                                   ||   |                                                                                           ||   2:VEXCHANGE                                                                                 ||      offset: 0                                                                                ||      distribute expr lists:                                                                   ||                                                                                               || PLAN FRAGMENT 1                                                                               ||                                                                                               ||   PARTITION: HASH_PARTITIONED: record_id[#2]                                                  ||                                                                                               ||   HAS_COLO_PLAN_NODE: false                                                                   ||                                                                                               ||   STREAM DATA SINK                                                                            ||     EXCHANGE ID: 02                                                                           ||     HASH_PARTITIONED: mv_store_id[#7]                                                         ||                                                                                               ||   1:VAGGREGATE (update serialize)(139)                                                        ||   |  STREAMING                                                                                ||   |  output: partial_sum(mva_SUM__sale_amt[#1])[#8]                                           ||   |  group by: mv_store_id[#0]                                                                ||   |  sortByGroupKey:false                                                                     ||   |  cardinality=1                                                                            ||   |  distribute expr lists:                                                                   ||   |                                                                                           ||   0:VOlapScanNode(136)                                                                        ||      TABLE: test_db.sales_records(store_amt), PREAGGREGATION: ON ||      partitions=1/1 (sales_records)                                                           ||      tablets=10/10, tabletList=494505,494507,494509 ...                                       ||      cardinality=1, avgRowSize=0.0, numNodes=1                                                ||      pushAggOp=NONE                                                                           ||                                                                                               ||                                                                                               || Statistics                                                                                    ||  planed with unknown column statistics                                                        |+-----------------------------------------------------------------------------------------------+

从底部VOlapScanNode的test_db.sales_records(store_amt)可以表明，该查询命中了store_amt这个物化视图。值得注意的是，如果表中没有数据，那么可能不会命中物化视图。

3.10 删除物化视图

drop materialized view store_amt on sales_records;

异步物化视图

4.1 异步物化视图构建

物化视图创建分区有两种方式：自定义分区和依赖基表的分区自动创建分区

4.1.1 自定义分区

创建物化视图时，如果不指定分区信息，物化视图将默认创建一个分区，所有数据都存放在这个分区中。

4.1.2 依赖基表进行分区

物化视图可以通过多个基表 JOIN 关联创建，并可以选择追随其中一个基表进行分区（建议选择事实表）。

例如，基表t1和t2的建表语句分别如下：

CREATE TABLE `t1` (  `user_id` LARGEINT NOT NULL,  `o_date` DATE NOT NULL,  `num` SMALLINT NOT NULL) ENGINE=OLAPCOMMENT 'OLAP'PARTITION BY RANGE(`o_date`)(PARTITION p20170101 VALUES [('2017-01-01'), ('2017-01-02')),PARTITION p20170102 VALUES [('2017-01-02'), ('2017-01-03')),PARTITION p20170201 VALUES [('2017-02-01'), ('2017-02-02')))DISTRIBUTED BY HASH(`user_id`) BUCKETS 2PROPERTIES ('replication_num' = '1') ;CREATE TABLE `t2` (  `user_id` LARGEINT NOT NULL,  `age` SMALLINT NOT NULL) ENGINE=OLAPPARTITION BY LIST(`age`)(    PARTITION `p1` VALUES IN ('1'),    PARTITION `p2` VALUES IN ('2'))DISTRIBUTED BY HASH(`user_id`) BUCKETS 2PROPERTIES ('replication_num' = '1') ;

若物化视图的建表语句如下：

CREATE MATERIALIZED VIEW mv1BUILD DEFERRED REFRESH AUTO ON MANUALpartition by(`order_date`)DISTRIBUTED BY RANDOM BUCKETS 2PROPERTIES ('replication_num' = '1')ASSELECT t1.o_date as order_date, t1.user_id as user_id, t1.num, t2.age FROM t1 join t2 on t1.user_id=t2.user_id;

那么物化视图mv1将和t1一样，有三个分区：

[('2017-01-01'), ('2017-01-02'))
[('2017-01-02'), ('2017-01-03'))
[('2017-02-01'), ('2017-02-02'))

若物化视图的建表语句如下：

CREATE MATERIALIZED VIEW mv2BUILD DEFERRED REFRESH AUTO ON MANUALpartition by(`age`)DISTRIBUTED BY RANDOM BUCKETS 2PROPERTIES ('replication_num' = '1')ASSELECT t1.o_date as order_date, t1.user_id as user_id, t1.num, t2.age FROM t1 join t2 on t1.user_id=t2.user_id;

那么物化视图mv2将和t2一样，有两个分区：

('1')
('2')

在依赖基表分区创建物化视图时，不同场景需要合理设计不同的分区策略，下面将举例说明多列分区、部分分区与分区上卷三种情况。

4.1.2.1 . 基表有多列分区

目前仅支持 Hive 外表有多列分区。Hive 外表有很多多级分区的情况，例如一级分区按照日期，二级分区按照区域。物化视图可以选择 Hive 的某一级分区列作为物化视图的分区列。

例如，Hive 的建表语句如下：

CREATE TABLE hive1 (`k1` int)PARTITIONED BY (`year` int,`region` string)STORED AS ORC;
alter table hive1 add if not existspartition(year=2020,region="bj")partition(year=2020,region="sh")partition(year=2021,region="bj")partition(year=2021,region="sh")partition(year=2022,region="bj")partition(year=2022,region="sh")

当物化视图的创建语句如下时，物化视图mv_hive将有三个分区：('2020')，('2021')，('2022')

CREATE MATERIALIZED VIEW mv_hiveBUILD DEFERRED REFRESH AUTO ON MANUALpartition by(`year`)DISTRIBUTED BY RANDOM BUCKETS 2PROPERTIES ('replication_num' = '1')ASSELECT k1,year,region FROM hive1;

当物化视图的建表语句如下时，那么物化视图mv_hive2将有如下两个分区：('bj')，('sh')：

CREATE MATERIALIZED VIEW mv_hive2BUILD DEFERRED REFRESH AUTO ON MANUALpartition by(`region`)DISTRIBUTED BY RANDOM BUCKETS 2PROPERTIES ('replication_num' = '1')ASSELECT k1,year,region FROM hive1;

4.1.2.2 . 仅使用基表部分分区

有些基表有很多分区，但是物化视图只关注最近一段时间的“热”数据，那么可以使用此功能。

基表的建表语句如下：

CREATE TABLE t1 (    `k1` INT,    `k2` DATE NOT NULL) ENGINE=OLAPDUPLICATE KEY(`k1`)COMMENT 'OLAP'PARTITION BY range(`k2`)(PARTITION p26 VALUES [("2024-03-26"),("2024-03-27")),PARTITION p27 VALUES [("2024-03-27"),("2024-03-28")),PARTITION p28 VALUES [("2024-03-28"),("2024-03-29")))DISTRIBUTED BY HASH(`k1`) BUCKETS 2PROPERTIES ('replication_num' = '1');

物化视图的创建语句如以下举例，代表物化视图只关注最近一天的数据。若当前时间为 2024-03-28 xx:xx:xx，这样物化视图会仅有一个分区 [("2024-03-28"),("2024-03-29")]：

CREATE MATERIALIZED VIEW mv1BUILD DEFERRED REFRESH AUTO ON MANUALpartition by(`k2`)DISTRIBUTED BY RANDOM BUCKETS 2PROPERTIES ('replication_num' = '1','partition_sync_limit'='1','partition_sync_time_unit'='DAY')ASSELECT * FROM t1;

若时间又过了一天，当前时间为 2024-03-29 xx:xx:xx，t1则会新增一个分区 [("2024-03-29"),("2024-03-30")]，若此时刷新物化视图，刷新完成后，物化视图会仅有一个分区 [("2024-03-29"),("2024-03-30")]。

此外，分区字段是字符串类型时，可以设置物化视图属性 partition_date_format，例如 %Y-%m-%d 。

4.1.2.3 . 分区上卷

当基表数据经过聚合处理后，各分区的数据量可能会显著减少。在这种情况下，可以采用分区上卷策略，以降低物化视图的分区数量。

List 分区

需要注意的是，Hive 中的分区对应于 Doris 中的 list 分区。

假设基表的建表语句如下：

CREATE TABLE `t1` (`k1` INT NOT NULL,`k2` DATE NOT NULL) ENGINE=OLAPDUPLICATE KEY(`k1`)COMMENT 'OLAP'PARTITION BY list(`k2`)(PARTITION p_20200101 VALUES IN ("2020-01-01"),PARTITION p_20200102 VALUES IN ("2020-01-02"),PARTITION p_20200201 VALUES IN ("2020-02-01"))DISTRIBUTED BY HASH(`k1`) BUCKETS 2PROPERTIES ('replication_num' = '1') ;

若物化视图的创建语句如下，则该物化视图将包含两个分区：("2020-01-01","2020-01-02") 和 ("2020-02-01")

CREATE MATERIALIZED VIEW mv1    BUILD DEFERRED REFRESH AUTO ON MANUAL    partition by (date_trunc(`k2`,'month'))    DISTRIBUTED BY RANDOM BUCKETS 2    PROPERTIES (    'replication_num' = '1'    )    AS    SELECT * FROM t1;

若物化视图的创建语句如下，则该物化视图将只包含一个分区：("2020-01-01","2020-01-02","2020-02-01")

CREATE MATERIALIZED VIEW mv1    BUILD DEFERRED REFRESH AUTO ON MANUAL    partition by (date_trunc(`k2`,'year'))    DISTRIBUTED BY RANDOM BUCKETS 2    PROPERTIES (    'replication_num' = '1'    )    AS    SELECT * FROM t1;

Range 分区

假设基表的建表语句如下：

CREATE TABLE `t1` (`k1` LARGEINT NOT NULL,`k2` DATE NOT NULL) ENGINE=OLAPDUPLICATE KEY(`k1`)COMMENT 'OLAP'PARTITION BY range(`k2`)(PARTITION p_20200101 VALUES [("2020-01-01"),("2020-01-02")),PARTITION p_20200102 VALUES [("2020-01-02"),("2020-01-03")),PARTITION p_20200201 VALUES [("2020-02-01"),("2020-02-02")))DISTRIBUTED BY HASH(`k1`) BUCKETS 2PROPERTIES ('replication_num' = '1') ;

若物化视图的创建语句如下，则该物化视图将包含两个分区：[("2020-01-01","2020-02-01")] 和 [("2020-02-01","2020-03-01")]

CREATE MATERIALIZED VIEW mv1    BUILD DEFERRED REFRESH AUTO ON MANUAL    partition by (date_trunc(`k2`,'month'))    DISTRIBUTED BY RANDOM BUCKETS 2    PROPERTIES (    'replication_num' = '1'    )    AS    SELECT * FROM t1;

若物化视图的创建语句如下，则该物化视图将只包含一个分区：[("2020-01-01","2021-01-01")]

CREATE MATERIALIZED VIEW mv1    BUILD DEFERRED REFRESH AUTO ON MANUAL    partition by (date_trunc(`k2`,'year'))    DISTRIBUTED BY RANDOM BUCKETS 2    PROPERTIES (    'replication_num' = '1'    )    AS    SELECT * FROM t1;

此外，如果分区字段为字符串类型，可以通过设置物化视图的 partition_date_format 属性来指定日期格式，例如 '%Y-%m-%d'。

4.2 异步物化视图刷新

物化视图是按照分区为单位进行刷新的。如果物化视图没有指定分区，那么每次都刷新物化视图的默认分区，即刷新物化视图的全部数据。

物化视图有三种触发刷新机制：

4.2.1 手动触发

用户通过 SQL 语句触发物化视图的刷新，目前有三种策略：

不关心具体刷新哪些分区，要求刷新完成后，物化视图的数据和基表保持同步。

REFRESH MATERIALIZED VIEW mvName AUTO;

不管物化视图现存哪些数据，刷新物化视图的所有分区。

REFRESH MATERIALIZED VIEW mvName COMPLETE;

不管物化视图现存哪些数据，只刷新指定的分区。

REFRESH MATERIALIZED VIEW mvName partitions(partitionName1,partitionName2);

4.2.2 定时触发

通过物化视图的创建语句指定间隔多久刷新一次数据

如果物化视图的创建语句如下，要求全量刷新 (REFRESH COMPLETE)，那么物化视图每 10 小时刷新一次，并且刷新物化视图的所有分区。

CREATE MATERIALIZED VIEW mv1REFRESH COMPLETE ON SCHEDULE EVERY 10 hourpartition by(`xxx`)ASselect ...;

如果物化视图的创建语句如下，要求自动计算需要刷新的分区 (REFRESH AUTO)，那么物化视图每 10 小时刷新一次（从 2.1.3 版本开始能自动计算 Hive 需要刷新的分区）。

CREATE MATERIALIZED VIEW mv1REFRESH AUTO ON SCHEDULE EVERY 10 hourpartition by(`xxx`)ASselect ...;

4.2.3 自动触发

基表数据发生变更后，自动触发相关物化视图刷新，刷新的分区范围与“定时触发”一致。

如果物化视图的创建语句如下，那么当 t1 的数据发生变化时，会自动触发物化视图的刷新。

CREATE MATERIALIZED VIEW mv1REFRESH ON COMMITpartition by(`xxx`)ASselect ... from t1;

如果喜欢请点个在看分享给身边的朋友

http://mp.weixin.qq.com/s?__biz=MzA3NTM0MTYyMQ==&mid=2448347018&idx=1&sn=cf3afed11538e34bed9e4339a1210395

志明与数据

关注与分享数据那些事儿｜数据治理｜数据管理｜数据架构｜大数据｜数据中台｜数据仓库｜数据湖｜数据分析｜数据要素｜数据资源｜数据资产｜数据入表｜数字化转型｜DataOps｜DAMA｜CDGA｜CDGP｜CDMP｜DGBOK｜CDGE｜PMP