StarRocks之数据模型实践
目前StarRocks根据摄入数据和实际存储数据之间的映射关系,分为明细模型(Duplicate key)、聚合模型(Aggregate key)、更新模型(Unique key)和主键模型(Primary key)。
四种模型分别对应不同业务场景
明细模型
StarRocks建表默认采用明细模型,排序列使用稀疏索引,可以快速过滤数据。明细模型用于保存所有历史数据,并且用户可以考虑将过滤条件中频繁使用的维度列作为排序键,比如用户经常需要查看某一时间,可以将事件时间和事件类型作为排序键。
- 建表,在建表时指定模型和排序键
1
2
3
4
5
6
7
8
9CREATE TABLE IF NOT EXISTS detail (
event_time DATETIME NOT NULL COMMENT "datetime of event",
event_type INT NOT NULL COMMENT "type of event",
user_id INT COMMENT "id of user",
device_code INT COMMENT "device of ",
channel INT COMMENT ""
)
DUPLICATE KEY ( event_time, event_type )
DISTRIBUTED BY HASH ( user_id ); - 插入测试数据
1
2
3
4
5INSERT INTO detail VALUES('2021-11-18 12:00:00.00',1,1001,1,1);
INSERT INTO detail VALUES('2021-11-17 12:00:00.00',2,1001,1,1);
INSERT INTO detail VALUES('2021-11-16 12:00:00.00',3,1001,1,1);
INSERT INTO detail VALUES('2021-11-15 12:00:00.00',1,1001,1,1);
INSERT INTO detail VALUES('2021-11-14 12:00:00.00',2,1001,1,1); - 查询
1
2
3
4
5
6
7
8
9
10
11SELECT *FROM detail;
+---------------------+------------+---------+-------------+---------+
| event_time | event_type | user_id | device_code | channel |
+---------------------+------------+---------+-------------+---------+
| 2021-11-18 12:00:00 | 1 | 1001 | 1 | 1 |
| 2021-11-17 12:00:00 | 2 | 1001 | 1 | 1 |
| 2021-11-16 12:00:00 | 3 | 1001 | 1 | 1 |
| 2021-11-15 12:00:00 | 1 | 1001 | 1 | 1 |
| 2021-11-14 12:00:00 | 2 | 1001 | 1 | 1 |
+---------------------+------------+---------+-------------+---------+
聚合模型
在数据分析中,很多场景需要基于明细数据进行统计和汇总,这个时候就可以使用聚合模型了。比如:统计app访问流量、用户访问时长、用户访问次数、展示总量、消费统计等等场景。
适合聚合模型来分析的业务场景有以下特点:
- 业务方进行查询为汇总类查询,比如sum、count、max
- 不需要查看原始明细数据
- 老数据不会被频繁修改,只会追加和新增
- 建表,指定聚合模型
1
2
3
4
5
6
7
8
9CREATE TABLE IF NOT EXISTS aggregate_tbl (
site_id LARGEINT NOT NULL COMMENT "id of site",
date DATE NOT NULL COMMENT "time of event",
city_code VARCHAR ( 20 ) COMMENT "city_code of user",
pv BIGINT SUM DEFAULT "0" COMMENT "total page views",
mt BIGINT MAX
)
AGGREGATE KEY(site_id, date, city_code)
DISTRIBUTED BY HASH (site_id); - 插入测试数据
1
2
3
4
5
6
7
8INSERT INTO aggregate_tbl VALUES(1001,'2021-11-18 12:00:00.00',100,1,5);
INSERT INTO aggregate_tbl VALUES(1001,'2021-11-18 12:00:00.00',100,1,10);
INSERT INTO aggregate_tbl VALUES(1001,'2021-11-18 12:00:00.00',100,1,15);
INSERT INTO aggregate_tbl VALUES(1001,'2021-11-18 12:00:00.00',100,1,100);
INSERT INTO aggregate_tbl VALUES(1001,'2021-11-18 12:00:00.00',100,1,20);
INSERT INTO aggregate_tbl VALUES(1002,'2021-11-18 12:00:00.00',100,1,5);
INSERT INTO aggregate_tbl VALUES(1002,'2021-11-18 12:00:00.00',100,3,25);
INSERT INTO aggregate_tbl VALUES(1002,'2021-11-18 12:00:00.00',100,1,15); - 查询测试数据,可以看到pv是sum累计的值,mt是明细中最大的值。如果只需要查看聚合后的指标,那么使用此种模型将会大大减少存储的数据量。
1
2
3
4
5
6
7
8select *from aggregate_tbl;
+---------+------------+-----------+------+------+
| site_id | date | city_code | pv | mt |
+---------+------------+-----------+------+------+
| 1001 | 2021-11-18 | 100 | 5 | 100 |
| 1002 | 2021-11-18 | 100 | 5 | 25 |
+---------+------------+-----------+------+------+
更新模型
有些分析场景之下,数据需要进行更新比如拉链表,StarRocks则采用更新模型来满足这种需求,比如电商场景中,订单的状态经常会发生变化,每天的订单更新量可突破上亿。这种业务场景下,如果只靠明细模型下通过delete+insert的方式,是无法满足频繁更新需求的,因此,用户需要使用更新模型(唯一键来判断唯一性)来满足分析需求。但是如果用户需要更加实时/频繁的更新操作,建议使用主键模型。
使用更新模型的场景特点:
- 已经写入的数据有大量的更新需求
- 需要进行实时数据分析
- 建表,指定更新模型
1
2
3
4
5
6
7
8CREATE TABLE IF NOT EXISTS update_detail (
create_time DATE NOT NULL COMMENT "create time of an order",
order_id BIGINT NOT NULL COMMENT "id of an order",
order_state INT COMMENT "state of an order",
total_price BIGINT COMMENT "price of an order"
)
UNIQUE KEY ( create_time, order_id )
DISTRIBUTED BY HASH ( order_id ); - 插入测试数据,注意:现在是指定create_time和order_id为唯一键,那么相同日期相同订单的数据会进行覆盖操作
1
2
3
4
5INSERT INTO update_detail VALUES('2011-11-18',1001,1,1000);
INSERT INTO update_detail VALUES('2011-11-18',1001,2,2000);
INSERT INTO update_detail VALUES('2011-11-17',1001,2,500);
INSERT INTO update_detail VALUES('2011-11-18',1002,3,3000);
INSERT INTO update_detail VALUES('2011-11-18',1002,4,4500); - 查询结果,可以看到如果日期和订单相同则会进行覆盖操作。
1
2
3
4
5
6
7
8
9select *from update_detail;
+-------------+----------+-------------+-------------+
| create_time | order_id | order_state | total_price |
+-------------+----------+-------------+-------------+
| 2011-11-17 | 1001 | 2 | 500 |
| 2011-11-18 | 1001 | 2 | 2000 |
| 2011-11-18 | 1002 | 4 | 4500 |
+-------------+----------+-------------+-------------+
主键模型
相比较更新模型,主键模型可以更好地支持实时/频繁更新的功能。虽然更新模型也可以实现实时对数据的更新,但是更新模型采用Merge on Read读时合并策略会大大限制查询功能,在主键模型更好地解决了行级的更新操作。配合Flink-connector-starrocks可以完成Mysql CDC实时同步的方案。
需要注意的是:由于存储引擎会为主键建立索引,导入数据时会把索引加载到内存中,所以主键模型对内存的要求更高,所以不适合主键模型的场景还是比较多的。
目前比较适合使用主键模型的场景有这两种:
- 数据冷热特征,比如最近几天的数据才需要修改,老的冷数据很少需要修改,比如订单数据,老的订单完成后就不在更新,并且分区是按天进行分区的,那么在导入数据时历史分区的数据的主键就不会被加载,也就不会占用内存了,内存中仅会加载近几天的索引。
- 大宽表(数百列数千列),主键只占整个数据的很小一部分,内存开销比较低。比如用户状态/画像表,虽然列非常多,但总的用户数量不大(千万-亿级别),主键索引内存占用相对可控。
原理:由于更新模型采用Merge策略,使得谓词无法下推和索引无法使用,严重影响查询性能。所以主键模型通过主键约束,保证同一个主键仅存一条数据的记录,这样就规避了Merge操作。StarRocks收到对某记录的更新操作时,会通过主键索引找到该条数据的位置,并对其标记为删除,再插入一条数据,相当于把update改写为delete+insert
建表,指定主键模型
1
2
3
4
5
6
7
8
9
10CREATE TABLE IF NOT EXISTS users (
user_id BIGINT NOT NULL,
name STRING NOT NULL,
email STRING NULL,
address STRING NULL,
age TINYINT NULL,
sex TINYINT NULL
)
PRIMARY KEY ( user_id )
DISTRIBUTED BY HASH (user_id);插入测试数据,和更新模型类似,当user_id相同发送冲突时会进行覆盖
1
2
3
4INSERT INTO users VALUES(1001,'张三','zhang@qq.com','address1',17,'0');
INSERT INTO users VALUES(1001,'李四','li@qq.com','address2',18,'1');
INSERT INTO users VALUES(1002,'alice','alice@qq.com','address3',18,'0');
INSERT INTO users VALUES(1002,'peter','peter@qq.com','address4',18,'1');查询数据
1
2
3
4
5
6
7select *from users;
+---------+--------+--------------+----------+------+------+
| user_id | name | email | address | age | sex |
+---------+--------+--------------+----------+------+------+
| 1001 | 李四 | li@qq.com | address2 | 18 | 1 |
| 1002 | peter | peter@qq.com | address4 | 18 | 1 |
+---------+--------+--------------+----------+------+------+