StarRocks表设计_starrocks建表

我们先来看一段代码

create table if not exists test (
    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 ""
) 
engine=olap
DUPLICATE KEY(event_time ,event_type)
PARTITION BY RANGE(event_time)
(
    PARTITION p1 VALUES LESS THAN ("2021-01-01")
)
distributed by hash(k2) buckets 32
properties (
    "storage_medium" = "SSD",
    "replication_num" = "3"
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

1. 建表基本语句与数据类型

create table if not exists test (
    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 ""
)
-- 这部分基本与mysql一致
1
2
3
4
5
6
7
8

col_type：列类型
具体的列类型以及范围等信息如下：

• TINYINT（1字节）
  范围：-2^7 + 1 ~ 2^7 - 1
• SMALLINT（2字节）
  范围：-2^15 + 1 ~ 2^15 - 1
• INT（4字节）
  范围：-2^31 + 1 ~ 2^31 - 1
• BIGINT（8字节）
  范围：-2^63 + 1 ~ 2^63 - 1
• LARGEINT（16字节）
  范围：-2^127 + 1 ~ 2^127 - 1
• FLOAT（4字节）
  支持科学计数法
• DOUBLE（12字节）
  支持科学计数法
• DECIMAL[(precision, scale)] (16字节)
  保证精度的小数类型。默认是 DECIMAL(10, 0)
  precision: 1 ~ 38
  scale: 0 ~ precision
  其中整数部分为：precision - scale
  不支持科学计数法
• DATE（3字节）
  范围：0000-01-01 ~ 9999-12-31
• DATETIME（8字节）
  范围：0000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• CHAR[(length)]
  定长字符串。长度范围：1 ~ 255。默认为1
• VARCHAR[(length)]
  变长字符串。长度范围：1 ~ 65533
• HLL (1~16385个字节)
  hll列类型，不需要指定长度和默认值，长度根据数据的聚合程度系统内控制，并且HLL列只能通过配套的hll_union_agg、> Hll_cardinality、hll_hash进行查询或使用
• BITMAP
  bitmap列类型，不需要指定长度和默认值。表示整型的集合，元素最大支持到2^64 - 1
  agg_type：聚合类型，如果不指定，则该列为 key 列。否则，该列为 value 列
  支持的聚合类型如下：
• SUM、MAX、MIN、REPLACE
• HLL_UNION(仅用于HLL列，为HLL独有的聚合方式)、
• BITMAP_UNION(仅用于 BITMAP 列，为 BITMAP 独有的聚合方式)、
• REPLACE_IF_NOT_NULL：这个聚合类型的含义是当且仅>当新导入数据是非NULL值时会发生替换行为，如果新导入的数据是NULL，那么StarRocks仍然会保留原值。
  注意：如果用在建表时REPLACE_IF_NOT_NULL列指定了NOT NULL，那么StarRocks仍然会将其转化NULL，不会向用户报错。用户可以借助这个类型完成「部分列导入」的功能。
  该类型只对聚合模型(key_desc的type为AGGREGATE KEY)有用，其它模型不能指这个。

2. 引擎

engine=olap
1

默认为 olap。可选 mysql, elasticsearch, hive
如果是 mysql，则需要在 properties 提供以下信息：
properties (
“host” = “mysql_server_host”,
“port” = “mysql_server_port”,
“user” = “your_user_name”,
“password” = “your_password”,
“database” = “database_name”,
“table” = “table_name”
)
在 StarRocks 创建 mysql 表的目的是可以通过 StarRocks 访问 mysql 数据库。 而 StarRocks 本身并不维护、存储任何 mysql 数据。
如果是 elasticsearch，则需要在 properties 提供以下信息：
properties (
“hosts” = “http://192.168.0.1:8200,http://192.168.0.2:8200”,
“user” = “root”,
“password” = “root”,
“index” = “tindex”,
“type” = “doc”
)
其中host为ES集群连接地址，可指定一个或者多个,user/password为开启basic认证的ES集群的用户名/密码，index是StarRocks中的表对应的ES的index名字，可以是alias，type指定index的type，默认是doc。
如果是 hive，则需要在 properties 提供以下信息：
properties (
“database” = “hive_db_name”,
“table” = “hive_table_name”,
“hive.metastore.uris” = “thrift://127.0.0.1:9083”
)
其中 database 是 hive 表对应的库名字，table 是 hive 表的名字，hive.metastore.uris 是 hive metastore 服务地址。

3. 数据模型

DUPLICATE KEY(event_time ,event_type)
1

1. Duplicate Key (默认)

   特点如下

   1. 需要保留原始数据
   2. 查询方式灵活, 不局限于预先定义的分析方式, 传统的预聚合方式难以命中
   3. 数据更新不频繁

   原理

   ​		用户可以指定数据表的排序列, 没有明确指定的情况下, 那么StarRocks会为表选择默认的几个列作为排序列。这样，在查询中，有相关排序列的过滤条件时，StarRocks能够快速地过滤数据，降低整个查询的时延。

   注意：在向StarRocks明细模型表中导入完全相同的两行数据时，StarRocks会认为是两行数据。

2. Aggregate Key 

   特点

   ​	在数据分析领域，有很多需要对数据进行统计和汇总操作的场景，就需要使用聚合模型

   适合采用聚合模型来分析的场景具有如下特点：

   1. 业务方进行的查询为汇总类查询，比如sum、count、max等类型的查询；
   2. 不需要召回原始的明细数据；
   3. 老数据不会被频繁更新，只会追加新数据。

   注意事项

   1. 聚合表中数据会分批次多次导入, 每次导入会形成一个版本. 相同排序键的数据行聚合有三种触发方式: 1. 数据导入时, 数据落盘前的聚合; 2. 数据落盘后, 后台的多版本异步聚合; 3. 数据查询时, 多版本多路归并聚合。
   2. 数据查询时, 指标列采用先聚合后过滤的方式, 把没必有做指标的列存储为维度列。

3. UNIQUE KEY
     1. 特点
     	 在有些场景下，数据会更新，StarRocks会采用更新模型来满足这种需求：1.已经写入的数据有大量的更新需求，2.需要进行实时数据分析。
    2. 原理
		更新模型中, 排序键满足唯一性约束, 成为主键。
        StarRocks存储内部会给每一个批次导入数据分配一个版本号, 同一主键的数据可能有多个版本, 查询时最大		(最新)版本的数据胜出。
4. Primary Key

   适用场景

   ​	相较更新模型，主键模型（Primary Key）可以更好地支持实时/频繁更新的功能。该类型的表要求有唯一的主键，支持对表中的行按主键进行更新和删除操作。

    需要注意的是：由于存储引擎会为主键建立索引，而在导入数据时会把主键索引加载在内存中，所以主键		模型对内存的要求比较高，还不适合主键特别多的场景。目前primary主键存储在内存中，为防止滥用造成内存占满，限制主键字段长度全部加起来编码后不能超过127字节。
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
33
34
35
36
37
38
39
40
41
42
43
44

在电商场景下，我们可能需要对订单进行明细的统计

-- 我们就可以把表设计成为这样
create table orderDetail (
    id varchar() NOT NULL COMMENT "订单明细id",
    order_id varchar() COMMENT "订单id",
    goods varchar COMMENT "商品名称" ,
    num int COMMENT "数量" ,
    price decimal(10,2) COMMENT "价格" ,
    create_time DATETIME COMMENT "创建时间" ,
    ...
)
engine=olap
duplicate key()
partition by RANGE(create_time)
(
    partition p1 values less than ("2020-01-01") ,
    partition p2 values less than ("2020-02-01") ,
    ...
)
distributed by hash(order_id) buckets 10 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

4. 分区分桶

PARTITION BY RANGE(event_time)
(
    PARTITION p1 VALUES LESS THAN ("2021-01-01")
)
distributed by hash(k2) buckets 32
1
2
3
4
5

StarRocks中Range分布，被称之为分区，用于分布的列也被称之为分区列，Hash分布，则被称之为分桶，用于分布的列也被称之为分桶列。
单分区建议控制在100G这个大小之内，分桶建议控制在100M-1G之间

我们还可以批量创建分区

PARTITION BY RANGE (datekey) (
    START ("2021-01-01") END ("2021-01-04") EVERY (INTERVAL 1 day)
)

分区等价于：
PARTITION p20210101 VALUES [('2021-01-01'), ('2021-01-02')),
PARTITION p20210102 VALUES [('2021-01-02'), ('2021-01-03')),
PARTITION p20210103 VALUES [('2021-01-03'), ('2021-01-04'))

还可以
START ("1") END ("5") EVERY (1)

等价于：
PARTITION p1 VALUES [("1"), ("2")),
PARTITION p2 VALUES [("2"), ("3")),
PARTITION p3 VALUES [("3"), ("4")),
PARTITION p4 VALUES [("4"), ("5"))

也可以同时使用不同时间类型
PARTITION BY RANGE (datekey) (
    START ("2019-01-01") END ("2021-01-01") EVERY (INTERVAL 1 YEAR),
    START ("2021-01-01") END ("2021-05-01") EVERY (INTERVAL 1 MONTH),
    START ("2021-05-01") END ("2021-05-04") EVERY (INTERVAL 1 DAY)
)

等价于：
PARTITION p2019 VALUES [('2019-01-01'), ('2020-01-01')),
PARTITION p2020 VALUES [('2020-01-01'), ('2021-01-01')),
PARTITION p202101 VALUES [('2021-01-01'), ('2021-02-01')),
PARTITION p202102 VALUES [('2021-02-01'), ('2021-03-01')),
PARTITION p202103 VALUES [('2021-03-01'), ('2021-04-01')),
PARTITION p202104 VALUES [('2021-04-01'), ('2021-05-01')),
PARTITION p20210501 VALUES [('2021-05-01'), ('2021-05-02')),
PARTITION p20210502 VALUES [('2021-05-02'), ('2021-05-03')),
PARTITION p20210503 VALUES [('2021-05-03'), ('2021-05-04'))
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
33
34
35

5. properties

properties (
    "storage_medium" = "SSD", -- 存储介质
    "replication_num" = "3" -- 设置副本数量
)
1
2
3
4

properties里面我们可以设置非常多的东西。
例如动态分区：

CREATE TABLE site_access(
event_day DATE,
site_id INT DEFAULT '10',
city_code VARCHAR(100),
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(event_day, site_id, city_code, user_name)
PARTITION BY RANGE(event_day)(
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
PROPERTIES(
    "dynamic_partition.enable" = "true",
    -- 是否打开动态分区
    "dynamic_partition.time_unit" = "DAY",
    -- 动态分区的粒度
    -- 1. 指定为DAY时分区后缀名称需要满足为yyMMdd的格式
    --     例如：PARTITION p20200321 VALUES LESS THAN ("2020-03-22")
    -- 2. 指定为 WEEK 时，分区名后缀需为yyyy_ww，例如2020_13代表2020年第13周。
    -- 3. 指定为 MONTH 时，动态创建的分区名后缀格式为 yyyyMM，例如 202003。
    "dynamic_partition.start" = "-3",
    -- 动态分区的开始时间
    "dynamic_partition.end" = "3",
    -- 动态分区的结束时间
    "dynamic_partition.prefix" = "p",
    -- 动态分区名称的前缀
    "dynamic_partition.buckets" = "32"
    -- 动态分区的内部的分桶数量
);
-- 这是官网上的一个例子
我们可以使用,来查看分区情况
show partitions from table_name ;
修改分区情况
alter table table_name set ("dynamic_partition.enable"="false") ;
alter table table_name set ("dynamic_partition.enable"="true");
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
33
34
35
36
37
38
39