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;

StarRocks中建表语句

CREATETABLE srtestdb.test_sr(
date_id DATE,
site_id INTDEFAULT10,
city_code SMALLINT,
user_name VARCHAR(32)DEFAULT'',
pv BIGINTDEFAULT0)PARTITIONBY 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'))DUPLICATEKEY(date_id, site_id, city_code)DISTRIBUTEDBYHASH(site_id) BUCKETS

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

建表模型

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

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

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

StarRocks 中的分区分桶

StarRocks 中的分区

创建分区

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

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

CREATETABLE site_access( 
 date_id DATE, 
 site_id INTDEFAULT'10',
 city_code VARCHAR(100),
 user_name VARCHAR(32)DEFAULT'',
 pv BIGINTDEFAULT'0')DUPLICATEKEY(dateid, site_id, city_code)PARTITIONBY 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"))DISTRIBUTEDBYHASH(event_day, site_id) BUCKETS 32；

批量创建分区

如下例，通过指定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

在自动创建分区之后，我们仍然可以使用 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")

管理分区

添加分区

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

删除分区

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

修改分区属性

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

ALTERTABLE site_access SET("dynamic_partition.enable"二"true");

查看分区信息

SHOW PARTITIONS FROM test.site_access2;

为什么要分区分桶

在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;

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

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

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

在下面的查询中，对于 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;

StarRocks中的分桶

分桶键的选择

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

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

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

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

select
city_code, sum(pv)
from site_access
where site_id = 54321;

但有的时候，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;

分桶数量的选择

分桶的压缩方式采用的是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:    102974    ReplicaId:    102985    BackendId:    100026    SchemaHash:    15150686277    Version:    28    VersionHash:    58156772826338576779    LstSuccessVersion:    210    LstSuccessVersionHash:    581567728263385767711    LstFailedVersion:    -112    LstFailedVersi onHash:    013    LstFailedTime:    NULL
14    DataSize:    83915    RowCount:    1616    State:    NORMAL
17    LstConsistencyCheckTime:    NULL
18    CheckVersion:    -119    CheckVersionHash:    -120    VersionCount:    221    PathHash:    -505782048230079383722    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

StarRocks 的表模型

数据模型

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

CREATETABLE srtestdb ・test_sr(
    siteid INT,
    citycode SMALLINT,
    username VARCHAR(32)DEFAULT'',
    pv BIGINT)DUPLICATEKEY(siteid, citycode, username)DISTRIBUTEDBYHASH(siteid) BUCKETS 10

根据业务需求的不同，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;

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

1INSERTINTO srtestdb・test_duplicate_tbl VALUES2(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);1011    一其中排序键(siteid, citycode)    有多条重复值如（10,100Jaaa'
12SELECT*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++-+        _一++

聚合模型

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

1CREATETABLE srtestdb.test_aggregate_tbl
2(3    siteid    INT,4    city    SMALLINT,5    username    VARCHAR(32),6    pv BIGINT    SUM DEFAULT '07)8    AGGREGATE KEY(siteid, city, username)9DISTRIBUTEDBYHASH(siteid) BUCKETS 10 PROPERTIES("replication_num"="1");

我们在聚合模型中插入和明细模型相同的数据，查询后发现，并没有存储明细数据，而是按照（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    +        -+-        -+-        -+            -+

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

1SELECT siteid, city, username,SUM(pv)2FROM srtestdb.test_duplicate_tbl
3GROUPBY 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+-+--+--+-+

主键模型

目前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");

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

1INSERTINTO srtestdb.test_primary_tbl VALUES(10,100,'aaa',1);2SELECT*FROM srtestdb.test_primary_tbl;3+++++4| siteid | city | username | pv    |5+++++6|10|100| aaa    |1|7+++++89--没有主键为(20,100, 'aaa')的数据，直接插入这条数据        10INSERTINTO srtestdb.test_primary_tbl VALUES(20,100,'aaa',1);11SELECT*FROM srtestdb.test_primary_tbl;12+++++13| siteid | city | username | pv    |14+++++15|10|100| aaa    |1|16|20|100| aaa    |1|17+++++1819--已经存在了主键为(10,100, 'aaa')的数据,更新原有记录        20INSERTINTO srtestdb.test_primary_tbl VALUES(10,100,'aaa',99);21SELECT*FROM srtestdb.test_primary_tbl;22+++++23| siteid | city | username | pv    |24+++++25|20|100| aaa    |1|26|10|100| aaa    |99|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会自动在稀疏索引上加入一些限制条件，确保稀疏索引内容较小，可以被缓存到内存中。

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

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

如何选择排序键

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

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

标签： mysql StarRocks doris

本文转载自: https://blog.csdn.net/Shockang/article/details/128895157
版权归原作者 Shockang 所有，如有侵权，请联系我们删除。