Doris基础知识篇(4):数据表
基本概念
Row&Column
一张表包括行（Row）和列（Column）。 Row 即用户的一行数据。 Column 用于描述一行数据中不同的字段。
- 在默认的数据模型中， Column 只分为排序列和非排序列。存储引擎会按照排序列对数据进行排序存储，并建立稀疏索引，以便在排序数据上进行快速查找。
- 而在聚合模型中，** Column 可以分为两大类： Key 和 Value。从业务角度看， Key 和Value 可以分别对应维度列和指标列。从聚合模型的角度来说， Key 列相同的行，会聚合成一行**。其中 Value 列的聚合方式由用户在建表时指定。
Partition&Tablet
用户数据先分区，再按hash值分桶。
分区partition，分桶tablet
Partiton：数据表的分区
Bucket：数据表的分桶，建议选择分布均匀的列
- 数值大时，查询并发高
- 数值小时，查询吞吐高
根据分桶列的 hash 值将数据划分成不同的 bucket。
- 如果使用了 Partition，则 DISTRIBUTED … 语句描述的是数据在各个分区内的划分规则。如果不使用 Partition，则描述的是对整个表的数据的划分规则。
- 分桶列可以选择多列，但必须为 Key 列。分桶列可以和 Partition 列相同或不同。
- 分桶列的选择，是在 查询吞吐 和 查询并发 之间的一种权衡：
- 如果选择多个分桶列，则数据分布更均匀。如果一个查询条件不包含所有分桶列的等值条件，那么该查询会触发所有分桶同时扫描，这样查询的吞吐会增加，单个查询的延迟随之降低。这个方式适合大吞吐低并发的查询场景。
- 如果仅选择一个或少数分桶列，则对应的点查询可以仅触发一个分桶扫描。此时，当多个点查询并发时，这些查询有较大的概率分别触发不同的分桶扫描，各个查询之间的 IO 影响较小（尤其当不同桶分布在不同磁盘上时），所以这种方式适合高并发的点查询场景。
- 分桶的数量理论上没有上限。
Tablet：物理概念，是表的分片大小，数据之间不重复。Tablet = Partiton num * Bucket num
- 注：表的数据量可以通过 SHOW DATA 命令查看，结果除以副本数，即表的数据量。
- 一个表的 Tablet 总数量等于 (Partition num * Bucket num)。
- 一个表的 Tablet 数量，在不考虑扩容的情况下，推荐略多于整个集群的磁盘数量。
- 单个 Tablet 的数据量理论上没有上下界，但建议在 1G - 10G 的范围内。如果单个 Tablet 数据量过小，则数据的聚合效果不佳，且元数据管理压力大。如果数据量过大，则不利于副本的迁移、补齐，且会增加 Schema Change 或者 Rollup 操作失败重试的代价（这些操作失败重试的粒度是 Tablet）。
- 当 Tablet 的数据量原则和数量原则冲突时，建议优先考虑数据量原则。
- 在建表时，每个分区的 Bucket 数量统一指定。但是在动态增加分区时（ADD PARTITION），可以单独指定新分区的 Bucket 数量。可以利用这个功能方便的应对数据缩小或膨胀。
- 一个 Partition 的 Bucket 数量一旦指定，不可更改。所以在确定 Bucket 数量时，需要预先考虑集群扩容的情况。比如当前只有 3 台 host，每台 host 有 1 块盘。如果 Bucket 的数量只设置为 3 或更小，那么后期即使再增加机器，也不能提高并发度。
- 举一些例子：假设在有10台BE，每台BE一块磁盘的情况下。如果一个表总大小为 500MB，则可以考虑4-8个分片。5GB：8-16个分片。50GB：32个分片。500GB：建议分区，每个分区大小在 50GB 左右，每个分区16-32个分片。5TB：建议分区，每个分区大小在 50GB 左右，每个分区16-32个分片。
建表
建表语法
create [external] table [if not exists] table_name
复合分区
分区+分桶
单分区
只分桶
字段类型
注：聚合模型在定义字段类型后，可以指定字段的 agg_type 聚合类型，如果不指定，则该列为 key 列。否则，该列为 value 列, 类型包括： SUM、 MAX、 MIN、 REPLACE。
示例
Range Partition
partition by **range(date)**
List Partition
partition by **list(date)**
数据划分
列
AGGREGATE KEY 数据模型中，所有没有指定聚合方式（SUM、 REPLACE、 MAX、MIN）的列视为 Key 列。而其余则为 Value 列
分区和分桶
分区
分区Partition：**range**+**list**两种方式分区
使用key列分区
range分区
**value less than (...)**左闭右开
分区的删除不会改变已存在分区的范围。删除分区可能出现空洞**values [...)**同时指定上下界
list分区
**values in(...)**
分桶
分桶Bucket（Tablet）：**hash**一种方式分区
复合分区
分区+分桶
多列分区
range分区
动态分区
用途：避免手动建立分区，麻烦。
通过动态分区功能，用户可以在建表时设定动态分区的规则。FE 会启动一个后台线程，根据用户指定的规则创建或删除分区。用户也可以在运行时对现有规则进行变更。
使用：
1 | CREATE TABLE tbl1 |
运行时修改：
1 | ALTER TABLE tbl1 SET |
规则参数：
动态分区的规则参数都以 dynamic_partition. 为前缀：
dynamic_partition.enable是否开启动态分区特性。可指定为 TRUE 或 FALSE。如果不填写，默认为 TRUE。如果为 FALSE，则 Doris 会忽略该表的动态分区规则。
dynamic_partition.time_unit动态分区调度的单位。可指定为 HOUR、DAY、WEEK、MONTH。分别表示按小时、按天、按星期、按月进行分区创建或删除。当指定为 HOUR 时，动态创建的分区名后缀格式为 yyyyMMddHH，例如2020032501。小时为单位的分区列数据类型不能为 DATE。当指定为 DAY 时，动态创建的分区名后缀格式为 yyyyMMdd，例如20200325。当指定为 WEEK 时，动态创建的分区名后缀格式为yyyy_ww。即当前日期属于这一年的第几周，例如 2020-03-25 创建的分区名后缀为 2020_13, 表明目前为2020年第13周。当指定为 MONTH 时，动态创建的分区名后缀格式为 yyyyMM，例如 202003。
dynamic_partition.time_zone动态分区的时区，如果不填写，则默认为当前机器的系统的时区，例如 Asia/Shanghai，如果想获取当前支持的时区设置，可以参考 https://en.wikipedia.org/wiki/List_of_tz_database_time_zones。
dynamic_partition.start动态分区的起始偏移，为负数。根据 time_unit 属性的不同，以当天（星期/月）为基准，分区范围在此偏移之前的分区将会被删除。如果不填写，则默认为 -2147483648，即不删除历史分区。
dynamic_partition.end动态分区的结束偏移，为正数。根据 time_unit 属性的不同，以当天（星期/月）为基准，提前创建对应范围的分区。
dynamic_partition.prefix动态创建的分区名前缀。
dynamic_partition.buckets动态创建的分区所对应的分桶数量。
dynamic_partition.replication_num动态创建的分区所对应的副本数量，如果不填写，则默认为该表创建时指定的副本数量。
dynamic_partition.start_day_of_week当 time_unit 为 WEEK 时，该参数用于指定每周的起始点。取值为 1 到 7。其中 1 表示周一，7 表示周日。默认为 1，即表示每周以周一为起始点。
dynamic_partition.start_day_of_month当 time_unit 为 MONTH 时，该参数用于指定每月的起始日期。取值为 1 到 28。其中 1 表示每月1号，28 表示每月28号。默认为 1，即表示每月以1号位起始点。暂不支持以29、30、31号为起始日，以避免因闰年或闰月带来的歧义。
dynamic_partition.create_history_partition默认为 false。当置为 true 时，Doris 会自动创建所有分区，具体创建规则见下文。同时，FE 的参数 max_dynamic_partition_num 会限制总分区数量，以避免一次性创建过多分区。当期望创建的分区个数大于 max_dynamic_partition_num 值时，操作将被禁止。当不指定 start 属性时，该参数不生效。
dynamic_partition.history_partition_num当 create_history_partition 为 true 时，该参数用于指定创建历史分区数量。默认值为 -1， 即未设置。
dynamic_partition.hot_partition_num指定最新的多少个分区为热分区。对于热分区，系统会自动设置其 storage_medium 参数为SSD，并且设置 storage_cooldown_time。hot_partition_num 是往前 n 天和未来所有分区我们举例说明。假设今天是 2021-05-20，按天分区，动态分区的属性设置为：hot_partition_num=2, end=3, start=-3。则系统会自动创建以下分区，并且设置 storage_medium 和 storage_cooldown_time 参数：p20210517：[“2021-05-17”, “2021-05-18”) storage_medium=HDD storage_cooldown_time=9999-12-31 23:59:59
p20210518：[“2021-05-18”, “2021-05-19”) storage_medium=HDD storage_cooldown_time=9999-12-31 23:59:59
p20210519：[“2021-05-19”, “2021-05-20”) storage_medium=SSD storage_cooldown_time=2021-05-21 00:00:00
p20210520：[“2021-05-20”, “2021-05-21”) storage_medium=SSD storage_cooldown_time=2021-05-22 00:00:00
p20210521：[“2021-05-21”, “2021-05-22”) storage_medium=SSD storage_cooldown_time=2021-05-23 00:00:00
p20210522：[“2021-05-22”, “2021-05-23”) storage_medium=SSD storage_cooldown_time=2021-05-24 00:00:00
p20210523：[“2021-05-23”, “2021-05-24”) storage_medium=SSD storage_cooldown_time=2021-05-25 00:00:00dynamic_partition.reserved_history_periods需要保留的历史分区的时间范围。当dynamic_partition.time_unit 设置为 “DAY/WEEK/MONTH” 时，需要以 [yyyy-MM-dd,yyyy-MM-dd],[…,…] 格式进行设置。当dynamic_partition.time_unit 设置为 “HOUR” 时，需要以 [yyyy-MM-dd HH:mm:ss,yyyy-MM-dd HH:mm:ss],[…,…] 的格式来进行设置。如果不设置，默认为 “NULL”。我们举例说明。假设今天是 2021-09-06，按天分类，动态分区的属性设置为：time_unit=”DAY/WEEK/MONTH”, end=3, start=-3, reserved_history_periods=”[2020-06-01,2020-06-20],[2020-10-31,2020-11-15]”。则系统会自动保留：[“2020-06-01”,”2020-06-20”],
[“2020-10-31”,”2020-11-15”]
或者time_unit=”HOUR”, end=3, start=-3, reserved_history_periods=”[2020-06-01 00:00:00,2020-06-01 03:00:00]”.则系统会自动保留：[“2020-06-01 00:00:00”,”2020-06-01 03:00:00”]
这两个时间段的分区。其中，reserved_history_periods 的每一个 […,…] 是一对设置项，两者需要同时被设置，且第一个时间不能大于第二个时间。创建历史分区规则
当 create_history_partition 为 true，即开启创建历史分区功能时，Doris 会根据 dynamic_partition.start 和 dynamic_partition.history_partition_num 来决定创建历史分区的个数。
假设需要创建的历史分区数量为 expect_create_partition_num，根据不同的设置具体数量如下：
- create_history_partition = true
- dynamic_partition.history_partition_num 未设置，即 -1. expect_create_partition_num = end - start;
- dynamic_partition.history_partition_num 已设置 expect_create_partition_num = end - max(start, -histoty_partition_num);
- create_history_partition = false 不会创建历史分区，expect_create_partition_num = end - 0;
当 expect_create_partition_num 大于 max_dynamic_partition_num（默认500）时，禁止创建过多分区。
临时分区
0.12版本之后
临时分区是归属于某一分区表的。只有分区表可以创建临时分区。
- 临时分区的分区列和正式分区相同，且不可修改。
- 一张表所有临时分区之间的分区范围不可重叠，但临时分区的范围和正式分区范围可以重叠。
- 临时分区的分区名称不能和正式分区以及其他临时分区重复。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15ALTER TABLE tbl1 ADD TEMPORARY PARTITION tp1 VALUES LESS THAN("2020-02-01");
ALTER TABLE tbl2 ADD TEMPORARY PARTITION tp1 VALUES [("2020-01-01"), ("2020-02-01"));
ALTER TABLE tbl1 ADD TEMPORARY PARTITION tp1 VALUES LESS THAN("2020-02-01")
("in_memory" = "true", "replication_num" = "1")
DISTRIBUTED BY HASH(k1) BUCKETS 5;
ALTER TABLE tbl3 ADD TEMPORARY PARTITION tp1 VALUES IN ("Beijing", "Shanghai");
ALTER TABLE tbl4 ADD TEMPORARY PARTITION tp1 VALUES IN ((1, "Beijing"), (1, "Shanghai"));
ALTER TABLE tbl3 ADD TEMPORARY PARTITION tp1 VALUES IN ("Beijing", "Shanghai")
("in_memory" = "true", "replication_num" = "1")
DISTRIBUTED BY HASH(k1) BUCKETS 5;
其他操作：https://doris.apache.org/zh-CN/docs/advanced/partition/table-temp-partition
DROP
使用 Drop 操作直接删除数据库或表后，可以通过 Recover 命令恢复数据库或表（限定时间内），但临时分区不会被恢复。
使用 Alter 命令删除正式分区后，可以通过 Recover 命令恢复分区（限定时间内）。操作正式分区和临时分区无关。
使用 Alter 命令删除临时分区后，无法通过 Recover 命令恢复临时分区。
TRUNCATE
使用 Truncate 命令清空表，表的临时分区会被删除，且不可恢复。
使用 Truncate 命令清空正式分区时，不影响临时分区。
-
ALTER
当表存在临时分区时，无法使用 Alter 命令对表进行 Schema Change、Rollup 等变更操作。
当表在进行变更操作时，无法对表添加临时分区。
properties
replication_num
是每个分桶的副本数，奇数，最大副本数和机器数量一致。
storage_medium & storage_cooldown_time
engine
创建test用户
mysql **-h** hadoop1 **-P** 9030 -uroot -pcreate user 'test' **identified by** 'test'
创建数据库
create database test_db;
用户授权
**grant all on **test_db to test;
