Doris(三) -- Rollup和物化视图 - ToB企服应用市场:ToB评测及商务社交产业平台

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;

复制代码

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模型示例表
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);

复制代码

-- 针对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;

复制代码

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;

复制代码

-- 创建表
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 聚合。

复制代码