Doris为数据分析而生的olap数据库：数据模型和数据分区使用详解_doris 字符串分区

 Apache Doris是一个现代化的MPP分析性数据库产品。是一个由百度开源，在2018年贡献给Apache基金会，成为有顶级开源项目。仅需要亚秒级响应时间即可获得查询结果，可以有效地支持实时数据分析。Apache Doris可以满足多种数据分析需求，如数仓T+1模式固定历史报表，实时数据分析等。

数据模型：

Doris中的字段类型：

概念解读：

在 DORIS 中，数据以表（Table）的形式进行逻辑上的描述。一张表包括行（Row）和列（Column）。Row 即用户的一行数据。Column 用于描述一行数据中不同的字段。

Column 可以分为两大类：Key 和 Value。从业务角度看，Key 和 Value 可以分别对应维度列和指标列。

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

• Duplicate 明细模型

• Aggregate 聚合模型

• Unique 唯一主键模型

Duplicate 明细模型

明细模型是 DORIS 默认使用的数据模型。该数据模型不会对导入的数据进行任何处理。表中的数据即用户导入的原始数据，主键仅仅是排序字段，在 DUPLICATE KEY 的选择上，我们建议适当的选择前 2-4 列就可以。。

CREATE TABLE session_data(    visitorid   SMALLINT,    sessionid   BIGINT,    city        CHAR(20),    ip          varchar(32))DUPLICATE KEY(visitorid, sessionid)comment '明细模型建表'DISTRIBUTED BY HASH(sessionid, visitorid) BUCKETS 10;

    

    这种数据模型适用于既没有聚合需求，又没有主键唯一性约束的原始数据的存储。同时，用户也可以通过物化视图功能功能在这种模型基础上建立聚合视图，因此是一种比较推荐的数据模型。

Aggregate 聚合模型

    聚合模型需要用户在建表时显式的将列分为 Key 列和 Value 列。该模型会自动的对 Key 相同的行，在 Value 列上进行聚合操作。

CREATE TABLE site_visit(    siteid      INT,    city        SMALLINT,    username    VARCHAR(32),    pv BIGINT   SUM DEFAULT '0')AGGREGATE KEY(siteid, city, username)comment '聚合模型表'DISTRIBUTED BY HASH(siteid) BUCKETS 10;

    表中的列按照是否设置了 AggregationType，分为 Key (维度列) 和 Value（指标列）。没有设置 AggregationType 的，如 user_id、date、age ... 等称为 Key，而设置了 AggregationType 的称为 Value。

数据的聚合，在 DORIS 中有如下三个阶段发生：

1. 每一批次数据导入的 ETL 阶段。该阶段会在每一批次导入的数据内部进行聚合。

2. 底层 BE 进行数据 Compaction 的阶段。该阶段，BE 会对已导入的不同批次的数据进行进一步的聚合。

3. 数据查询阶段。在数据查询时，对于查询涉及到的数据，会进行对应的聚合。

    数据在不同时间，可能聚合的程度不一致。比如一批数据刚导入时，可能还未与之前已存在的数据进行聚合。但是对于用户而言，用户只能查询到聚合后的数据。即不同的聚合程度对于用户查询而言是透明的。用户需始终认为数据以最终的完成的聚合程度存在，而不应假设某些聚合还未发生。

聚合模型表的局限性就是在count(*) 查询需要扫描大量的数据。

因此，当业务上有频繁的 count(*) 查询时，我们建议用户通过增加一个值恒为 1 的，聚合类型为 SUM 的列来模拟 count(*)。

Unique 唯一主键模型

    在某些多维分析场景下，用户更关注的是如何保证 Key 的唯一性，即如何获得 Primary Key 唯一性约束。因此，我们引入了 Unique 的数据模型。该模型本质上是聚合模型的一个特例，也是一种简化的表结构表示方式。

CREATE TABLE sales_order(    orderid     BIGINT,    status      TINYINT,    username    VARCHAR(32),    amount      BIGINT DEFAULT '0')UNIQUE KEY(orderid)comment '明细去重模型'DISTRIBUTED BY HASH(orderid) BUCKETS 10;

会根据主键保留唯一条数据，后来的数据会覆盖前一条数据。

数据模型的选择建议

因为数据模型在建表时就已经确定，且无法修改。所以，选择一个合适的数据模型非常重要。

1. Aggregate 模型可以通过预聚合，极大地降低聚合查询时所需扫描的数据量和查询的计算量，非常适合有固定模式的报表类查询场景。但是该模型对 count(*) 查询很不友好。同时因为固定了 Value 列上的聚合方式，在进行其他类型的聚合查询时，需要考虑语意正确性。

2. Unique 模型针对需要唯一主键约束的场景，可以保证主键唯一性约束。但是无法利用 ROLLUP 等预聚合带来的查询优势（因为本质是 REPLACE，没有 SUM 这种聚合方式）。

3. Duplicate 适合任意维度的 Ad-hoc 查询。虽然同样无法利用预聚合的特性，但是不受聚合模型的约束，可以发挥列存模型的优势（只读取相关列，而不需要读取所有 Key 列）。

数据分区

概念解读

Partition & Tablet

    在 Doris 的存储引擎中，用户数据首先被划分成若干个分区（Partition），划分的规则通常是按照用户指定的分区列进行范围划分，比如按时间划分。而在每个分区内，数据被进一步的按照Hash的方式分桶，分桶的规则是要找用户指定的分桶列的值进行Hash后分桶。每个分桶就是一个数据分片（Tablet），也是数据划分的最小逻辑单元。

    Tablet之间的数据是没有交集的，独立存储的。Tablet也是数据移动、复制等操作的最小物理存储单元。

    Partition 可以视为是逻辑上最小的管理单元。数据的导入与删除，都可以或仅能针对一个 Partition 进行。

CREATE TABLE IF NOT EXISTS example_db.expamle_tbl(    `user_id` LARGEINT NOT NULL COMMENT "用户id",    `date` DATE NOT NULL COMMENT "数据灌入日期时间",    `timestamp` DATETIME NOT NULL COMMENT "数据灌入的时间戳",    `city` VARCHAR(20) COMMENT "用户所在城市",    `age` SMALLINT COMMENT "用户年龄",    `sex` TINYINT COMMENT "用户性别",    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间")ENGINE=olapAGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)PARTITION BY RANGE(`date`)(    PARTITION `p201701` VALUES LESS THAN ("2017-02-01"),    PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),    PARTITION `p201703` VALUES LESS THAN ("2017-04-01"))DISTRIBUTED BY HASH(`user_id`) BUCKETS 16PROPERTIES(    "replication_num" = "3",    "storage_medium" = "SSD",    "storage_cooldown_time" = "2018-01-01 12:00:00");

AGGREGATE KEY 数据模型中，所有没有指定聚合方式（SUM、REPLACE、MAX、MIN）的列视为 Key 列。而其余则为 Value 列。

定义列时，可参照如下建议：

1. Key 列必须在所有 Value 列之前。

2. 尽量选择整型类型。因为整型类型的计算和查找比较效率远高于字符串。

3. 对于不同长度的整型类型的选择原则，遵循 够用即可。

4. 对于 VARCHAR 和 CHAR 类型的长度，遵循 够用即可。

5. 所有列的总字节长度（包括 Key 和 Value）不能超过 100KB。、

分区与分桶

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

也可以仅使用一层分区。使用一层分区时，只支持 Bucket 划分。

1. Partition

   • Partition 列可以指定一列或多列。分区列必须为 KEY 列。多列分区的使用方式在后面 多列分区 小结介绍。

   • 不论分区列是什么类型，在写分区值时，都需要加双引号。

   • 分区列通常为时间列，以方便的管理新旧数据。

   • 分区数量理论上没有上限。

   • 当不使用 Partition 建表时，系统会自动生成一个和表名同名的，全值范围的 Partition。该 Partition 对用户不可见，并且不可删改。

   • Partition 支持通过 VALUES LESS THAN (...) 仅指定上界，系统会将前一个分区的上界作为该分区的下界，生成一个左闭右开的区间。通过，也支持通过 VALUES [...) 指定同时指定上下界，生成一个左闭右开的区间。

   • 综分区的删除不会改变已存在分区的范围。删除分区可能出现空洞。通过 VALUES LESS THAN 语句增加分区时，分区的下界紧接上一个分区的上界。

   • 不可添加范围重叠的分区。

2.Bucket

    如果使用了 Partition，则 DISTRIBUTED ... 语句描述的是数据在各个分区内的划分规则。如果不使用 Partition，则描述的是对整个表的数据的划分规则。

    分桶列可以是多列，但必须为 Key 列。分桶列可以和 Partition 列相同或不同。

分桶列的选择，是在 查询吞吐 和 查询并发 之间的一种权衡：

如果选择多个分桶列，则数据分布更均匀。如果一个查询条件不包含所有分桶列的等值条件，那么该查询会触发所有分桶同时扫描，这样查询的吞吐会增加，单个查询的延迟随之降低。这个方式适合大吞吐低并发的查询场景。

如果仅选择一个或少数分桶列，则对应的点查询可以仅触发一个分桶扫描。此时，当多个点查询并发时，这些查询有较大的概率分别触发不同的分桶扫描，各个查询之间的IO影响较小（尤其当不同桶分布在不同磁盘上时），所以这种方式适合高并发的点查询场景。

分桶的数量理论上没有上限。

关于 Partition 和 Bucket 的数量和数据量的建议。

• 一个表的 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个分片。

表的数据量可以通过 show data 命令查看，结果除以副本数，即表的数据量。

多列分区

Doris 支持指定多列作为分区列，示例如下：

PARTITION BY RANGE(`date`, `id`)(    PARTITION `p201701_1000` VALUES LESS THAN ("2017-02-01", "1000"),    PARTITION `p201702_2000` VALUES LESS THAN ("2017-03-01", "2000"),    PARTITION `p201703_all`  VALUES LESS THAN ("2017-04-01"))

* p201701_1000:    [(MIN_VALUE,  MIN_VALUE), ("2017-02-01", "1000")   )* p201702_2000:    [("2017-02-01", "1000"),  ("2017-03-01", "2000")   )* p201703_all:     [("2017-03-01", "2000"),  ("2017-04-01", MIN_VALUE))

注意，最后一个分区用户缺省只指定了 date 列的分区值，所以 id 列的分区值会默认填充 MIN_VALUE。结果如下：​​​​​​​

* 数据  -->  分区* 2017-01-01, 200     --> p201701_1000* 2017-01-01, 2000    --> p201701_1000* 2017-02-01, 100     --> p201701_1000* 2017-02-01, 2000    --> p201702_2000* 2017-02-15, 5000    --> p201702_2000* 2017-03-01, 2000    --> p201703_all* 2017-03-10, 1       --> p201703_all* 2017-04-01, 1000    --> 无法导入* 2017-05-01, 1000    --> 无法导入

动态分区

    动态分区是在Doris0.12版本加入的功能。旨在对表级别的分区实现生命周期管理(TTL），减少用户的使用负担。

     目前实现了动态添加分区及动态删除分区的功能

 原理：

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

    在实现方式上，FE会启动一个后台子线程，根据fe.conf中dynamic_partition_enable及dynamic_partition_check_interval_seconds参数决定线程是否启动以及该线程的调度频率。每次调度时，会在注册表中读取动态分区表的属性。

参数

（1）开启动态分区功能，可以在fe.conf中设置dynamic_partition_enable=true，也可以使用命令进行修改。使用命令进行修改，并dynamic_partition_check_interval_seconds调度时间设置为5秒，意思就是每过5秒根据配置刷新分区。我这里做测试设置为5秒，真实场景可以设置为12小时。​​​​​​​

root@doris1:/opt/module# curl --location-trusted -u root:123456 -XGET http://doris1:8030/api/_set_config?dynamic_partition_enable=trueroot@doris1:/opt/module# curl --location-trusted -u root:123456 -XGET http://doris1:8030/api/_set_config?dynamic_partition_check_interval_seconds=5mysql> ADMIN SET FRONTEND CONFIG ("dynamic_partition_enable" = "true");mysql> ADMIN SET FRONTEND CONFIG ("dynamic_partition_check_interval_seconds"="5");​​​​​​​

（2）创建一张调度单位为天，不删除历史分区的动态分区表​​​​​​​

create table student_dynamic_partition1(id int,time date,name varchar(50),age int)duplicate key(id,time)PARTITION BY RANGE(time)()DISTRIBUTED BY HASH(id) buckets 10PROPERTIES("dynamic_partition.enable" = "true","dynamic_partition.time_unit" = "DAY","dynamic_partition.end" = "3","dynamic_partition.prefix" = "p","dynamic_partition.buckets" = "10", "replication_num" = "1" );

查看分区表情况 SHOW DYNAMIC PARTITION TABLES，更新最后调度时间
使用命令查看表下的所有分区show partitions from student_dynamic_partition1;