世界聚焦：Doris(三) -- Rollup和物化视图

2023-05-29 20:04:37 来源：博客园

【资料图】

Rollup

ROLLUP 在多维分析中是“上卷”的意思，即将数据按某种指定的粒度进行进一步聚合。通过建表语句创建出来的表称为 Base 表（Base Table,基表）在 Base 表之上，我们可以创建任意多个 ROLLUP 表。这些 ROLLUP 的数据是基于 Base 表产生的，并且在物理上是独立存储的。Rollup表的好处：

和基表共用一个表名，doris会根据具体的查询逻辑选择合适的数据源(合适的表)来计算结果
对于基表中数据的增删改，rollup表会自动更新同步

Aggregate 模型中的 ROLLUP

添加一个roll up

alter table aggregate表名 add rollup "rollup表的表名" (user_id,city,date,cost);alter table ex_user add rollup rollup_ucd_cost(user_id,city,date,cost);alter table ex_user add rollup rollup_u_cost(user_id,cost);alter table ex_user add rollup rollup_cd_cost(city,date,cost);alter table ex_user drop rollup rollup_u_cost;alter table ex_user drop rollup rollup_cd_cost;--如果是replace聚合类型得value，需要指定所有得key-- alter table ex_user add rollup rollup_cd_visit(city,date,last_visit_date);-- ERROR 1105 (HY000): errCode = 2, detailMessage = Rollup should contains -- all keys if there is a REPLACE value--添加完成之后可以show一下，看看底层的rollup有没有执行完成SHOW ALTER TABLE ROLLUP;

在查询时, Doris 会自动命中这个 ROLLUP 表，从而只需扫描极少的数据量，即可完成这次聚合查询。

explain SELECT user_id, sum(cost) FROM ex_user GROUP BY user_id;

获取不同城市，不同年龄段用户的总消费、最长和最短页面驻留时间

alter table ex_user add rollup rollup_city(city,age,cost,max_dwell_time,min_dwell_time);  -- 当创建好了立即去查看得时候就会发现，他还没有开始 SHOW ALTER TABLE ROLLUP; 然后过会再去查询得时候，他就完成了，看他的状态即可

Unique 模型中的 ROLLUP

-- unique模型示例表drop table if exists test.user;CREATE TABLE IF NOT EXISTS test.user( `user_id` LARGEINT NOT NULL COMMENT "用户 id", `username` VARCHAR(50) NOT NULL COMMENT "用户昵称", `city` VARCHAR(20) COMMENT "用户所在城市", `age` SMALLINT COMMENT "用户年龄", `sex` TINYINT COMMENT "用户性别", `phone` LARGEINT COMMENT "用户电话", `address` VARCHAR(500) COMMENT "用户地址", `register_time` DATETIME COMMENT "用户注册时间" )UNIQUE KEY(`user_id`, `username`)DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;--插入语句insert into test.user values\(10000,"zss","北京",18,0,12345678910,"北京朝阳区 ","2017-10-01 07:00:00"),\(10000,"zss","北京",18,0,12345678910,"北京朝阳区 ","2017-10-01 08:00:00"),\(10001,"lss","北京",20,0,12345678910,"北京海淀区","2017-11-15 06:10:20");-- 在unique模型中做rollup表，rollup的key必须延用base表中所有的key，不同的是value可以随意指定-- 所以说，unique模型中建立rollup表没有什么太多的意义alter table user add rollup rollup_username_id(username,user_id,age);

Duplicate 模型中的 ROLLUP

因为 Duplicate 模型没有聚合的语意。所以该模型中的 ROLLUP，已经失去了“上卷” 这一层含义。而仅仅是作为调整列顺序，以命中前缀索引的作用。下面详细介绍前缀索引，以及如何使用 ROLLUP 改变前缀索引，以获得更好的查询效率。ROLLUP 调整前缀索引（新增一套前缀索引）因为建表时已经指定了列顺序，所以一个表只有一种前缀索引。这对于使用其他不能命中前缀索引的列作为条件进行的查询来说，效率上可能无法满足需求。因此，我们可以通过创建 ROLLUP 来人为的调整列顺序。

-- 针对log_detail这张基表添加两个rollup表-- 按照type 和error_code 进行建前缀索引alter table log_detail add rollup rollup_tec(type,error_code,timestamp,error_msg,op_id,op_time);alter table log_detail drop rolluprollup_tec-- 按照op_id和error_code 进行建前缀索引alter table log_detail add rollup rollup_oec(op_id,error_code,timestamp,type,error_msg,op_time);-- 查看基表和rollup表desc log_detail all;

ROLLUP使用说明
ROLLUP 是附属于 Base 表的,用户可以在 Base 表的基础上，创建或删除 ROLLUP，但是不能在查询中显式的指定查询某 ROLLUP。是否命中 ROLLUP 完全由 Doris 系统自动决定
ROLLUP 的数据是独立物理存储的。因此，创建的 ROLLUP 越多，占用的磁盘空间也就越大。同时对导入速度也会有影响，但是不会降低查询效率（只会更好）。
ROLLUP 的数据更新与 Base 表是完全同步的。用户无需关心这个问题。
在聚合模型中，ROLLUP 中列的聚合类型，与 Base 表完全相同。在创建 ROLLUP 无需指定，也不能修改。
可以通过 EXPLAIN your_sql; 命令获得查询执行计划，在执行计划中，查看是否命中 ROLLUP。
可以通过 DESC tbl_name ALL; 语句显示 Base 表和所有已创建完成的 ROLLUP

物化视图

就是查询结果预先存储起来的特殊的表。物化视图的出现主要是为了满足用户，既能对原始明细数据的任意维度分析，也能快速的对固定维度进行分析查询

优势

可以复用预计算的结果来提高查询效率 ==> 空间换时间
自动实时的维护物化视图表中的结果数据，无需额外人工成本(自动维护会有计算资源的开销)
查询时，会自动选择最优物化视图

物化视图 VS Rollup

•明细模型表下，rollup和物化视图的差别：物化视图：都可以实现预聚合，新增一套前缀索引rollup：对于明细模型，新增一套前缀索引•聚合模型下，功能一致

创建物化视图

CREATE MATERIALIZED VIEW [MV name] as [query]  -- sql逻辑--[MV name]：物化视图的名称--[query]：查询条件，基于base表创建物化视图的逻辑-- 物化视图创建成功后，用户的查询不需要发生任何改变，也就是还是查询的 base 表。Doris 会根据当前查询的语句去自动选择一个最优的物化视图，从物化视图中读取数据并计算。-- 用户可以通过 EXPLAIN 命令来检查当前查询是否使用了物化视图。create table sales_records(record_id int, seller_id int, store_id int, sale_date date, sale_amt bigint) duplicate key (record_id,seller_id,store_id,sale_date)distributed by hash(record_id) buckets 2properties("replication_num" = "1");-- 插入数据insert into sales_records values \(1,1,1,"2022-02-02",100),\(2,2,1,"2022-02-02",200),\(3,3,2,"2022-02-02",300),\(4,3,2,"2022-02-02",200),\(5,2,1,"2022-02-02",100),\(6,4,2,"2022-02-02",200),\(7,7,3,"2022-02-02",300),\(8,2,1,"2022-02-02",400),\(9,9,4,"2022-02-02",100);-- 创建一个物化视图select store_id, sum(sale_amt)  from sales_records  group by store_id; CREATE MATERIALIZED VIEW store_id_sale_amonut as select store_id, sum(sale_amt)  from sales_records  group by store_id;CREATE MATERIALIZED VIEW store_amt as select store_id, sum(sale_amt)  as sum_amountfrom sales_records  group by store_id; --针对上述场景做一个物化视图create materialized view store_amt as  select store_id, sum(sale_amt) as sum_amount from sales_records  group by store_id; -- 检查物化视图是否构建完成(物化视图的创建是个异步的过程)show alter table materialized view from 库名  order by CreateTime desc limit 1;show alter table materialized view from test order by CreateTime desc limit 1;-- 查看 Base 表的所有物化视图 desc sales_records all;--查询并查看是否命中刚才我们建的物化视图EXPLAIN SELECT store_id, sum(sale_amt) FROM sales_records GROUP BY store_id;-- 删除物化视图语法-- 语法：DROP MATERIALIZED VIEW 物化视图名 on base_table_name; --示例：drop materialized view store_amt on sales_records;

练习

计算广告的 pv、uv

pv:page view，页面浏览量或点击量uv:unique view，通过互联网访问、浏览这个网页的自然人

-- 创建表drop table if exists ad_view_record;create table ad_view_record( dt date,  ad_page varchar(10),  channel varchar(10), refer_page varchar(10), user_id int )  distributed by hash(dt)  properties("replication_num" = "1");select dt,ad_page,channel,count(ad_page) as pv,  count(distinct user_id ) as uvfrom ad_view_recordgroup by dt,ad_page,channel-- 插入数据insert into ad_view_record values \("2020-02-02","a","app","/home",1),\("2020-02-02","a","web","/home",1),\("2020-02-02","a","app","/addbag",2),\("2020-02-02","b","app","/home",1),\("2020-02-02","b","web","/home",1),\("2020-02-02","b","app","/addbag",2),\("2020-02-02","b","app","/home",3),\("2020-02-02","b","web","/home",3),\("2020-02-02","c","app","/order",1),\("2020-02-02","c","app","/home",1),\("2020-02-03","c","web","/home",1),\("2020-02-03","c","app","/order",4),\("2020-02-03","c","app","/home",5),\("2020-02-03","c","web","/home",6),\("2020-02-03","d","app","/addbag",2),\("2020-02-03","d","app","/home",2),\("2020-02-03","d","web","/home",3),\("2020-02-03","d","app","/addbag",4),\("2020-02-03","d","app","/home",5),\("2020-02-03","d","web","/addbag",6),\("2020-02-03","d","app","/home",5),\("2020-02-03","d","web","/home",4);-- 创建物化视图-- 在doris的物化视图中，一个字段不能用两次,并且聚合函数后面必须跟字段名称-- count(distinct) 不能使用。需要用bitmap_union来代替create materialized view tpc_pv_uv as  selectdt,ad_page,channel,count(refer_page) as pv,bitmap_union(to_bitmap(user_id)) as uv_bitmapfrom ad_view_record group by dt,ad_page,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 聚合。

关键词：