Doris(四) -- Rollup和物化视图
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 2
properties("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_amount
from 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 uv
from ad_view_record
group 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
select
dt,ad_page,channel,
count(refer_page) as pv,
bitmap_union(to_bitmap(user_id)) as uv_bitmap
from 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 聚合。
