首页 > 其他分享 >Doris(三) -- Rollup和物化视图

Doris(三) -- Rollup和物化视图

时间：2023-05-29 19:55:04浏览次数：43  
标签：02 -- Rollup rollup 视图 ROLLUP id

Rollup

ROLLUP 在多维分析中是“上卷”的意思，即将数据按某种指定的粒度进行进一步聚合。
通过建表语句创建出来的表称为 Base 表（Base Table,基表）
在 Base 表之上，我们可以创建任意多个 ROLLUP 表。这些 ROLLUP 的数据是基于 Base 表产生的，并且在物理上是独立存储的。
Rollup表的好处：

  1. 和基表共用一个表名，doris会根据具体的查询逻辑选择合适的数据源(合适的表)来计算结果
  2. 对于基表中数据的增删改，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使用说明

  1. ROLLUP 是附属于 Base 表的,用户可以在 Base 表的基础上，创建或删除 ROLLUP，但是不能在查询中显式的指定查询某 ROLLUP。是否命中 ROLLUP 完全由 Doris 系统自动决定
  2. ROLLUP 的数据是独立物理存储的。因此，创建的 ROLLUP 越多，占用的磁盘空间也就越大。同时对导入速度也会有影响，但是不会降低查询效率（只会更好）。
  3. ROLLUP 的数据更新与 Base 表是完全同步的。用户无需关心这个问题。
  4. 在聚合模型中，ROLLUP 中列的聚合类型，与 Base 表完全相同。在创建 ROLLUP 无需指定，也不能修改。
  5. 可以通过 EXPLAIN your_sql; 命令获得查询执行计划，在执行计划中，查看是否命中 ROLLUP。
  6. 可以通过 DESC tbl_name ALL; 语句显示 Base 表和所有已创建完成的 ROLLUP

物化视图

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

优势

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

物化视图 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 聚合。

标签：02,--,Rollup,rollup,视图,ROLLUP,id
From： https://www.cnblogs.com/paopaoT/p/17441495.html

相关文章

  • < Python全景系列-8 > Python超薄感知，超强保护：异常处理的绝佳实践
    欢迎来到我们的系列博客《Python全景系列》！在这个系列中，我们将带领你从Python的基础知识开始，一步步深入到高级话题，帮助你掌握这门强大而灵活的编程语法。无论你是编程新手，还是有一定基础的开发者，这个系列都将提供你需要的知识和技能。**欢迎来到系列第八篇，异常处理的深入探讨。......
  • 一种神秘的均摊方法
    UOJ191Unknown你需要维护一个向量序列，支持如下操作：在末尾加入一个向量\((u,v)\)。删除末尾的向量。询问\([l,r]\)内的向量与\((x,y)\)叉积的最大值。\(n,m\le5e5\)。这个东西我们首先一眼用李超树或者维护凸包来做全局询问最大值的子问题。考虑怎么把\([l,r]\)......
  • Redis+分布式+秒杀
    聊一下MySQL关于mysql关系型数据库的一些分析：1、从性能上：如果我们碰到需要执行耗时特别久，并且执行结果不是很频繁变动的SQL语句，我们就没有必要每次都去查询数据库，因为每次操作数据库都很耗时。2、从并发上：在大并发的情况下（比如618秒杀活动，你敢让千万级的请求直接打到数据库上吗......
  • MYSQL复习
    --创建数据库CREATEDATABASEdatabase_nameON[PRIMARY](NAME=file_name,FILENAME='os_file_name',[SIZE=size,][MAXSIZE=max_size|UNLIMTED,][FILEGROWTH=growth_increment])LOGON[PRIMARY](NAME=logical_file_name,FILENAME='os_f......
  • 游戏物体
    游戏物体1.物体的标签和层级标签可以方便查找物体。层级可以运用在一些操作上，比如Camera中Inspector中的CullingMask上，可以通过勾选掉一些层级来屏蔽显示一些物体2.摄像机组件Camera>Projection下的persepective表示透视，透视模式下可以通过Fieldinview调镜头的角度；另一个O......
  • 「Ynoi2011」成都七中
    「Ynoi2011」成都七中题意：询问\(([l,r],x)\)，表示将树中编号在\([l,r]\)内的所有节点保留，求\(x\)所在连通块中颜色种类数可以转化为从\(x\)出发且只经过节点范围在\([l,r]\)的路径上的颜色种类数，是路径问题且多次询问，所以可以考虑点分树但是可以发现，一个节点的答案可......
  • 2023盘古石决赛 物联网 Robot
    其实是一道非常简单的IoT分析题，甚至没有分析的成分，但是比赛过程中出的意外太多了，思路偏了，导致没能出题，而赛后复盘一下就明白了，完全是个简单题，所以写个博客警醒一下自己。个人感觉这道题完全算不上物联网的题，靠搜就可以做出来，只是当时做出来第一道题之后，以为要靠解包才能做，加上st......
  • 物体组件
    物体组件1.组件的基本操作代码类名后面跟的MonoBehaviour使脚本能够挂到物体的组件中。组件在Inspector视图中，可以通过最下面的AddComponent添加组件，通过组件点击组件名左边的勾选符号打开或关闭组件，通过组件名右边的三个点下面的RemoveComponent移除组件。2.通过脚本操作组......
  • eSpeek实现中文文字转语音功能
      最近业务上面有个文字转语音的想法，搜了下espeak可以实现这部分功能，搞下来试试效果!一、源码包下载  1、下载pa_stable_v190700_20210406.tgz   http://www.portaudio.com/download.html   2、下载espeak-1.48.04-source.zip   http://espeak.source......
  • anaconda安装
    目录一、前言Anaconda是一个开源的Python发行版本，用来管理Python相关的包，安装Anaconda可以很方便的切换不同的环境，使用不同的深度学习框架开发项目，本文将详细介绍Anaconda的安装。二、实验环境Windows10三、Anaconda安装注：在Anaconda安装的过程中，比较容易出错的环节是环境......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99