doris---物化视图及pv、uv案例实现网站首页 其他

doris---物化视图及pv、uv案例实现

咚动咚 2024-07-01 11:59:47

简介doris---物化视图及pv、uv案例实现

物化视图

就是查询结果预先存储起来的特殊的表。物化视图的出现主要是为了满足用户，既能对原始明细数据的任意维度分析，也能快速的对固定维度进行分析查询

优势

1. 可以复用预计算的结果来提高查询效率  ==> 空间换时间
2. 自动实时的维护物化视图表中的结果数据，无需额外人工成本(自动维护会有计算资源的开销)
3. 查询时，会自动选择最优物化视图

物化视图 VS Rollup

- 明细模型表下，rollup和物化视图的差别：
物化视图：都可以实现预聚合，新增一套前缀索引
rollup：对于明细模型，新增一套前缀索引
- 聚合模型下，功能一致

创建物化视图

CREATE MATERIALIZED VIEW [MV name] as 
[query]  -- sql逻辑

--[MV name]：雾化视图的名称
--[query]：查询条件，基于base表创建雾化视图的逻辑


取消正在创建的物化视图
CANCEL ALTER MATERIALIZED VIEW FROM db_name.table_name

注意:
物化视图创建成功后，用户的查询不需要发生任何改变，也就是还是查询的 base 表。Doris 会根据当前查询的语句去自动选择一个最优的物化视图，从物化视图中读取数据并计算
用户可以通过 EXPLAIN 命令来检查当前查询是否使用了物化视图。

案例一：计算广告的 pv、uv

用户有一张点击广告的明细数据表
需求：针对用户点击计广告明细数据的表，算每天，每个页面，每个渠道的 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);

创建物化视图

-- 怎么去计算pv，uv
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;

-- 1.物化视图中，不能够使用两个相同的字段
-- 2.在增量聚合里面，不能够使用count(distinct) ==> bitmap_union
-- 3.count(字段)

create materialized view dpc_pv_uv as 
select
dt,ad_page,channel,
-- refer_page 没有null的情况
count(refer_page) as pv,
-- doris的物化视图中，不支持count(distint) ==> bitmap_union
-- count(distinct user_id) as uv
bitmap_union(to_bitmap(user_id)) uv_bitmap
from ad_view_record 
group by dt,ad_page,channel;

//1. count(必须加字段名)   不能写count(1)
//2.同一个字段在物化视图的sql逻辑中不能出现两次
//3. count(distinct) 不能使用。需要用bitmap_union来代替

create materialized view tpc_pv_uv as  
select
dt,ad_page,channel,
count(refer_page) as pv,
-- refer_page 不能为null
-- count(user_id) as pv
-- count(1) as pv,
bitmap_union(to_bitmap(user_id)) as uv_bitmap
--count(distinct user_id) as uv
from ad_view_record 
group by dt,ad_page,channel;
--结论：在doris的物化视图中，一个字段不能用两次,并且聚合函数后面必须跟字段名称

在 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 聚合。查询自动匹配

explain 
select
dt,ad_page,channel,
count(refer_page) as pv,
count(distinct user_id) as uv
from ad_view_record
group by dt,ad_page,channel;

会自动转换成

explain 
select
dt,ad_page,channel,
count(1) as pv,
bitmap_union_count(to_bitmap(user_id)) as uv
from ad_view_record
group by dt,ad_page,channel;

这个sql用的是哪张表呢？

explain 
select
dt,ad_page,
count(refer_page) as pv,
count(distinct user_id) as uv
from ad_view_record
group by dt,ad_page;

TABLE: ad_view_record_1(tpc_pv_uv), PREAGGREGATION: ON    
-- 很显然命中的是tpc_pv_uv 这个物化视图

当然，我们还可以根据日期和页面的维度再去创建一张物化视图

create materialized view tp_pv_uv as  
select
dt,ad_page,
count(refer_page) as pv,
bitmap_union(to_bitmap(user_id)) as uv
from ad_view_record
group by dt,ad_page;

再去执行上面的sql，显然命中的就是tp_pv_uv这个物化视图

explain 
select
dt,ad_page,
count(refer_page) as pv,
count(distinct user_id) as uv
from ad_view_record
group by dt,ad_page;

-- TABLE: ad_view_record_1(tp_pv_uv), PREAGGREGATION: ON 


explain 
select
dt,
count(refer_page) as pv,
count(distinct user_id) as uv
from ad_view_record
group by dt;