Doris之rollup上卷及物化视图

最新推荐文章于 2024-04-02 15:43:31 发布
最新推荐文章于 2024-04-02 15:43:31 发布
阅读量1.2k 收藏 2
点赞数
文章标签： mysql 数据库 Apache Doris
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45012397/article/details/130916841
版权

Rollup上卷

通过建表语句创建出来的表称为 Base 表（Base Table,基表）
在 Base 表之上，我们可以创建任意多个 ROLLUP 表。这些 ROLLUP 的数据是基于 Base 表产生的，并且在物理上是独立存储的。
Rollup表的好处：

  1. 和基表共用一个表名，doris会根据具体的查询逻辑选择合适的数据源(合适的表)来计算结果
  2. 对于基表中数据的增删改，rollup表会自动更新同步

1.Aggregate 模型中的 ROLLUP

查看之前建的表格：

mysql> desc ex_user all;

在这里插入图片描述
示例1：查看某个用户的总消费：

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;

在这里插入图片描述
再次查看该表得详细信息后发现，多了一个IndexName为rollup_cost_userid（这是我们自己取得rollUp 名字）
在这里插入图片描述

Doris 会自动命中这个 ROLLUP 表，从而只需扫描极少的数据量，即可完成这次聚合查询。

explain SELECT user_id, sum(cost) FROM ex_user GROUP BY user_id;

在这里插入图片描述

2.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');

里面的数据是这样的

mysql> select * from user;
+---------+----------+--------+------+------+-------------+------------------+---------------------+
| user_id | username | city   | age  | sex  | phone       | address          | register_time       |
+---------+----------+--------+------+------+-------------+------------------+---------------------+
| 10000   | lss      | 北京   |   20 |    0 | 12345678910 | 北京海淀区       | 2017-11-15 06:10:20 |
| 10000   | zss      | 北京   |   19 |    0 | 12345678910 | 北京朝阳区       | 2017-10-01 07:00:00 |
+---------+----------+--------+------+------+-------------+------------------+---------------------+

在unique模型中做rollup表，rollup的key必须延用base表中所有的key，不同的是value可以随意指定

alter table user add rollup rollup_username_id(username,user_id,age);

因此，unique模型中建立rollup表没有什么太多的意义。

3.Duplicate 模型中的 ROLLUP

ROLLUP 调整前缀索引（新增一套前缀索引）
因为建表时已经指定了列顺序，所以一个表只有一种前缀索引。这对于使用其他不能命中前缀索引的列作为条件进行的查询来说，效率上可能无法满足需求。因此，我们可以通过创建 ROLLUP 来人为的调整列顺序。
Base 表结构如下：

ColumnNameType
user_idBIGINT
ageINT
messageVARCHAR(100)
max_dwell_timeDATETIME
min_dwell_timeDATETIME

我们可以在此基础上创建一个 ROLLUP 表：

ColumnNameType
ageINT
user_idBIGINT
messageVARCHAR(100)
max_dwell_timeDATETIME
min_dwell_timeDATETIME

可以看到，ROLLUP 和 Base 表的列完全一样，只是将 user_id 和 age 的顺序调换了。那么当我们进行如下查询时：

SELECT * FROM table where age=20 and message LIKE "%error%";

会优先选择 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表

mysql> desc log_detail all;
+------------+---------------+------------+---------------+------+-------+---------+-------+---------+
| IndexName  | IndexKeysType | Field      | Type          | Null | Key   | Default | Extra | Visible |
+------------+---------------+------------+---------------+------+-------+---------+-------+---------+
| log_detail | DUP_KEYS      | timestamp  | DATETIME      | No   | true  | NULL    |       | true    |
|            |               | type       | INT           | No   | true  | NULL    |       | true    |
|            |               | error_code | INT           | Yes  | false | NULL    | NONE  | true    |
|            |               | error_msg  | VARCHAR(1024) | Yes  | false | NULL    | NONE  | true    |
|            |               | op_id      | BIGINT        | Yes  | false | NULL    | NONE  | true    |
|            |               | op_time    | DATETIME      | Yes  | false | NULL    | NONE  | true    |
|            |               |            |               |      |       |         |       |         |
| rollup_oec | DUP_KEYS      | op_id      | BIGINT        | Yes  | true  | NULL    |       | true    |
|            |               | error_code | INT           | Yes  | true  | NULL    |       | true    |
|            |               | timestamp  | DATETIME      | No   | true  | NULL    |       | true    |
|            |               | type       | INT           | No   | false | NULL    | NONE  | true    |
|            |               | error_msg  | VARCHAR(1024) | Yes  | false | NULL    | NONE  | true    |
|            |               | op_time    | DATETIME      | Yes  | false | NULL    | NONE  | true    |
|            |               |            |               |      |       |         |       |         |
| rollup_tec | DUP_KEYS      | type       | INT           | No   | true  | NULL    |       | true    |
|            |               | error_code | INT           | Yes  | true  | NULL    |       | true    |
|            |               | timestamp  | DATETIME      | No   | true  | NULL    |       | true    |
|            |               | error_msg  | VARCHAR(1024) | Yes  | false | NULL    | NONE  | true    |
|            |               | op_id      | BIGINT        | Yes  | false | NULL    | NONE  | true    |
|            |               | op_time    | DATETIME      | Yes  | false | NULL    | NONE  | true    |
+------------+---------------+------------+---------------+------+-------+---------+-------+---------+

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. 查询时，会自动选择最优物化视图

物化视图 与 Rollup

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

创建物化视图
语法：

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

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


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

案例：计算广告的 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;

总结：

  1. 在创建doris的物化视图中，同一个字段不能被使用两次,并且聚合函数后面必须跟字段名称(不能使用count(1)这样的聚合逻辑)
  2. doris在选择使用哪一个物化视图表的时候，按照维度上卷的原则，选距离查询维度最接近，并且指标可以复用的物化视图
  3. 一张基表可以创建多个物化视图(计算资源占用比较多)
今夕何夕9720
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Doris系列之高级功能-Rollup操作
Allen_人工智能与大数据
08-17 2196
Doris系列之高级功能-Rollup操作
Apache Doris 入门教程07：建表、Rollup和查询
sunjian286的专栏
07-07 1676
中已经介绍过 Doris 的前缀索引，即 Doris 会把 Base/Rollup 表中的前 36 个字节（有 varchar 类型则可能导致前缀索引不满 36 个字节，varchar 会截断前缀索引，并且最多使用 varchar 的 20 个字节）在底层存储引擎单独生成一份排序的稀疏索引数据(数据也是排序的，用索引定位，然后在数据中做二分查找)，然后在查询的时候会根据查询中的条件来匹配每个 Base/Rollup 的前缀索引，并且选择出匹配前缀索引最长的一个 Base/Rollup。
深入解析实时数仓Doris：Rollup上卷表与查询
热门推荐
码到三十五
04-02 1万+
在 Doris 中，我们将用户通过建表语句创建出来的表称为 Base 表（Base Table）。Base 表中保存着按用户建表语句指定的方式存储的基础数据。 在 Base 表之上，我们可以创建任意多个 ROLLUP 表。这些 ROLLUP 的数据是基于 Base 表产生的，并且在物理上是独立存储的。 ROLLUP 表的基本作用，在于在 Base 表的基础上，获得更粗粒度的聚合数据。
Doris之Rollup 与查询
qq_43688472的博客
01-30 1989
Doris之Rollup 与查询
【大数据 - Doris 实践】数据表的基本使用（五）：ROLLUP
书山有路，学海无涯。记录成长，追逐梦想
10-27 630
在 Doris 中，我们将用户通过建表语句创建出来的表称为 Base 表（Base Table）。Base 表中保存着按用户建表语句指定的方式存储的基础数据。在 Base 表之上，我们可以创建任意多个 ROLLUP 表。这些 ROLLUP 的数据是基于 Base 表产生的，并且在物理上是独立存储的。ROLLUP 表的基本作用，在于在 Base 表的基础上，获得更粗粒度的聚合数据。
doris---Rollup
Y521316的博客
05-27 1090
doris---Rollup
1-5+Doris物化视图、索引的典型应用案例.pdf
03-18
1-5+Doris物化视图、索引的典型应用案例
Doris应用指南Doris
最新发布
05-06
Doris应用指南Doris
doris 0.15版本 安装包
01-26
已对doris 0.15版本进行了编译，可以直接使用此包进行 分发，修改配置文件，启动 如果需要积分或者校验码，可以去百度网盘中下载，地址如下： 链接：https://pan.baidu.com/s/1BA3ZRcfjj4-tVVZNUIw9Bw 提取码：...
doris-flink-connector
03-25
官网没有提供下载编译好的 connector ，需要自己编译，我将编译好的上传，供大家下载。 flink 版本1.13.5； <scala.version>2.12 <flink.version>1.13.5 <libthrift.version>0.13.0 <arrow.version>5.0.0 ...
Doris基础使用手册.pdf
06-21
Doris基础使用手册.pdf
Doris之ROLLUP
程裕强的专栏
09-01 2435
Duplicate 数据模型：DorisDB中的用于存放明细数据的数据模型，建表可指定，数据不会被聚合。 Base 表：DorisDB 中通过 CREATE TABLE 命令创建出来的表。 Materialized Views 表：简称 MVs，物化视图。 https://cloud.baidu.com/doc/PALO/s/kksis5hci ROLLUP是物化视图的一个子集。 ...
Doris之物化视图
qq_43688472的博客
01-21 1113
Doris之物化视图
Doris--Rollup
qq_46893497的博客
12-03 61
上卷。
Doris进阶——Rollup和前缀索引
weixin_59812175的博客
06-17 974
Doris中Rollup和前缀索引
Doris系列6-Doris之rollup
只是甲的博客
01-05 2034
文章目录一. 基本概念二. Aggregate 和 Uniq 模型中的 ROLLUP2.1 测试数据准备2.2 获取每一天的销售额2.2.1 直接写sql2.2.2 先使用rollup聚合再查询三. ROLLUP 的几点说明参考: 一. 基本概念 在 Doris 中，我们将用户通过建表语句创建出来的表称为 Base 表（Base Table）。Base 表中保存着按用户建表语句指定的方式存储的基础数据。 在 Base 表之上，我们可以创建任意多个 ROLLUP 表。这些 ROLLUP 的数据是基于 Base
Doris ： ROLLUP 与 物化视图
悦上心灵的博客
05-16 1389
Apache Doris 为分析而生
Apache Doris的Rollup和前缀索引
Bulut0907
10-26 1922
目录1. Aggregate表(Uniq表同理)添加rollup2. Duplicate表添加rollup3. key和前缀索引的关系、查询命中前缀索引的规则3.1 key和前缀索引的关系3.2 查询命中前缀索引的规则 1. Aggregate表(Uniq表同理)添加rollup 添加rollup mysql> mysql> alter table table1 add rollup rollup_city(name, city_code, pv); Query OK, 0 rows af
Doris 上卷（Rollup）和前缀索引
shangjg3的博客
12-01 482
底层的数据存储，是按照各自建表语句中，AGGREGATE KEY、UNIQ KEY 和 DUPLICATE KEY 中指定的列进行排序存储的。用户可以在Base表的基础上，创建或删除ROLLUP，但是不能在查询中显式的指定查询某 ROLLUP。查询能否命中ROLLUP的一个必要条件（非充分条件）是，查询所涉及的所有列（包括 select list 和 where 中的查询条件列等）都存在于该ROLLUP的列中。3）当我们的查询条件，是前缀索引的前缀时，可以极大的加快查询速度。
doris删除视图
05-26
如果您想在Doris中删除一个视图，可以使用以下命令： ``` DROP VIEW view_name; ``` 其中，`view_name`是您想要删除的视图的名称。执行此命令后，Doris将删除该视图及其定义。请注意，此操作是不可逆的，因此请谨慎执行。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值