StarRocks 建表指南_starrocks建表语句

前言

本文隶属于专栏《大数据技术体系》，该专栏为笔者原创，引用请注明来源，不足和错误之处请在评论区帮忙指出，谢谢！

本专栏目录结构和参考文献请见大数据技术体系

---

MySQL 与 StarRocks 建表区别

StarRocks 兼容 MySQL 5 协议，在建表时，与 MySQL 稍有不同。

MySQL中建表语句

CREATE TABLE	mysqltestdb ・ test_mysql(
dateid	DATE,
siteid	INT DEFAULT 10,
citycode	SMALLINT,
username	VARCHAR(32) DEFAULT '',
pv	BIGINT DEFAULT 0
)ENGINE=InnoDB DEFAULT CHARSET=latin1;	
1
2
3
4
5
6
7

StarRocks中建表语句

CREATE TABLE srtestdb.test_sr(
date_id DATE,
site_id INT DEFAULT 10,
city_code SMALLINT,
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT 0
)
PARTITION BY RANGE(date_id)(
PARTITION p1 VALUES LESS THAN ('2020-01-31'),
PARTITION p2 VALUES LESS THAN ('2020-02-29'),
PARTITION p3 VALUES LESS THAN ('2020-03-31')
)
DUPLICATE KEY(date_id, site_id, city_code)
DISTRIBUTED BY HASH(site_id) BUCKETS 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

其中，DUPLICATE KEY 语句指定的是 StarRocks 中的建表模型。

---

建表模型

在StarRocks中，建表模型有四种，分别是明细模型、聚合模型、更新模型以及主键模型。

DISTRIBUTED BY HASH 语句指定建表的
分桶键以及分桶的数量。

相比 MySQL 主要用于 OLTP 的业务不同，强烈建议在 StarRocks 中的表创建分区。

StarRocks 中的分区分桶

StarRocks 中的分区

创建分区

与MySQL的分区表作用一样，表通过分区后，可以有效的利用分区剪裁，减少数据的扫描量。

目前 StarRocks 只支持 range 分区，以下面的例子来介绍分区功能：

CREATE TABLE site_access( 
 date_id DATE, 
 site_id INT DEFAULT '10',
 city_code VARCHAR(100),
 user_name VARCHAR(32) DEFAULT '',
 pv BIGINT DEFAULT '0'
 )
 DUPLICATE KEY(dateid, site_id, city_code)
 PARTITION BY RANGE(date_id)(
 PARTITION p20200321 VALUES LESS THAN ("2020-03-22"),
 PARTITION p20200322 VALUES LESS THAN ("2020-03-23"), 
 PARTITION p20200323 VALUES LESS THAN ("2020-03-24"),
 PARTITION p20200324 VALUES LESS THAN ("2020-03-25")
 )
 DISTRIBUTED BY HASH(event_day, site_id) BUCKETS 32；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

批量创建分区

如下例，通过指定START END EVERY语句可以自动创建分区。其中，START的值将被包括在内，而 END的值会被排除在外。

CREATE TABLE site_access (
	date_id DATE,
	site_id INT,
	city_code SMALLINT,
	user_name VARCHAR(32),
	pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(date_id, site_id, city_code)
PARTITION BY RANGE (date_id)(
START ("2021-01-01") END ("2021-02-01") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(site_id) BUCKETS 10
1
2
3
4
5
6
7
8
9
10
11
12

在自动创建分区之后，我们仍然可以使用 ADD PARTITION 语句添加分区。

上面的例子中，我们通过 START END 语句指定了创建2021-01-01至2021-01-04的每日分区。

对于不包含在内的数据，插入时会抛出异常。

如果想保留这一部分数据，可以像下面的例子，手动创建两个边界分区。

ALTER TABLE test.site_access2 ADD PARTITION p_low VALUES LESS THAN ("2021-01-01"
)
ALTER TABLE test.site_access2 ADD PARTITION p_high VALUES LESS THAN ("2999-01-0 1")
1
2
3

管理分区

添加分区

ALTER TABLE test.site_access2 ADD PARTITION p_low VALUES LESS THAN 
("2021-01¬01")
1
2

删除分区

ALTER TABLE test.site_access2 ADD PARTITION p_low VALUES LESS THAN ("2021-01¬01")
1

修改分区属性

ALTER TABLE site_access SET("dynamic_partition.enable"二"false");
1

ALTER TABLE site_access SET("dynamic_partition.enable"二"true");
1

查看分区信息

SHOW PARTITIONS FROM test.site_access2;
1

为什么要分区分桶

在StarRocks中，数据采用先分区再分桶的方式存储。

如果没进行分区，那么全表默认为一个分区, 对全表进行分桶操作。

我们以下表作为例子

CREATE TABLE ads(
	ads_uuid INT,
	ads_date DATE,
	uuid INT,
	imp_cnt INT,
	click_cnt INT
)
DUPLICATE KEY (access_date, site_id, citycode)
PARTITION BY RANGE (ads_date)(
PARTITION p1 VALUES LESS THAN ('2020-01-31'),
PARTITION p2 VALUES LESS THAN ('2020-02-29'),
PARTITION p3 VALUES LESS THAN ('2020-03-31')
)
DISTRIBUTE BY HASH(ads_uuid) BUCKETS 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在选择分区分桶键时，我们需要尽可能的覆盖查询语句所带的条件。

表经过分区分桶后，表中的数据 变得更具有指向性。

原本需要全表扫描的查询，经过分区分桶后，只扫描几个分区分桶。

在下面的查 询中，对于 ads 表的查询，条件 ads_date > ‘2020-02-29’ AND ads_date < '2020-03-31’可以使用分区剪裁，裁减掉大部分的数据，条件ads_uuid = `可以使用分桶剪裁，可以将十个分桶中的九个剪裁掉，只扫描剩下的一个。

SELECT	pv		
FROM	ads		
WHERE	ads_date >	'2020-02-29
AND	ads_date <	'2020-03-31
AND	ads_uuid =	1;
1
2
3
4
5

StarRocks中的分桶

分桶键的选择

分区的下一级是分桶，StarRocks 采用 HASH 算法作为分桶算法，可以更高的让分区下的数据均衡的分 布在不同的节点上，避免了热点查询的问题。

同一分区内，分桶键hash值相同的数据会形成数据分 片(tablet) , tablet是多副本冗余存储的最小单位，也是调度进程进行副本管理的最小单位。

一般来说，我们会尽量让分区分桶键覆盖 where 语句的大部分条件。

如下面的查询，我们会选择site_id列作为分桶列：

select
city_code, sum(pv)
from site_access
where site_id = 54321;
1
2
3
4

但有的时候，site_id列数据分布不均，这样的分桶方式会产生数据倾斜，造成局部数据过热的情况。

我们可以通过组合分桶的方式，将数据打散：

CREATE TABLE site_access
(
 site_id INT DEFAULT '10',
 city_code SMALLINT,
 user_name VARCHAR(32) DEFAULT '',
 pv BIGINT
)
DUPLICATE KEY(site_id, city_code, user_name)
DISTRIBUTED BY HASH(site_id,city_code) BUCKETS 10;
1
2
3
4
5
6
7
8
9

分桶数量的选择

分桶的压缩方式采用的是lz4。

建议每个分桶数据文件大小在100MB-1GB左右。

一般来说，我们遵循以下几个规则确定分桶数：

• 在机器比较少的情况下，如果想充分利用机器资源可以考虑使用BE数量 * cpucore / 2 来设置 bucket 数量。例如有100GB数据的一张表，有4台BE,每台64C,只有一个分区，那么可以采用
  bucket数量4*64/2 = 128,这样每个tablet的数据也在781MB,同时也能充分利用 CPU资源。
• 分桶的数量影响查询的并行度，最佳实践是计算一下数据存储量，将每个tablet设置成 100MB-1GB 之间。
• 对照CSV文件，StarRocks的压缩比在 0.3 ~ 0.5 左右（以下计算取0.5,按照千进制计算）。假设10GB的CSV文件导入StarRocks,我们分为10个均匀的分区。一个分区承担的CSV文本数据量：10GB/10 = 1GB。单一副本按照0.5压缩比存入StarRocks文件大小：1GB * 0.5 = 500MB，通常存储三副本，一个分区的文件总大小为500MB*3 = 1500MB，按照建议，一个tablet规划300MB,则需设置5个分桶：1500MB/300MB = 5，如果是MySQL中的文件，一主两从的模式，我们只需要计算单副本的MySQL集群大小，按 照0.7的压缩比（经验值）换算成CSV文件大小，再按照上面的步骤计算出StarRcoks的分桶数量。
• 选择高基数的列来作为分桶键（如果有唯一ID就用这个列来作为分桶键即可），这样保证数据在 各个bucket中尽可能均衡，如果碰到数据倾斜严重的，数据可以使用多列作为分桶键（但一般不要太多）。

管理分桶

目前分桶数量没有办法做调整。

在PoC时，可以试探性的先导入一个分区的数据，可以通过show tablet命令中的DataSize （单位为 字节）判断tablet的大小。

1	mysql> show tablet from	srtestdb.test_duplicate_tbl \G
2	*************************** 1. row ***************************
3	TabletId:	10297
4	ReplicaId:	10298
5	BackendId:	10002
6	SchemaHash:	1515068627
7	Version:	2
8	VersionHash:	5815677282633857677
9	LstSuccessVersion:	2
10	LstSuccessVersionHash:	5815677282633857677
11	LstFailedVersion:	-1
12	LstFailedVersi onHash:	0
13	LstFailedTime:	NULL
14	DataSize:	839
15	RowCount:	16
16	State:	NORMAL
17	LstConsistencyCheckTime:	NULL
18	CheckVersion:	-1
19	CheckVersionHash:	-1
20	VersionCount:	2
21	PathHash:	-5057820482300793837
22	MetaUrl:	
	http://192.168.88.14:8040/api/meta/header/10297/1515068627

23	CompactionStatus:	
	http://192.168.88.14:8040/api/compaction/show?tablet_id=10297&schema_hash=151506
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

StarRocks 的表模型

数据模型

除了要指定分桶信息，与MySQL建表不同，在StarRocks中还需要指定建表的数据模型。在这个例 子中，使用DUPLICATE KEY关键字指定了创建明细模型。

CREATE TABLE srtestdb ・test_sr(
	siteid INT,
	citycode SMALLINT,
	username VARCHAR(32) DEFAULT '',
	pv BIGINT
)
DUPLICATE KEY(siteid, citycode, username)
DISTRIBUTED BY HASH(siteid) BUCKETS 10
1
2
3
4
5
6
7
8

根据业务需求的不同，StarRocks 提供了三种数据模型：

• 明细模型：表中存在排序键重复的数据行，和摄入数据行一一对应，用户可以召回全部的历史数据
• 聚合模型：表中不存在主键重复的数据行，摄入的主键重复的数据行将合并为一行
• 更新模型：主键满足唯一性约束，导入的数据通过主键替换掉重复的数据，相当于upsert操作

明细模型

StarRocks中默认使用明细模型。

和MySQL等关系型数据库一样，数据如何写入到StarRocks,就如何存储，不做计算转变。

明细模型以DUPLICATE KEY为关键字：

CREATE TABLE srtestdb・test_duplicate_tbl(
siteid INT,
city SMALLINT,
username VARCHAR(32) DEFAULT '',
pv BIGINT
)
DUPLICATE KEY(siteid, city, username)
DISTRIBUTED BY HASH(siteid) BUCKETS 10;
1
2
3
4
5
6
7
8

按照下面的例子，我们插入一组数据，全表查询后可以发现，查询的数据集插入的数据，没有经过任 何的变化。需要注意的是，我们的排序键(siteid,city, username)可以重复。

1	INSERT INTO srtestdb・test_duplicate_tbl VALUES
2	(10, 100,'	aaa',	1), (10,	100,	'aaa', 2),
3	(10, 200,'	aaa',	1), (10,	200,	'aaa ' , 2),
4	(20, 100,'	aaa',	1), (20,	100,	'aaa', 2),
5	(20, 200,'	aaa',	1), (20,	200,	'aaa', 2),
6	(10, 100,'	bbb',	1), (10,	100,	'bbb', 2),
7	(10, 200,'	bbb',	1), (10,	200,	'bbb', 2),
8	(20, 100,'	bbb',	1), (20,	100,	'bbb', 2),
9	(20, 200,'	bbb',	1), (20,	200,	'bbb', 2);
10					
11	一其中排序键(siteid, citycode)	有多条重复值如（10,100Jaaa'
12	SELECT * FROM srtestdb・test_duplicate_tbl;
13	+	+-		+		_一+			+
14	| siteid |	city	I username I pv	I
15	+	+-		+		—+			+
16	I	10 I	100	I aaa	I	1 I
17	I	10 I	100	I aaa	I	2 I
18	I	10 I	100	I bbb	I	1 I
19	I	10 I	100	I bbb	I	2 I
20	I	10 I	200	I aaa	I	1 I
21	I	10 I	200	I aaa	I	2 I
22	I	10 I	200	I bbb	I	1 I
23	I	10 I	200	I bbb	I	2 I
24	I	20 I	100	I aaa	I	1 I
25	I	20 I	100	I aaa	I	2 I
26	I	20 I	100	I bbb	I	1 I
27	I	20 I	100	I bbb	I	2 I
28	I	20 I	200	I aaa	I	1 I
29	I	20 I	200	I aaa	I	2 I
30	I	20 I	200	I bbb	I	1 I
31	I	20 I	200	I bbb	I	2 I
32	+	+-		+		_一+			+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

聚合模型

当我们的查询中，不需要召回明细数据，只需要一个汇总操作，可以使用聚合模型。数据在插入到表 中后，不存储明细数据，只存储聚合计算后的结果。聚合模型以AGGREGATE KEY为关键字：

1	CREATE TABLE srtestdb.test_aggregate_tbl
2	(
3	siteid	INT,
4	city	SMALLINT,
5	username	VARCHAR(32),
6	pv BIGINT	SUM DEFAULT '0

7	)
8	AGGREGATE KEY(siteid, city, username)
9	DISTRIBUTED BY HASH(siteid) BUCKETS 10 PROPERTIES("replication_num" = "1");
1
2
3
4
5
6
7
8
9
10

我们在聚合模型中插入和明细模型相同的数据，查询后发现，并没有存储明细数据，而是按照（siteid, city, username）做了聚合之后的结果：

1 INSERT INTO srtestdb.test_aggregate_tbl VALUES
2	(10,	100,	'aaa',	1),	(10,	100,	'aaa',	2),
3	(10,	200,	'aaa',	1),	(10,	200,	'aaa',	2),
4	(20,	100,	'aaa',	1),	(20,	100,	'aaa',	2),
5	(20,	200,	'aaa',	1),	(20,	200,	'aaa',	2),
6	(10,	100,	'bbb',	1),	(10,	100,	'bbb',	2),
7	(10,	200,	'bbb',	1),	(10,	200,	'bbb',	2),
8	(20,	100,	'bbb',	1),	(20,	100,	'bbb',	2),
9	(20,	200,	'bbb',	1),	(20,	200,	'bbb',	2);

10
11 SELECT * FROM srtestdb.test_aggregate_tbl;
12	+				-+-		-+			-+
13	|	siteid	|	city	|	username	| pv		|
14	+								
									
15	|	10	|	100	|	aaa	|	3	|
16	|	10	|	100	|	bbb	|	3	|
17	|	10	|	200	|	aaa	|	3	|
18	|	10	|	200	|	bbb	|	3	|
19	|	20	|	100	|	aaa	|	3	|
20	|	20	|	100	|	bbb	|	3	|
21	|	20	|	200	|	aaa	|	3	|
22	|	20	|	200	|	bbb	|	3	|
23	+		-+-		-+-		-+			-+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

聚合模型相当于我们在明细模型上做了一个聚合操作的物化视图:

1	SELECT siteid, city, username, SUM(pv)
2	FROM srtestdb.test_duplicate_tbl
3	GROUP BY siteid, city, username;
4	+		-+-				-+		-+
5	I	siteid	I	city	I	username	I sum('pv')	I
6	+							
								
7	I	10	I	100	I	bbb	I	3	I
8	I	20	I	200	I	bbb	I	3	I
9	I	20	I	200	I	aaa	I	3	I
10	I	10	I	100	I	aaa	I	3	I
11	I	20	I	100	I	aaa	I	3	I
12	I	10	I	200	I	aaa	I	3	I
13	I	10	I	200	I	bbb	I	3	I
14	I	20	I	100	I	bbb	I	3	I
15	+		-+-		-+-		-+		-+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

主键模型

目前StarRocks还不支持UPDATE语句，我们提供了主键模型实现UPSERT的功能。

当我们插入一条 数据，如果不存在这个key, StarRocks会插入这条记剥如果key已经存在了，StarRocks会修改原有的的记录，更新成新的值。

主键模型以PRIMARY KEY为关键字：

1	CREATE TABLE srtestdb ・test_primary_tbl
2	(			
3	siteid	INT	NOT	NULL,
4	city	SMALLINT	NOT	NULL,
5	username	VARCHAR(32)	NOT	NULL,
6	pv BIGINT	DEFAULT '0'		
7	)
8	PRIMARY KEY(siteid, city, username)
9	DISTRIBUTED BY HASH(siteid) BUCKETS 10 PROPERTIES("replication_num" = "1");
1
2
3
4
5
6
7
8
9

我们插入一条数据后，再分别插入一条主键已经存在的数据和主键未存在的数据，可以看到，表中还 有两条数据，主键已经存在的数据将原有的数据覆盖掉(UPDATE),主键没有存在的数据直接被插 入到表中(INSERT):

1	INSERT INTO srtestdb.test_primary_tbl VALUES	(10, 100,	'aaa'	,1);
2	SELECT * FROM srtestdb.test_primary_tbl;			
3	+	+	+	+	+			
4	| siteid | city | username | pv	|			
5	+	+	+	+	+			
6	|	10 |	100 | aaa	|	1 |			
7	+	+	+	+	+			
8
9	--没有主键为(20,100, 'aaa')的数据，直接插入这条数据		
10	INSERT INTO srtestdb.test_primary_tbl VALUES	(20, 100,	'aaa'	,1);
11	SELECT * FROM srtestdb.test_primary_tbl;			
12	+	+	+	+	+			
13	| siteid | city | username | pv	|			
14	+	+	+	+	+			
15	|	10 |	100 | aaa	|	1 |			
16	|	20 |	100 | aaa	|	1 |			
17	+	+	+	+	+			
18				
19	--已经存在了主键为(10,100, 'aaa')的数据,更新原有记录		
20	INSERT INTO srtestdb.test_primary_tbl VALUES	(10, 100,	'aaa'	,99);
21	SELECT * FROM srtestdb.test_primary_tbl;			
22	+	+	+	+	+			
23	| siteid | city | username | pv	|			
24	+	+	+	+	+			
25	|	20 |	100 | aaa	|	1 |			
26	|	10 |	100 | aaa	|	99 |			
27	+	+	+	+	+			
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

排序键

排序键介绍

Star Rocks表中的数据分为key与value在上面例子中，三种模型都使用了（siteid, city, username) 作为表的排序键(key)。

以上面的列子为例，排序列需要注意两点：

• 排序列的定义必须出现在建表语句中其他列的定义之前。
• 排序列的顺序可以是(siteid, city),或者是(siteid, city, username),但不能是(city,
  username)或者是(siteid, city, pv)
• 排序列的顺序是由CREATE TABLE中的顺序决定的
• 排序列的顺序可以是(siteid,city),或者是(siteid,city, username),但不能是(city, siteid) 或者是(city, siteid, username)

稀疏索引

为了加速查询，StarRocks会在排序列上自动创建稀疏索引。

在范围查找时，稀疏索引(shortkey index)可以帮我们快速的定位到起始的目标行。

当排序列非常多的时候，StarRocks会自动在稀疏索 引上加入一些限制条件，确保稀疏索引内容较小，可以被缓存到内存中。

由于稀疏索引的存在，可以对查询进行加速。

按照查询是否使用稀疏索引先导列的情况，加速的效果 不同

如何选择排序键

根据稀疏索引加速规则，在指定排序列的时候可以遵循以下的建议：

• 选择性(区分度)高的列放在前面，作为先导列
• 查询条件中最常被使用的列放在前面，作为先导列
• 尽量让分区间覆盖尽可能多的查询条件