Apache Doris 建表指南

学习目标

  • 掌握 Apache Doris 中的建表语法

  • 了解 Apache Doris 中的分区分桶机制

  • 了解 Apache Doris 中的多种建表模型

创建第一张表

MySQL 与 Apache Doris 建表区别

Apache Doris 兼容了 MySQL 5.7 协议,在建表时与 MySQL 稍有不同。

图 1 MySQL 与 Apache Doris 建表语句对比

如上图中,左面是 MySQL 中的建表语句,右面是 Apache Doris 中的建表语句。其中,字段和类型的语法 Apache Doris 与 MySQL 基本相同,区别主要有三个地方:

  • 在 Apache Doris 中,建表时需要指定使用的表模型与维度列,在上例中,DUPLICATE KEY 关键字后指定了 user_id, date 与 timestamp 为维度列;

  • 在 Apache Doris 中,建表时需要对数据进行分区操作。一般来说,MySQL 会用在联机事务系统中,通过创建索引,基本上可以满足单表上的简单点查操作。但在 Apache Doris 中,强烈建议建表时同时对数据进行分区,这样可以有效的帮助用户使用分区剪裁等优化手段。在上例中,根据 date 列进行了 RANGE 分区;

  • 在 Apache Doris 中,建表时需要在指定分桶的信息,包括选择分桶键与分桶的数量。在上例中,指定了 user_id 列为分桶键,分桶数为 16 个。

Apache Doris 中的列式存储

在 Apache Doris 中,数据以列的形式存储。一张表的列可以分成维度列与指标列。

图 2 Apache Doris 中的列式数据存储


其中,维度列用于分组和排序,指标列可以参与函数的聚合。例如,在表中指定维度列为 user_id,date 及 timestamp 时:

  • 可以通过这三列进行分组去重操作

  • 也可以在指标列上创建聚合操作,在维度列上进行分组,完成聚合

表中的数据是按照维度列进行排序的,在等值或者范围查询中,可以充分利用维度列的有序性,快速锁定需要检索的行。

Apache Doris 中的数据管理

数据的分区管理

分区简介

在 Apache Doris 中,建议在建表的时候对数据进行分区操作。

与 MySQL 的分区语法基本类似,Apache Doris 可以通过 PARTITION 关键字在建表时指定分区。

相比于 OLTP 业务,OLAP 业务会大量存储历史数据,数据量会成倍增长。建议可以通过分区的模式进行数据存储的优化,利用分区剪裁,减少查询需要扫描的数据量。目前在 Apache Doris 中支持 RANGE 和 LIST 分区。

图 3 Apache Doris 中的数据分区存储

在上例中,对 date 列进行了 RANGE 分区。按照日期创建了从无穷小到 2017-04-01 的三个分区。分区创建遵循左闭右开原则:

  • p201701 分区指定小于 02-01 的所有数据;

  • p201702 分区指定了从 02-01 到 03-01 的左闭右开分区

  • p201703 分区指定了从 03-01 到 04-01 的左闭右开分区

动态分区

在很多实际应用的场景中,数据的实效性尤为重要。需要动态的为新增的数据创建新的分区,并删除过期的分区。可以在 Apache Doris 中使用 PROPERTIES 关键字指定动态分区管理。通过动态分区的功能实现 Apache Doris 分区数据的生命周期管理(TTL)。

CREATE TABLE test.doris_tbl (
    user_id            INT           NOT NULL,
    date               DATE          NOT NULL,
    timestamp          DATETIME      NOT NULL,
    city               VARCHAR(20),
    age                INT,
    sex                INT,
    cost               INT           DEFAULT 0
)
DUPLICATE KEY(user_id, date, timestamp)
PARTITION BY RANGE(date) ()
DISTRIBUTED BY HASH(user_id)
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "32"
);

在 PROPERTIES 中,动态分区的规则参数都以 dynamic_partition 为前缀:

  • “dynamic_partition.enable” = “true” 表示开启动态分区

  • “dynamic_partition.time_unit” = “DAY” 表示分区调度的单位为天,按天滚动删除过期分区

  • “dynamic_partition.start” = “-7” 表示动态分区的起始偏移,-7 代表保留一周前的数据

  • “dynamic_partition.end” = “3” 表示动态分区的结束偏移,3 代表预先创建三天内的分区

  • “dynamic_partition.prefix” = “p” 代表分区名字的前缀

  • “dynamic_partition.buckets” = “32” 代表每个分区的 Buckets 数量

通过设置动态分区,可以实现分区数据的生命周期管理。如当前日期为 03-18,那么如下图所示,会删除 03-17 之前的数据,并且会提前创建 03-19 的分区。

图 4 动态分区效果

数据的分桶管理

分桶简介

分区的下一级是分桶,Apache Doris 采用 hash 算法作为分桶算法,以实现分区下的数据均衡的分布在不同的节点上,避免了热点查询的问题。同一分区内,分桶键 hash 值相同的数据会形成数据分片(tablet),tablet 是多副本冗余存储的最小单位,也是调度进程进行副本管理的最小单位。

使用高基数的列来作为分桶键是一个不错的选择,比如可以用唯一性相对较高的 ID 列进行分桶,对于 sex 这样选择性不高的列,可以通过组合列的形式提高选择性。这样保证数据在各个bucket中尽可能均衡,如果碰到数据倾斜严重的,数据可以使用多列作为分桶键。

图 5 数据分桶

基于分区分桶的高并发查询

Apache Doris 提供优秀的高并发查询能力。通过合适的分区分桶策略及一定规模的节点数量,可以支撑上万的查询并发量。在经过数据分区分桶后,数据的指向性有了显著的提高。良好的分区分桶规则可以将业务逻辑相关的数据集中存储。当分区分桶的规则能够覆盖大部分的查询条件时,可以利用分区剪裁、分桶剪裁及前缀索引等手段,有效减少数据的扫描量,精准定位并抓取查询数据。Apache Doris 提供了多种数据存储优化手段以避免全表扫描,减少查询对硬件资源的消耗,从而节省出更多的硬件资源支撑高并发的业务查询。

在下的例子中,良好的分区分桶策略可以过滤掉大量的数据。谓词 date_id = “2020-03-23” 可以定位到分区 p20200323,谓词 site_id = 1 能定位到该分区下的 bucket_1。

图 6 基于分区分同的高并发查询

分桶键的选择

一般来说,为了能够让分区分桶的策略尽可能的过滤掉更多的数据,会让分区分桶键覆盖 WHERE 语句的大部分谓词条件。如下面的查询中,通常会选择 site_id 列作为分桶列。

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

但有时候,site_id 列可能会存在数据分布不均的问题,这样的分桶方式可能会产生数据倾斜,造成局部数据过热的情况。可以通过组合分桶的方式,将数据打散:

CREATE TABLE demo.test_tbl(
    date_id      DATE,
    site_id      INT,
    city_code    SMALLINT,
    user_name    VARCHAR(32)  DEFAULT '',
    pv           BIGINT
)
DUPLICATE KEY(date_id, site_id, city_code)
PARTITION BY 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"))
DISTRIBUTED BY HASH(site_id, city_code) BUCKETS 10

如下图中,在使用单一列 site_id 作为分桶键时,大量的数据集中在 bucket_4 中。但通过使用复合键 site_id 与 city_code 作为分桶键。

图 7 复合分桶键均衡数据倾斜问题

分桶键数量的确定

单个 Tablet 的数据量理论上没有上下界,但建议在 1G - 10G 的范围内。如果单个 Tablet 数据量过小,则数据的聚合效果不佳,且元数据管理压力大。如果数据量过大,则不利于副本的迁移、补齐,且会增加 Schema Change 或者 Rollup 操作失败重试的代价(这些操作失败重试的粒度是 Tablet)。

例如:

对照文本数据(csv),Apache Doris 的压缩比在 0.3 ~ 0.5 左右(以下按 0.5 计算,千进制)。假设 1T 的 csv 文件导入 Apache Doris,分为 30 个均匀的分区:

  • 一个分区承担的 CSV 文本数据量为 1T / 30 = 33 GB

  • 单一副本按照 0.5 的压缩比存入 Doris,文件大小为 33 GB * 0.5 = 16.5 GB

  • 按照建议,一个 tablet 规划 1GB-10GB(取 2G),需要 16.5 / 2 = 8 个左右分桶

如果是 MySQL 一主两从的模式,可以将 MySQL 中的数据对应换算成文本文件的大小,按压缩比 0.7 计算(经验值),再按照上面的步骤计算出 Apache Doris 中的分桶数量。

自动分桶

在 Apache Doris 1.2.2 后,可以在建表的时候指定自动分桶,无需手动指定分桶键的数量。具体语法如下

-- 旧版本指定分桶个数的创建语法
DISTRIBUTED BY HASH(site) BUCKETS 20

-- 新版本使用自动分桶推算的创建语法
DISTRIBUTED BY HASH(site) BUCKETS AUTO
properties("estimate_partition_size" = "100G")

新增的配置参数 estimate_partition_size 表示一个单分区的数据量。该参数是可选的,如果没有给出则 Doris 会将 estimate_partition_size 的默认值取为 10GB。

Apache Doris 中的表模型

Apache Doris 中的数据模型

在 Aapache Doris 建表时,需要指定字段信息、分区信息和分桶信息,同时也需要指定模型信息。在 Apache Doris 中,支持三种数据建模方式:

  • DUPLICATE 模型主要用于存储明细数据

  • AGGREGATE 模型主要用于预聚合场景,数据插入后即完成聚合

  • UNIQUE 模型主要用于处理数据的更新,实现 UPSERT 功能

图 8 建表模型

DUPLICATE 模型

明细模型主要用于需要保留原始数据的场景,如日志分析,用户行为分析等场景。

图 9 DUPLICATE 模型


在明细模型中:

  • 表中的排序键可以存在重复的数据行

  • 和 MySQL 类似,插入的数据行与表中存储的数据行是意义对应的,用户可以找回全部的历史数据

  • 如上⾯的例⼦中,有多少条 insert 的 value 值,表中就新增多少⾏数据

  • 明细模型底层使⽤ LSM Tree 数据结构进⾏存储。LSM Tree 是⼀个写友好的数据结构,在 Apache Doris 数据存储层,选择了⼀个两层结构的 LSM Tree,减少了 compaction 对系统的压⼒,也减少了 write stall 的影响

AGGREGATE 模型

在数据分析领域,有很多需要对数据进行统计和汇总操作的场景,如需要分析网站和 APP 访问流量,统计用户的访问总时长,访问总次数。或者像厂商需要为广告主提供广告点击的总流量,展示总量,消费统计等指标。在这些不需要召回明细数据的场景,通常可以使用聚合模型。

图 10 AGGREGATE 模型

UNIQUE 模型

在某些多维分析场景下,用户更关注的是如何保证 Key 的唯一性,即如何获得 Unique Key 唯一性约束。因此,Apache Doris 引入了 Unique 的数据模型。

图 11 UNIQUE 模型


在 UNIQUE 模型中:

  • 会根据表中的主键进⾏ upsert 操作,对于已有的主键做 update 操作,更新 value 列,没有的主键做 insert 操作

  • 在上⾯的例⼦中,新写⼊的主键为 (1, 100) 的⾏会替换原表中 (1, 100) 的⾏

Apache Doris 中的排序键与稀疏索引

什么是稀疏索引

Short Key Index前缀索引,是在key(AGGREGATE KEY、UNIQ KEY 和 DUPLICATE KEY)排序的基础上,实现的一种根据给定前缀列,快速查询数据的索引方式。这里Short Key Index索引也采用了稀疏索引结构,在数据写入过程中,每隔一定行数,会生成一个索引项。这个行数为索引粒度默认为1024行。

排序列注意事项

Apache Doris 表中的数据分为 key 与 value。在上面例子中,三种模型都使用了 (siteid, city) 两列作为表的排序键(key)。以上面的列子为例,排序列需要注意两点:

  • 排序列的定义必须出现在建表语句中其他列的定义之前。

    • 例如:排序列的顺序可以是 (siteid, city),或者是 (siteid, city, username),但不能是 (city, username) 或者是 (siteid, city, pv)
  • 排序列的顺序是由 CREATE TABLE 中的顺序决定的

    • 排序列的顺序可以是 (siteid, city),或者是 (siteid, city, username),但不能是(city, siteid) 或者是 (city, siteid, username)

图 12 排序列注意事项

稀疏索引的限制

稀疏索引是 Apache Doris 在范围查询是的一个非常重要的优化手段。

由于稀疏索引被放在缓存中,一旦稀疏索引过大,会占用大量的内存。

为了避免这样的情况,Apache Doris 对稀疏索引做出了一些限制:

  • 对于稀疏索引大小的限制

    • 稀疏索引的列只能是排序建的前缀

    • 稀疏索引的列不能超过 3 个

    • 稀疏索引字节数不能超过 36 byte,超过会截断

  • 与字段类型相关的限制条件

    • 稀疏索引列不能是 FLOAT 或者是 DOUBLE 类型

    • 如果是 VARCHAR 类型也只能出现一次,并且一定要放在末尾

    • 在末尾列是 CHAR 或者是 VARCHAR 类型时,超过 36 byte 稀疏索引会被截断

    • 如果在 PROPERTIES 中指定了 short key 的大小时,可以突破上面的限制

排序键的选择

为了加速查询,Apache Doris 会在排序列上自动创建稀疏索引。在范围查找时,稀疏索引(shortkey index)可以帮快速的定位到起始的目标行。当排序列非常多的时候,Apache Doris 会自动在稀疏索引上加入一些限制条件,确保稀疏索引内容较小,可以被缓存到内存中。

基于上面稀疏索引的原理,可以通过几个案例来看一下稀疏索引的加速效果

  • 如果的排序键全都被使用了,那么加速效果也是最好的
  • 如果只使用了包含先到列在内的几个 key,加速效果也比较好
  • 在这种情况下,一般要保证查询使用了先导列,如果是跳跃使用其他的列,也会起到效果
  • 如果在查询中,没有使用到先导列,那么可能获得不了一个较好的效果
    所以可以总结一下在选择排序键的一些规则:
  • 选择性(区分度)高的列放在前面,作为先导列
  • 查询条件中最常被使用的列放在前面,作为先导列
  • 尽量让分区间覆盖尽可能多的查询条件
4 个赞