Doris：数据表设计

目录

1.数据模型

1.1 聚合模型 Aggregate

1.2 主键模型 Unique

1.3 明细模型 Duplicate

2.数据类型

2.1 基本数据类型

2.2 自增列

3.数据划分

3.1 分区(Partition)

3.1.1 Range分区

3.1.2 List分区

2.2 分桶(Bucket)

3.3 动态分区

3.4 自动分区

4.基本操作

4.1 创建数据库

4.2 创建表

4.3 表结构变更

---

1.数据模型

        在 Doris 中，数据以表（Table）的形式进行逻辑上的描述。 一张表包括行（Row）和列（Column）。Column 可以分为两大类：Key (维度列) 和 Value（指标列）。Doris建表时需要指定Key列信息，除了Key列剩下的就是Value列。

        Doris 的数据模型主要分为3类:

• 聚合模型 Aggregate
• 主键模型 Unique
• 明细模型 Duplicate

1.1 聚合模型 Aggregate

        建表时使用“aggregate key”关键字指定key列，value列需要指定聚合类型（AggregationType）。当导入新数据时，对于 Key 列相同的行会聚合成一行，而 Value 列会按照设置的 AggregationType 进行聚合。

    AggregationType 目前有以下几种聚合方式和agg_state：

1. SUM：求和，多行的 Value 进行累加。
2. REPLACE：替代，下一批数据中的 Value 会替换之前导入过的行中的 Value。
3. MAX：保留最大值。
4. MIN：保留最小值。
5. REPLACE_IF_NOT_NULL：非空值替换。和 REPLACE 的区别在于对于null值，不做替换。
6. HLL_UNION：HLL 类型的列的聚合方式，通过 HyperLogLog 算法聚合。
7. BITMAP_UNION：BIMTAP 类型的列的聚合方式，进行位图的并集聚合。

如果这几种聚合方式无法满足需求，则可以选择使用agg_state类型。

1.2 主键模型 Unique

        建表时使用“unique key”关键字指定key列，导入新数据时表中的数据根据指定的 Key 的保证唯一性，当用户更新一条数据时，新写入的数据会覆盖具有相同 key（主键）的旧数据。在1.2版本之前读时合并，在1.2版本中实现写时合并（merge on write）【默认关闭，可以通过设置（"enable_unique_key_merge_on_write" = "true"）开启】。

1.3 明细模型 Duplicate

        建表时使用“duplicate key”关键字指定key列，这里的key列只是用来指明底层数据按照哪些列进行排序。Duplicate模型支持存储重复数据，即使两条数据完全一致。

        注意：

1. 当创建表的时候没有指定Unique、Aggregate或Duplicate时，会默认创建一个Duplicate模型的表，并自动指定排序列。
2. 建表时声明的key列的顺序要和建表语句中的列的顺序要一致。如下图中的key列(user_id,username),必须前后顺序一致，且定义列时key必须在所有value列的前面。

2.数据类型

2.1 基本数据类型

字段	描述
BOOLEAN	与TINYINT一样，0代表false，1代表true
TINYINT	1字节有符号整数，范围[-128, 127]
SMALLINT	2字节有符号整数，范围[-32768, 32767]
INT	4字节有符号整数，范围[-2147483648, 2147483647]
BIGINT	8字节有符号整数，范围[-9223372036854775808, 9223372036854775807]
LARGEINT	6字节有符号整数，范围[-2^127 + 1 ~ 2^127 - 1]
FLOAT	4字节浮点数
DOUBLE	8字节浮点数
DECIMAL	DECIMAL(M[,D])高精度定点数，默认值为 DECIMAL(9, 0)。M 代表一共有多少个有效数字(precision)，D 代表小数位有多少数字(scale)。
DATE	日期类型，目前的取值范围是['0000-01-01', '9999-12-31'], 默认的打印形式是'yyyy-MM-dd'
DATETIME	DATETIME([P])日期时间类型，可选参数P表示时间精度，取值范围是[0, 6]，即最多支持6位小数（微秒）。不设置时为0。 取值范围是['0000-01-01 00:00:00[.000000]', '9999-12-31 23:59:59[.999999]']. 打印的形式是'yyyy-MM-dd HH:mm:ss.SSSSSS'
CHAR	CHAR(M)定长字符串，M代表的是定长字符串的字节长度。M的范围是1-255
VARCHAR	VARCHAR(M) 变长字符串，M代表的是变长字符串的字节长度。M的范围是1-65533。 注意：变长字符串是以UTF-8编码存储的，因此通常英文字符占1个字节，中文字符占3个字节。
STRING	STRING 变长字符串，默认支持1048576 字节（1MB），可调大到 2147483643 字节（2G），可通过be配置`string_type_length_soft_limit_bytes`调整。 String类型只能用在value 列，不能用在 key 列和分区 分桶列。 注意：变长字符串是以UTF-8编码存储的，因此通常英文字符占1个字节，中文字符占3个字节。

        上图为常用的基本数据类型，特殊数据类型，请参考官网。

2.2 自增列

        自增列是Doris2.1新增的一个功能，在导入数据时，Doris 会为在自增列上没有指定值的数据行分配一个表内唯一的值。要使用自增列，需要在建表CREATE-TABLE时为对应的列添加AUTO_INCREMENT属性。

• 如果导入的目标列中不包含自增列，则自增列将会被 Doris 自动生成的值填充。
• 如果导入的目标列中包含自增列，则导入数据中该列中的 null 值将会被 Doris 自动生成的值替换，非 null 值则保持不变。需要注意非 null 值会破坏自增列值的唯一性。
• 仅 Duplicate 模型表和 Unique 模型表可以包含自增列。
• 一张表最多只能包含一个自增列。自增列的类型必须是 BIGINT 类型，且必须为 NOT NULL。

        Doris 保证自增列上自动生成的值是稠密的，但不能保证在一次导入中自动填充的自增列的值是完全连续的，因此可能会出现一次导入中自增列自动填充的值具有一定的跳跃性的现象。这是因为出于性能考虑，每个 BE 上都会缓存一部分预先分配的自增列的值，每个 BE 上缓存的值互不相交。此外，由于缓存的存在，Doris 不能保证在物理时间上后一次导入的数据在自增列上自动生成的值比前一次更大。因此，不能根据自增列分配出的值的大小来判断导入时间上的先后顺序。

3.数据划分

        Doris 支持两层的数据划分。第一层是 Partition，支持 Range 和 List 的划分方式。第二层是 Bucket（Tablet），支持 Hash 和 Random 的划分方式。

3.1 分区(Partition)

• Partition 列可以指定一列或多列，分区列必须为 KEY 列。
• 不论分区列是什么类型，在写分区值时，都需要加双引号。
• 分区数量理论上没有上限。
• 当不使用 Partition 建表时，系统会自动生成一个和表名同名的，全值范围的 Partition。该 Partition 对用户不可见，并且不可删改。
• 创建分区时不可添加范围重叠的分区。

3.1.1 Range分区

• 分区列通常为时间列，以方便的管理新旧数据。
• Range 分区支持的列类型：[DATE,DATETIME,TINYINT,SMALLINT,INT,BIGINT,LARGEINT]
• Partition 支持通过 VALUES LESS THAN (...) 仅指定上界，系统会将前一个分区的上界作为该分区的下界，生成一个左闭右开的区间。也支持通过 VALUES [...) 指定上下界，生成一个左闭右开的区间。

3.1.2 List分区

• 分区列支持 BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR 数据类型，分区值为枚举值。只有当数据为目标分区枚举值其中之一时，才可以命中分区。
• Partition 支持通过 VALUES IN (...) 来指定每个分区包含的枚举值。

2.2 分桶(Bucket)

• 如果使用了 Partition，则 DISTRIBUTED ... 语句描述的是数据在各个分区内的划分规则。如果不使用 Partition，则描述的是对整个表的数据的划分规则。
• 分桶列可以是多列，Aggregate 和 Unique 模型必须为 Key 列，Duplicate 模型可以是 key 列和 value 列。分桶列可以和 Partition 列相同或不同。
• 分桶列的选择，是在 查询吞吐 和 查询并发 之间的一种权衡：

        1)如果选择多个分桶列，则数据分布更均匀。如果一个查询条件不包含所有分桶列的等值条件，那么该查询会触发所有分桶同时扫描，这样查询的吞吐会增加，单个查询的延迟随之降低。这个方式适合大吞吐低并发的查询场景。
        2)如果仅选择一个或少数分桶列，则对应的点查询可以仅触发一个分桶扫描。此时，当多个点查询并发时，这些查询有较大的概率分别触发不同的分桶扫描，各个查询之间的IO影响较小（尤其当不同桶分布在不同磁盘上时），所以这种方式适合高并发的点查询场景。

• AutoBucket: 根据数据量，计算分桶数。 对于分区表，可以根据历史分区的数据量、机器数、盘数，确定一个分桶。
• 分桶的数量理论上没有上限。

3.3 动态分区

        在某些使用场景下，用户会将表按照天进行分区划分，每天定时执行例行任务，这时需要使用方手动管理分区，否则可能由于使用方没有创建分区导致数据导入失败，这给使用方带来了额外的维护成本。

        通过动态分区功能，用户可以在建表时设定动态分区的规则。FE 会启动一个后台线程，根据用户指定的规则创建或删除分区。用户也可以在运行时对现有规则进行变更。动态分区只支持 Range 分区。

        动态分区使用规则请参考官方文档：动态分区 - Apache Doris。

3.4 自动分区

        Doris 2.0.3版本支持自动分区。自动分区功能支持了在导入数据过程中自动检测是否存在对应所属分区。如果不存在，则会自动创建分区并正常进行导入。

        新功能，使用可参考官方文档：自动分区 - Apache Doris

4.基本操作

4.1 创建数据库

create database dmp;

4.2 创建表

        1)创建用户表(user)，使用Unique模型，unique key 为id、username。并根据用户id，自动分桶。

create table user(
	id int NOT NULL COMMENT "用户id",
	username varchar(32) NOT NULL COMMENT "用户名",
	password varchar(255) NOT NULL COMMENT "用户密码",
	mobile varchar(11) COMMENT "手机号码",
	remark varchar(500) COMMENT "备注",
	create_time datetime NOT NULL COMMENT "注册时间"
)
unique key(`id`,`username`)
DISTRIBUTED BY HASH(`id`) BUCKETS auto;

插入测试数据，其中第一和第三条的key列值一致，保留最新数据。

insert into user(id,username,password,mobile,create_time) values 
(1,'weisx','123456','13712345678','2023-10-06 18:10:20'),
(2,'yichen','123456','13812345678','2023-10-06 18:10:30'),
(1,'weisx','123456','13912345678','2023-10-06 18:10:40')

        2)创建订单表(tbl_order)，使用Unique模型，unique key 为user_id、product_id、order_time，并根据order_time按月进行动态分区。

create table tbl_order(
	user_id int NOT NULL COMMENT "用户id",
	username varchar(32) NOT NULL COMMENT "用户名",
	product_id int NOT NULL COMMENT "商品id",
	product_name varchar(255) COMMENT "商品名称",
	order_time datetime NOT NULL COMMENT "订单时间",
	nums int COMMENT "数量",
	amount int COMMENT "订单金额"
	
)
unique key(`user_id`,`username`,`product_id`,`product_name`,`order_time`)
PARTITION BY RANGE(`order_time`) ()
DISTRIBUTED BY HASH(`user_id`)
PROPERTIES
(
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "MONTH",
	"dynamic_partition.create_history_partition" = "true",
	"dynamic_partition.history_partition_num" = "10",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "tbl_order_p",
    "dynamic_partition.buckets" = "8"
);

可以使用show partitions from tbl_order; 查看分区信息

插入测试数据,第一条数据没有对应分区，插入失败

insert into tbl_order(user_id,username,product_id,product_name,order_time,nums,amount) values 
(1,'weisx',1,'HUAWEI Mate 60','2023-09-30 18:10:20',1,7999),
(1,'weisx',2,'荣耀Magic V2','2023-10-06 18:10:20',2,8999),
(1,'weisx',1,'HUAWEI Mate 60','2023-11-30 18:10:20',1,7999),
(2,'yichen',2,'荣耀Magic V2','2023-10-06 18:10:20',2,8999)

提示错误信息如下：

去掉没有分区的数据，则可以正常插入：

        3）创建用户月度订单统计表，使用Aggregate模型，Aggregate key 为user_id,username,order_month。

create table user_order_month(
	user_id int NOT NULL COMMENT "用户id",
	username varchar(32) NOT NULL COMMENT "用户名",
	order_month varchar(32) NOT NULL COMMENT "订单月份",
	total_nums int SUM DEFAULT "0" COMMENT "商品总数量",
	total_amount int SUM DEFAULT "0" COMMENT "订单总金额"
)
aggregate  key(`user_id`,`username`,`order_month`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS auto;

插入测试数据,其中用户1，10月份有多条数据，插入之后按聚合规则对数据进行聚合操作，最后只保留聚合之后的数据。

insert into user_order_month(user_id,username,order_month,total_nums,total_amount) values 
(1,'weisx','2023-10',2,8999),
(1,'weisx','2023-10',1,3999),
(1,'weisx','2023-11',1,7999),
(2,'yichen','2023-10',2,8999)

        注意：建表时必须包含有分区信息，同时指定的可以列和分区列需要使用`号括起来,例如(`列名`)。

4.3 表结构变更

使用 ALTER TABLE COLUMN 命令可以修改表的 Schema，包括如下修改：

1.增加列

 alter table user add column email varchar(50) after mobile;

2.删除列

 alter table user drop column email;

3.修改列类型

 alter table user modify column mobile varchar(50);

4.改变列顺序

修改列顺序时，需要指定全部的字段顺序，否则会报错。

alter table user order by (id,username,password,email,mobile,remark,create_time);