Doris(三) -- Rollup和物化视图

Rollup

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

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

Aggregate 模型中的 ROLLUP

添加一个roll up

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

复制代码

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

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

复制代码

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

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

复制代码

Unique 模型中的 ROLLUP

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

复制代码

Duplicate 模型中的 ROLLUP

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

1. -- 针对log_detail这张基表添加两个rollup表
2. -- 按照type 和error_code 进行建前缀索引
3. alter table log_detail add rollup rollup_tec(type,error_code,timestamp,error_msg,op_id,op_time);
4. alter table log_detail drop rolluprollup_tec
6. -- 按照op_id和error_code 进行建前缀索引
7. alter table log_detail add rollup rollup_oec(op_id,error_code,timestamp,type,error_msg,op_time);
9. -- 查看基表和rollup表
10. 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：对于明细模型，新增一套前缀索引
•        聚合模型下，功能一致
创建物化视图

1. CREATE MATERIALIZED VIEW [MV name] as
2. [query]  -- sql逻辑
4. --[MV name]：物化视图的名称
5. --[query]：查询条件，基于base表创建物化视图的逻辑
6. -- 物化视图创建成功后，用户的查询不需要发生任何改变，也就是还是查询的 base 表。Doris 会根据当前查询的语句去自动选择一个最优的物化视图，从物化视图中读取数据并计算。
7. -- 用户可以通过 EXPLAIN 命令来检查当前查询是否使用了物化视图。
9. create table sales_records(
10. record_id int,
11. seller_id int,
12. store_id int,
13. sale_date date,
14. sale_amt bigint)
15. duplicate key (record_id,seller_id,store_id,sale_date)
16. distributed by hash(record_id) buckets 2
17. properties("replication_num" = "1");
19. -- 插入数据
20. insert into sales_records values \
21. (1,1,1,'2022-02-02',100),\
22. (2,2,1,'2022-02-02',200),\
23. (3,3,2,'2022-02-02',300),\
24. (4,3,2,'2022-02-02',200),\
25. (5,2,1,'2022-02-02',100),\
26. (6,4,2,'2022-02-02',200),\
27. (7,7,3,'2022-02-02',300),\
28. (8,2,1,'2022-02-02',400),\
29. (9,9,4,'2022-02-02',100);
31. -- 创建一个物化视图
32. select store_id, sum(sale_amt)  
33. from sales_records  
34. group by store_id;
36. CREATE MATERIALIZED VIEW store_id_sale_amonut as
37. select store_id, sum(sale_amt)  
38. from sales_records  
39. group by store_id;
41. CREATE MATERIALIZED VIEW store_amt as
42. select store_id, sum(sale_amt)  as sum_amount
43. from sales_records  
44. group by store_id;
46. --针对上述场景做一个物化视图
47. create materialized view store_amt as  
48. select store_id, sum(sale_amt) as sum_amount
49. from sales_records  
50. group by store_id;
52. -- 检查物化视图是否构建完成(物化视图的创建是个异步的过程)
53. show alter table materialized view from 库名  order by CreateTime desc limit 1;
55. show alter table materialized view from test order by CreateTime desc limit 1;
57. -- 查看 Base 表的所有物化视图
58. desc sales_records all;
60. --查询并查看是否命中刚才我们建的物化视图
61. EXPLAIN SELECT store_id, sum(sale_amt) FROM sales_records GROUP BY store_id;
64. -- 删除物化视图语法
65. -- 语法：
66. DROP MATERIALIZED VIEW 物化视图名 on base_table_name;
68. --示例：
69. drop materialized view store_amt on sales_records;

复制代码

练习

计算广告的 pv、uv

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

1. -- 创建表
2. drop table if exists ad_view_record;
3. create table ad_view_record(
4. dt date,  
5. ad_page varchar(10),  
6. channel varchar(10),
7. refer_page varchar(10),
8. user_id int
9. )  
10. distributed by hash(dt)  
11. properties("replication_num" = "1");
14. select
15. dt,ad_page,channel,
16. count(ad_page) as pv,  
17. count(distinct user_id ) as uv
18. from ad_view_record
19. group by dt,ad_page,channel
22. -- 插入数据
23. insert into ad_view_record values \
24. ('2020-02-02','a','app','/home',1),\
25. ('2020-02-02','a','web','/home',1),\
26. ('2020-02-02','a','app','/addbag',2),\
27. ('2020-02-02','b','app','/home',1),\
28. ('2020-02-02','b','web','/home',1),\
29. ('2020-02-02','b','app','/addbag',2),\
30. ('2020-02-02','b','app','/home',3),\
31. ('2020-02-02','b','web','/home',3),\
32. ('2020-02-02','c','app','/order',1),\
33. ('2020-02-02','c','app','/home',1),\
34. ('2020-02-03','c','web','/home',1),\
35. ('2020-02-03','c','app','/order',4),\
36. ('2020-02-03','c','app','/home',5),\
37. ('2020-02-03','c','web','/home',6),\
38. ('2020-02-03','d','app','/addbag',2),\
39. ('2020-02-03','d','app','/home',2),\
40. ('2020-02-03','d','web','/home',3),\
41. ('2020-02-03','d','app','/addbag',4),\
42. ('2020-02-03','d','app','/home',5),\
43. ('2020-02-03','d','web','/addbag',6),\
44. ('2020-02-03','d','app','/home',5),\
45. ('2020-02-03','d','web','/home',4);
47. -- 创建物化视图
48. -- 在doris的物化视图中，一个字段不能用两次,并且聚合函数后面必须跟字段名称
49. -- count(distinct) 不能使用。需要用bitmap_union来代替
50. create materialized view tpc_pv_uv as  
51. select
52. dt,ad_page,channel,
53. count(refer_page) as pv,
54. bitmap_union(to_bitmap(user_id)) as uv_bitmap
55. from ad_view_record
56. group by dt,ad_page,channel;
58. -- 在 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 聚合。

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！