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 聚合，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/553792.html
標籤：其他
上一篇：花了億點點時間，寫了一個趕海和茶藝小程式：探索多重功能，開啟精彩互動之旅！
下一篇：返回列表