Rollup
ROLLUP 在多維分析中是“上卷”的意思，即將數(shù)據(jù)按某種指定的粒度進(jìn)行進(jìn)一步聚合。
通過(guò)建表語(yǔ)句創(chuàng)建出來(lái)的表稱為 Base 表（Base Table,基表）
在 Base 表之上，我們可以創(chuàng)建任意多個(gè) ROLLUP 表。這些 ROLLUP 的數(shù)據(jù)是基于 Base 表產(chǎn)生的，并且在物理上是獨(dú)立存儲(chǔ)的。
Rollup表的好處：
- 和基表共用一個(gè)表名，doris會(huì)根據(jù)具體的查詢邏輯選擇合適的數(shù)據(jù)源(合適的表)來(lái)計(jì)算結(jié)果
- 對(duì)于基表中數(shù)據(jù)的增刪改，rollup表會(huì)自動(dòng)更新同步
Aggregate 模型中的 ROLLUP
添加一個(gè)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有沒(méi)有執(zhí)行完成
SHOW ALTER TABLE ROLLUP;
在查詢時(shí), Doris 會(huì)自動(dòng)命中這個(gè) ROLLUP 表，從而只需掃描極少的數(shù)據(jù)量，即可完成這次聚合查詢。
explain SELECT user_id, sum(cost) FROM ex_user GROUP BY user_id;
獲取不同城市，不同年齡段用戶的總消費(fèi)、最長(zhǎng)和最短頁(yè)面駐留時(shí)間
alter table ex_user add rollup rollup_city(city,age,cost,max_dwell_time,min_dwell_time);
-- 當(dāng)創(chuàng)建好了立即去查看得時(shí)候就會(huì)發(fā)現(xiàn)，他還沒(méi)有開(kāi)始
SHOW ALTER TABLE ROLLUP;
然后過(guò)會(huì)再去查詢得時(shí)候，他就完成了，看他的狀態(tài)即可
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 "用戶注冊(cè)時(shí)間" )
UNIQUE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;
--插入語(yǔ)句
insert into test.user values\
(10000,'zss','北京',18,0,12345678910,'北京朝陽(yáng)區(qū) ','2017-10-01 07:00:00'),\
(10000,'zss','北京',18,0,12345678910,'北京朝陽(yáng)區(qū) ','2017-10-01 08:00:00'),\
(10001,'lss','北京',20,0,12345678910,'北京海淀區(qū)','2017-11-15 06:10:20');
-- 在unique模型中做rollup表，rollup的key必須延用base表中所有的key，不同的是value可以隨意指定
-- 所以說(shuō)，unique模型中建立rollup表沒(méi)有什么太多的意義
alter table user add rollup rollup_username_id(username,user_id,age);
Duplicate 模型中的 ROLLUP
因?yàn)?Duplicate 模型沒(méi)有聚合的語(yǔ)意。所以該模型中的 ROLLUP，已經(jīng)失去了“上卷” 這一層含義。而僅僅是作為調(diào)整列順序，以命中前綴索引的作用。下面詳細(xì)介紹前綴索引，以及如何使用 ROLLUP 改變前綴索引，以獲得更好的查詢效率。
ROLLUP 調(diào)整前綴索引（新增一套前綴索引）
因?yàn)榻ū頃r(shí)已經(jīng)指定了列順序，所以一個(gè)表只有一種前綴索引。這對(duì)于使用其他不能命中前綴索引的列作為條件進(jìn)行的查詢來(lái)說(shuō)，效率上可能無(wú)法滿足需求。因此，我們可以通過(guò)創(chuàng)建 ROLLUP 來(lái)人為的調(diào)整列順序。
-- 針對(duì)log_detail這張基表添加兩個(gè)rollup表
-- 按照type 和error_code 進(jìn)行建前綴索引
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 進(jìn)行建前綴索引
alter table log_detail add rollup rollup_oec(op_id,error_code,timestamp,type,error_msg,op_time);
-- 查看基表和rollup表
desc log_detail all;
ROLLUP使用說(shuō)明
- ROLLUP 是附屬于 Base 表的,用戶可以在 Base 表的基礎(chǔ)上，創(chuàng)建或刪除 ROLLUP，但是不能在查詢中顯式的指定查詢某 ROLLUP。是否命中 ROLLUP 完全由 Doris 系統(tǒng)自動(dòng)決定
- ROLLUP 的數(shù)據(jù)是獨(dú)立物理存儲(chǔ)的。因此，創(chuàng)建的 ROLLUP 越多，占用的磁盤空間也就越大。同時(shí)對(duì)導(dǎo)入速度也會(huì)有影響，但是不會(huì)降低查詢效率（只會(huì)更好）。
- ROLLUP 的數(shù)據(jù)更新與 Base 表是完全同步的。用戶無(wú)需關(guān)心這個(gè)問(wèn)題。
- 在聚合模型中，ROLLUP 中列的聚合類型，與 Base 表完全相同。在創(chuàng)建 ROLLUP 無(wú)需指定，也不能修改。
- 可以通過(guò) EXPLAIN your_sql; 命令獲得查詢執(zhí)行計(jì)劃，在執(zhí)行計(jì)劃中，查看是否命中 ROLLUP。
- 可以通過(guò) DESC tbl_name ALL; 語(yǔ)句顯示 Base 表和所有已創(chuàng)建完成的 ROLLUP
物化視圖
就是查詢結(jié)果預(yù)先存儲(chǔ)起來(lái)的特殊的表。物化視圖的出現(xiàn)主要是為了滿足用戶，既能對(duì)原始明細(xì)數(shù)據(jù)的任意維度分析，也能快速的對(duì)固定維度進(jìn)行分析查詢
優(yōu)勢(shì)
- 可以復(fù)用預(yù)計(jì)算的結(jié)果來(lái)提高查詢效率 ==> 空間換時(shí)間
- 自動(dòng)實(shí)時(shí)的維護(hù)物化視圖表中的結(jié)果數(shù)據(jù)，無(wú)需額外人工成本(自動(dòng)維護(hù)會(huì)有計(jì)算資源的開(kāi)銷)
- 查詢時(shí)，會(huì)自動(dòng)選擇最優(yōu)物化視圖
物化視圖 VS Rollup
? 明細(xì)模型表下，rollup和物化視圖的差別：
物化視圖：都可以實(shí)現(xiàn)預(yù)聚合，新增一套前綴索引
rollup：對(duì)于明細(xì)模型，新增一套前綴索引
? 聚合模型下，功能一致
創(chuàng)建物化視圖
CREATE MATERIALIZED VIEW [MV name] as
[query] -- sql邏輯
--[MV name]：物化視圖的名稱
--[query]：查詢條件，基于base表創(chuàng)建物化視圖的邏輯
-- 物化視圖創(chuàng)建成功后，用戶的查詢不需要發(fā)生任何改變，也就是還是查詢的 base 表。Doris 會(huì)根據(jù)當(dāng)前查詢的語(yǔ)句去自動(dòng)選擇一個(gè)最優(yōu)的物化視圖，從物化視圖中讀取數(shù)據(jù)并計(jì)算。
-- 用戶可以通過(guò) EXPLAIN 命令來(lái)檢查當(dāng)前查詢是否使用了物化視圖。
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");
-- 插入數(shù)據(jù)
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);
-- 創(chuàng)建一個(gè)物化視圖
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;
--針對(duì)上述場(chǎng)景做一個(gè)物化視圖
create materialized view store_amt as
select store_id, sum(sale_amt) as sum_amount
from sales_records
group by store_id;
-- 檢查物化視圖是否構(gòu)建完成(物化視圖的創(chuàng)建是個(gè)異步的過(guò)程)
show alter table materialized view from 庫(kù)名 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;
-- 刪除物化視圖語(yǔ)法
-- 語(yǔ)法：
DROP MATERIALIZED VIEW 物化視圖名 on base_table_name;
--示例：
drop materialized view store_amt on sales_records;
練習(xí)
計(jì)算廣告的 pv、uv文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-463519.html
pv:page view，頁(yè)面瀏覽量或點(diǎn)擊量
uv:unique view，通過(guò)互聯(lián)網(wǎng)訪問(wèn)、瀏覽這個(gè)網(wǎng)頁(yè)的自然人文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-463519.html
-- 創(chuàng)建表
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
-- 插入數(shù)據(jù)
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);
-- 創(chuàng)建物化視圖
-- 在doris的物化視圖中，一個(gè)字段不能用兩次,并且聚合函數(shù)后面必須跟字段名稱
-- count(distinct) 不能使用。需要用bitmap_union來(lái)代替
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) 聚合的結(jié)果和 bitmap_union_count 聚合的結(jié)果是完全一致的。而 bitmap_union_count 等于 bitmap_union 的結(jié)果求 count，所以如果查詢中涉及到count(distinct) 則通過(guò)創(chuàng)建帶 bitmap_union 聚合的物化視圖方可加快查詢。因?yàn)楸旧?user_id 是一個(gè) INT 類型，所以在 Doris 中需要先將字段通過(guò)函數(shù) to_bitmap 轉(zhuǎn)換為 bitmap 類型然后才可以進(jìn)行 bitmap_union 聚合。
到了這里，關(guān)于Doris(三) -- Rollup和物化視圖的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
