Doris中物化视图-案例（二十）

参考上篇： Doris中的物化视图-查询

案例一

1）创建一个 Base 表

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,2,3,'2020-02-02',10);

2）基于这个 Base 表的数据提交一个创建物化视图的任务

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

3）检查物化视图是否构建完成

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

SHOW ALTER TABLE MATERIALIZED VIEW FROM test_db; (Version 0.13)

查看 Base 表的所有物化视图

desc sales_records all;

4）检验当前查询是否匹配到了合适的物化视图

EXPLAIN SELECT store_id, sum(sale_amt) FROM sales_records GROUP BY store_id;

5）删除物化视图语法

DROP MATERIALIZED VIEW 物化视图名 on Base 表名;

案例二：计算广告的 pv、uv

假设用户的原始广告点击数据存储在 Doris，那么针对广告 PV, UV 查询就可以通过创建 bitmap_union 的物化视图来提升查询速度。

1）创建 base 表

create table advertiser_view_record(
time date,
advertiser varchar(10),
channel varchar(10),
user_id int
)
distributed by hash(time)
properties("replication_num" = "1");

插入数据

insert into advertiser_view_record values('2020-02-02','a','app',123);

2）创建物化视图

create materialized view advertiser_uv as 
select advertiser, channel, bitmap_union(to_bitmap(user_id)) 
from advertiser_view_record 
group by advertiser, channel;

在 Doris 中，count(distinct) 聚合的结果和 bitmap_union_count 聚合的结果是完全一致的。而 bitmap_union_count 等于 bitmap_union 的结果求 count，所以如果查询中涉及到count(distinct) 则通过创建带 bitmap_union 聚合的物化视图方可加快查询。

因为本身 user_id 是一个 INT 类型，所以在 Doris 中需要先将字段通过函数 to_bitmap 转换为 bitmap 类型然后才可以进行 bitmap_union 聚合。

3）查询自动匹配

SELECT advertiser, channel, count(distinct user_id) 
FROM advertiser_view_record 
GROUP BY advertiser, channel;

会自动转换成

SELECT advertiser, channel, bitmap_union_count(to_bitmap(user_id)) 
FROM advertiser_uv 
GROUP BY advertiser, channel;

4）检验是否匹配到物化视图

explain SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel;

在 EXPLAIN 的结果中，首先可以看到 OlapScanNode 的 rollup 属性值为 advertiser_uv

也就是说，查询会直接扫描物化视图的数据。说明匹配成功。

其次对于 user_id 字段求 count(distinct)被改写为求 bitmap_union_count(to_bitmap)。也就是通过 bitmap 的方式来达到精确去重的效果。

案例三

用户的原始表有（k1, k2, k3）三列。其中 k1, k2 为前缀索引列。这时候如果用户查询条件中包含 where k1=1 and k2=2 就能通过索引加速查询。

但是有些情况下，用户的过滤条件无法匹配到前缀索引，比如 where k3=3。则无法通过索引提升查询速度。

创建以 k3 作为第一列的物化视图就可以解决这个问题。

1）查询

explain select record_id,seller_id,store_id from sales_records where store_id=3;

2）创建物化视图

create materialized view mv_1 as 
select 
 store_id,
 record_id,
 seller_id,
 sale_date,
 sale_amt
from sales_records;

通过上面语法创建完成后，物化视图中既保留了完整的明细数据，且物化视图的前缀索引为 store_id 列。

3）查看表结构

desc sales_records all;

4）查询匹配

explain select record_id,seller_id,store_id from sales_records 
where store_id=3;

这时候查询就会直接从刚才创建的 mv_1 物化视图中读取数据。物化视图对 store_id是存在前缀索引的，查询效率也会提升。