Doris基础学习系列（五十三）doris多表异步物化视图

在前面的文章《Doris基础学习系列（二十七）doris的物化视图》我们介绍了Doris表的物化视图，但是由于那个时候比较早，doris仅支持单表物化视图，即大家理解的同步物化视图。从doris2.1.5版本开始，doris支持多表的物化视图了，即大家理解的异步物化视图。

多表物化视图与单表物化视图的区别有：

1、多表的物化视图主要是多个表之间通过join连接查询的新数据集。
2、多表物化视图需要有分区。

基于上诉两个核心点，join可以暂时不说了，分区这块需要重点说明一下：

1、多表物化视图的分区主要是依赖于基表。
2、多表物化视图的分区可以选择任意一个基表的分区作为当前物化视图的分区。
3、多表物化视图是根据分区来进行异步刷新数据的。

所以下面我们来演示一下：

doris分区示例

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

第一张表：

CREATE TABLE `users` 
(  
`user_id` bigint NOT NULL,  
`register_date` DATE NOT NULL,  
`user_info` varchar NOT NULL
) ENGINE=OLAP COMMENT 'OLAP'
PARTITION BY RANGE(`register_date`)(
PARTITION p2022 VALUES [('2022-01-01'), ('2023-01-01')),
PARTITION p2023 VALUES [('2023-01-01'), ('2024-01-01')),
PARTITION p2024 VALUES [('2024-01-01'), ('2025-01-01'))
)DISTRIBUTED BY HASH(`user_id`) BUCKETS 2
PROPERTIES ('replication_num' = '1') ;

第二张表：

CREATE TABLE `shop` (
`shop_id` bigint NOT NULL,
`user_id` bigint NOT NULL,  
`shop_type` SMALLINT NOT NULL
) ENGINE=OLAP
PARTITION BY LIST(`shop_type`)(    
PARTITION `p1` VALUES IN ('1'),    
PARTITION `p2` VALUES IN ('2'))
DISTRIBUTED BY HASH(`shop_id`) BUCKETS 2
PROPERTIES ('replication_num' = '1') ;

那么此时想要创建一个关于两张表的物化视图查询语句是：

select users.user_id,users.register_date,users.user_info,shop.shop_type from users join shop on users.user_id = shop.user_id;

根据上面的查询语句创建物化视图的话，我们需要选择一个基表的分区作为物化视图的分区，如果是选择users表的分区的话，创建语句如下：

CREATE MATERIALIZED VIEW users_shop 
BUILD DEFERRED REFRESH AUTO ON MANUAL partition by(`register_date`)
DISTRIBUTED BY RANDOM BUCKETS 2 PROPERTIES ('replication_num' = '1')
AS
select users.user_id,users.register_date,users.user_info,shop.shop_type from users join shop on users.user_id = shop.user_id;

看到上面的核心了吗？第二行的partition by(`register_date`)这个分区是users的分区，所以当前创建的users_shop的分区就是以users基表的分区为主，users分区有几个分区，那么users_shop这个物化视图就会有多少个分区。

如果想要使用shop的分区怎么办呢？很简单把分区字段修改为shop的分区字段即可，例如：

CREATE MATERIALIZED VIEW users_shop1 
BUILD DEFERRED REFRESH AUTO ON MANUAL partition by(`shop_type`)
DISTRIBUTED BY RANDOM BUCKETS 2 PROPERTIES ('replication_num' = '1')
AS
select users.user_id,users.register_date,users.user_info,shop.shop_type from users join shop on users.user_id = shop.user_id;

此时users_shop1的物化视图的分区就以shop表的分区为主，shop有多少个分区，users_shop1的物化视图就会有多少个分区。

Hive示例

特别说明一下，在hive中会有一级分区，二级分区的情况，那么在doris中可以选择hive的某一级分区来作为物化视图的分区即可，例如：

hive的创建语句：

CREATE TABLE hive1 (
`k1` int)
PARTITIONED BY (`year` int,`region` string) STORED AS ORC;

那么我们可以选择year作为分区，也可以选用region作为分区，选择任意一个即可：

CREATE MATERIALIZED VIEW mv_hive
BUILD DEFERRED REFRESH AUTO ON MANUAL partition by(`year`)
DISTRIBUTED BY RANDOM BUCKETS 2 PROPERTIES ('replication_num' = '1')
AS
SELECT k1,year,region FROM hive1;

限制doris多表物化视图的分区

还有一种场景，例如每天需要统计前一天的相关数据，这里的统计是从多张表汇聚的，那此时需求只有当天的前一天的数据有效，咱们就没有必要把所有的历史数据都保存下来，只需要在物化视图保存近一天的数据即可，那这种怎么弄呢？其实就是添加属性。示例如下：

假设原表的建表语句是：

CREATE TABLE `orders` 
(    
`orders_id` INT,    
`orders_cts` DATE NOT NULL
) ENGINE=OLAP 
DUPLICATE KEY(`orders_id`) COMMENT 'OLAP'
PARTITION BY range(`orders_cts`)(
PARTITION p11 VALUES [("2024-12-11"),("2024-12-12")),
PARTITION p12 VALUES [("2024-12-12"),("2024-12-13")),
PARTITION p13 VALUES [("2024-12-13"),("2024-12-14")))
DISTRIBUTED BY HASH(`orders_id`) BUCKETS 2
PROPERTIES ('replication_num' = '1');

这张订单表按每天来保存分区的，那么我们创建多表异步物化视图的时候，分区选择orders_cts，然后限制一下分区只有1个即可，示例如下：

CREATE MATERIALIZED VIEW orders_mv1
BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by(`orders_cts`) 
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
'replication_num' = '1',
'partition_sync_limit'='1',
'partition_sync_time_unit'='DAY')
AS
SELECT * FROM orders;

看到下面我们使用

'partition_sync_limit'='1',
'partition_sync_time_unit'='DAY'

这两个参数来限制了吗？如果想要保存2天的数据，那么limit设置为2即可。

备注：

1、这里大家可以看到多表异步物化视图不仅支持多表，还支持单表。

2、多表物化视图在实际中需要刷新数据，详见《doris多表异步物化视图刷新数据》。

3、如果想要查询有哪些异步物化视图，详见《doris传多表异步物化视图》。

4、多表物化视图其实也是一张新的存储表，和单表物化视图不同的是多表物化视图在tables中是可以看到的：

5、多表物化视图是可以直接通过select * from ${多表异步物化视图} 查询的，例如：