
Doris 三种模型(学习笔记)
Aggregate聚合键模型
是相同key的数据进行自动聚合的表模型。表中的列按照是否设置了 AggregationType,分为 Key(维度列)和 Value(指标列),没有设置 AggregationType 的称为 Key,设置了 AggregationType 的称为 Value。当我们导入数据时,对于 Key 列相同的行会聚合成一行,而 Value 列会按照设置的AggregationType 进行聚合。
AggregationType 目前有以下四种聚合方式:
聚合方式 | 描述 |
---|---|
SUM | 求和,多行的 Value 进行累加。 |
REPLACE | 替代,下一批数据中的 Value 会替换之前导入过的行中的 Value。 |
REPLACE_IF_NOT_NULL | 当遇到 null 值则不更新。 |
MAX | 保留最大值。 |
MIN | 保留最小值。 |
有如下场景:需要创建一个表,来记录公司每个用户的每一次消费行为信息,有如下字段:
而且,公司对这份数据,特别关心一个报表。
每一个用户最后一次访问我们页面的时间,用户消费的总金额,用户停留在我们页面上的最大最小时长。
每次要看这个报表,都需要在“明细表”上运行一个统计sql。
假如没有使用聚合模型,需要编写sql语句
Select
user_id,data,city,age,gender,
max(visit_data) as last_visit_data,
sum(cost) as cost,
max(dwell_time) as max_dwell_time,
min(dwell_time) as min_dwell_time
From t
Group by user_id,data,city,age,gender -- 对应的是聚合模型型key
聚合模型
sql示例:
-- 这是一个用户消费和行为记录的数据表
CREATE TABLE IF NOT EXISTS test.ex_user
(
`user_id` LARGEINT NOT NULL COMMENT "用户 id",
`date` DATE NOT NULL COMMENT "数据灌入日期时间",
`city` VARCHAR(20) COMMENT "用户所在城市",
`age` SMALLINT COMMENT "用户年龄",
`sex` TINYINT COMMENT "用户性别",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
`cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
`max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
ENGINE=olap
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
-- 分区
-- 分桶
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES
(
"replication_num" = "1"
);
向表中插入7条数据
insert into test.ex_user values
(10000,'2017-10-01','北京',20,0,'2017-10-01 06:00:00',20,10,10),
(10000,'2017-10-01','北京',20,0,'2017-10-01 07:00:00',15,2,2),
(10001,'2017-10-01','北京',30,1,'2017-10-01 17:05:45',2,22,22),
(10002,'2017-10-02','上海',20,1,'2017-10-02 12:59:12',200,5,5),
(10003,'2017-10-02','广州',32,0,'2017-10-02 11:20:00',30,11,11),
(10004,'2017-10-01','深圳',35,0,'2017-10-01 10:00:15',100,3,3),
(10004,'2017-10-03','深圳',35,0,'2017-10-03 10:20:22',11,6,6);
添加后我们发现,第一条记录与第二条由于user_id
、date
、 city
、 age
、 sex
字段一样记录合并成一条了,但是聚合数据有变化。last_visit_date
将以最后一条数据覆盖为准,然后cost
该进行了求和操作,max_dwell_time
取最大值与以及min_dwell_time
取最小值。
所以实际插入的数据只有6条。
再添加一条试试:
insert into test.ex_user values
(10000,'2017-10-01','北京',20,0,'2017-10-01 08:00:00',10,5,5);
数据的聚合,在 Doris 中有如下三个阶段发生:
1.每一批次数据导入的 ETL 阶段。该阶段会在每一批次导入的数据内部进行聚合。
2.底层 BE 进行数据 Compaction 的阶段。BE 会对已导入的不同批次的数据进行进一步的聚合。
3.数据查询阶段。在数据查询时,对于查询涉及到的数据,会进行对应的聚合。
UNIQUE 模型
是 相同key 的数据进行 自动去重 的表模型。在某些多维分析场景下,用户更关注的是如何保证 Key 的唯一性,即如何获得 Primary Key 唯一性约束。因此,引入了 Uniq 的数据模型。该模型本质上是聚合模型的一个特例,也是一种简化的表结构表示方式。
建表示例:
CREATE TABLE IF NOT EXISTS test.user
(
-- key列
`user_id` LARGEINT NOT NULL COMMENT "用户 id",
`username` VARCHAR(50) NOT NULL COMMENT "用户昵称",
-- value列
`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
PROPERTIES
(
"replication_num" = "1"
);
插入语句
insert into test.user values
(10000,'zss','北京',18,0,12345678910,'北京朝阳区 ','2017-10-01 07:00:00'),
(10000,'zss','北京',19,0,12345678910,'北京顺义区 ','2018-10-01 07:00:00'),
(10000,'lss','北京',20,0,12345678910,'北京海淀区','2017-11-15 06:10:20');
查询结果后发现,相同的user_id
、username
数据就会被替换掉
Uniq 模型完全可以用聚合模型中的 REPLACE 方式替代。其内部的实现方式和数据存储方式也完全一样。
Duplicate(复制的意思) 模型
就是存明细数据的表模型,既不做聚合也不做去重。
在某些多维分析场景下,数据既没有主键,也没有聚合需求。
Duplicate 数据模型可以满足这类需求。
数据完全按照导入文件中的数据进行存储,不会有任何聚合。
即使两行数据完全相同,也都会保留。
而在建表语句中指定的 DUPLICATE KEY,只是用来指明底层数据按照那些列进行排序。
建表语句:
CREATE TABLE IF NOT EXISTS test.log_detail
(
`timestamp` DATETIME NOT NULL COMMENT "日志时间",
`type` INT NOT NULL COMMENT "日志类型",
`error_code` INT COMMENT "错误码",
`error_msg` VARCHAR(1024) COMMENT "错误详细信息",
`op_id` BIGINT COMMENT "负责人 id",
`op_time` DATETIME COMMENT "处理时间"
)
DUPLICATE KEY(`timestamp`, `type`)
DISTRIBUTED BY HASH(`timestamp`) BUCKETS 1
PROPERTIES
(
"replication_num" = "1"
);
插入部分数据
insert into test.log_detail values
('2017-10-01 08:00:05',1,404,'not found page', 101, '2017-10-01 08:00:05'),
('2017-10-01 08:00:05',1,404,'not found page', 101, '2017-10-01 08:00:05'),
('2017-10-01 08:00:05',2,404,'not found page', 101, '2017-10-01 08:00:06'),
('2017-10-01 08:00:06',2,404,'not found page', 101, '2017-10-01 08:00:07');
查询结果后发现,插入的数据全部会被保留,即使两条数据一模一样,也会保留,正常可以操作用户行为日志数据这种
数据模型的选择
数据模型在建表时就已经确定,且无法修改;所以,选择一个合适的数据模型非常重要。
Aggregate 模型可以通过预聚合,极大地降低聚合查询时所需扫描的数据量和查询的计算量,非常适合有固定模式的报表类查询场景。
Unique 模型针对需要唯一主键约束的场景,可以保证主键唯一性约束。但是无法利用 ROLLUP 等预聚合带来的查询优势(因为本质是 REPLACE,没有 SUM 这种聚合方式)。
Duplicate 适合任意维度的查询。虽然同样无法利用预聚合的特性,但是不受聚合模型的约束,可以发挥列存模型的优势(只读取相关列,而不需要读取所有 Key 列)
欢迎加群讨论技术,1群:677373950(满了,可以加,但通过不了),2群:656732739

