Doris 建表指导

c的掘金之旅
785 阅读12分钟

为方便阅读，可直接查看各个TL;DR，或者全文搜索“注意点”

Doris 采用 MySQL 协议进行通信，语法和MySQL也类似，所以在建表规范上大同小异，只是多了一些需额外关注的特性。

CREATE TABLE 语法

【必看】举个栗子

我们将建表语句拆分一下，里面有一些我们需要关注的问题，我们带着这些问题寻找答案。

-- 绿色加粗部分将SQL拆分
-- 绿色部分为 问题

CREATE TABLE IF NOT EXISTS example_db.expamle_list_tbl
-- 列信息  分为Key 和 value 两种列，如何理解Key 和 value呢？
(   -- 以下列为 Key
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `timestamp` DATETIME NOT NULL COMMENT "数据灌入的时间戳",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    -- 以下列为 value
    `age` SMALLINT REPLACE COMMENT "用户年龄",
    `sex` TINYINT REPLACE COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    -- value 列中的 SUM、MAX 为聚合数据模型的 显式 定义
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
-- 引擎类型，大部分场景选择olap即可
ENGINE=olap
-- 数据模型 
AGGREGATE Key  -- 如何选择数据模型？
            (`user_id`, `date`, `timestamp`,`city`) -- 使用哪些列作为Key？
-- partition 分区（如果没需求可以不设置） 
PARTITION BY -- 如何选择 partition 分区类型？
        LIST(`city`)  -- 使用哪些Key列 进行分区？
(
    PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"),
    PARTITION `p_usa` VALUES IN ("New York", "San Francisco"),
    PARTITION `p_jp` VALUES IN ("Tokyo") -- 分区范围如何设置？
)
-- bucket 分桶
DISTRIBUTED BY HASH(`user_id`) -- 使用哪些Key列 进行分桶？
BUCKETS 16  -- 分桶数量如何设置？
-- 表属性
PROPERTIES
(
    "replication_num" = "3",
    "storage_medium" = "SSD",
    "storage_cooldown_time" = "2018-01-01 12:00:00"
);

Doris 建表需额外关注的特性

  • 数据类型

    • 支持的数据类型与 MySQL 略有差异

  • 数据模型

    • 简单理解为不同数据模型下，在表存储数据时，历史数据是否会根据条件被更新 。 例如：聚合模型会根据聚合函数(eg：sum)更新数据，唯一模型会根据主键更新数据。

  • partition 分区

    • 简单理解为 将表水平划分为若干个partition 分区。 类似MySQL 水平分表。

  • bucket 分桶

    • 根据某些 column 对数据进行 Hash 物理划分；类似 Hive 的分区；

    • 如果设置了partition 分区，则分桶作用在每个partition上，如果没有设置partition，则分桶作用在整张表上。

    • 分区和分桶，组成了二维分区，更利于对数据划分。

帮助整体理解 partition 分区 和 bucket 分桶

  下图可以对应为mysql表：

  • Table是 MySQL 业务主表
  • partition 分别是 MySQL 的分表
  • Bucket 分别是 各自分表的分区

数据模型的选择

👉 TL;DR

怎么理解数据模型？

  Doris 中多了模型的概念，可以理解不同数据模型下，在表存储数据时，历史数据是否会根据条件被更新 。

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

如何选择数据模型？

  在我们的实时数仓场景中，用到较多的是唯一模型、聚合模型。

  • 根据多个 Doris基础表，进行联合查询（join）。这种场景下，使用唯一模型 。
  • 针对比较固定的汇总类指标查询，没有过多的联合查询。这种场景下，使用聚合模型。

  目前我们大部分场景使用的是唯一模型。

👇🏻数据模型的简单对比。

数据模型场景特点建表SQL关键字
聚合模型（AGGREGATE）- 业务方汇总类查询场景

  • 不需要明细数据场景 | 字段类型后 有sum、max、replace等函数，数据写入时，会被聚合 | AGGREGATE Key(user_id, date) | | 唯一模型（Uniq） | - 唯一主键约束场景

  • 更新场景场景

  • 需要明细数据 | 指定主键，数据写入时，会被更新，不会被聚合， | UNIQUE Key(user_id, username) | | 明细模型（DUPLICATE） | - 保留原始数据场景 | 数据完全按照导入文件中的数据进行存储 ，不会有任何聚合。即使两行数据完全相同，也都会保留。 | DUPLICATE Key(timestamp, type) |

聚合模型（AGGREGATE）

特点：字段类型后 有sum、max、replace等函数，数据写入时，会被聚合

场景：

  1. 业务方进行汇总类查询，比如sum、count、 max等类型的查询；

  2. 不需要召回原始的明细数据；

  3. 老数据不会被频繁更新，只会追加新数据。

建表语句表现

AGGREGATE Key(user_id, date)

在建表时, 只要给指标列的定义指明聚合函数, 就会启用聚合模型;

唯一模型（Uniq）

特点：指定主键，数据不会被聚合，数据写入时，会被更新

可以理解为特殊的聚合模型，除主键外，其他value 的聚合函数都是 REPLACE。

场景：

  1. 没有聚合需求，只需保证主键唯一性。

  2. 根据主键更新数据的场景；

  3. 需要进行实时数据分析，例如和其他表join。

建表语句表现

UNIQUE Key(user_id, username)

明细模型（DUPLICATE）

特点：数据完全按照导入文件中的数据进行存储，不会有任何聚合。即使两行数据完全相同，也都会保留。

场景特点：

  1. 需要保留原始的数据（例如原始日志，原始操作记录等）来进行分析；

  2. 查询方式灵活, 不局限于预先定义的分析方式, 传统的预聚合方式难以命中;

  3. 数据更新不频繁。导入数据的来源一般为日志数据或者是时序数据, 以追加写为主要特点, 数据产生后就不会发生太多变化。

建表语句表现

DUPLICATE Key(timestamp, type)，DUPLICATE Key只是用来指明底层数据按照那些列进行排序。

column 的设置

如何理解 Key 和 Value?

  Doris 里将列分为了 Key列 和 Value列。

  可以大致理解为，数据模型中的列值不会更新的列是Key列，其他可被更新的列为Value列。

  在聚合模型中，Key列相同的数据，value列会根据各自的聚合函数被更新。

  例如： a、b、c 三列，a、b为Key 列，c为value列并被sum作用。注意 变化

-- 插入数据
caozhen test 30 
-- 查询数据
caozhen test 30



-- 插入数据
caozhen test 20
-- 查询数据
caozhen test 50
 


-- 插入数据
caozhen test 70 
-- 查询数据
caozhen test 120

  在唯一模型中，Key列相同的数据，value列会被覆盖。

  在明细模型中，由于所有列不会被更新，Key列只是用来指明底层数据按照那些列进行排序。

注意点：

  • 【强制】Key 列必须在所有 Value 列之前。

  • 【强制】列信息中的Key 列，与数据模型关键字中使用的 Key 列，必须保持顺序一直。

    • CREATE TABLE IF NOT EXISTS example_db.expamle_list_tbl
-- 列信息  
(   -- 以下列为 Key
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    -- 以下列为 value
    `age` SMALLINT REPLACE COMMENT "用户年龄",
    `sex` TINYINT REPLACE COMMENT "用户性别"
)
ENGINE=olap
-- 数据模型 
AGGREGATE Key  (`user_id`, `date`) -- 这里的 Key 列顺序要和列信息的顺序一致， 
-- ❌错误示范
AGGREGATE Key  (`date`, `user_id`) 

DISTRIBUTED BY HASH(`user_id`) BUCKETS 16  
PROPERTIES
(
    "replication_num" = "3",
    "storage_medium" = "SSD",
    "storage_cooldown_time" = "2018-01-01 12:00:00"
);

  • 【建议】尽量选择整型类型。因为整型类型的计算和查找比较效率远高于字符串。

  • 【建议】字符串类型尽量定义 VARCHAR(xxx长度) 类型，长度遵循够用即可。

  • 【强制】所有列的总字节长度（包括 Key 和 Value）不能超过 100KB。

数据类型参考 官网

partition 分区的设置

是否要设置partition ?

  在我们实时数仓场景中，并且是一些长周期的场景（教育、房产），可能对于时间范围更敏感些。例如 一般都会按照时间去筛选数据，即便不能做到将数据分到一个分区，也会减少扫描的数据量。所以可以用range partition。

  但是，对于数据量不是很大的表，也可以不设置 partition分区。

如何选择 partition 分区类型？ 使用哪些Key列 进行分区？

  前面说 partition分区 类似 MySQL 中的水平分表。MySQL水平分表方案一般有：按时间范围、hash分表；

  类似的，Doris 的 partition分区也有两种类型：按照时间范围(range)、按照固定枚举值(list) 。

  很明显的是，分表或者分区是为了减少每次扫描的数据量，也就是适用数据量大的场景 。

  • range partition

    • 业务上对时间范围较敏感的场景，例如，一张订单表，需求上经常会计算一定时间范围的数据
    • 例如选用Key 列中的时间列

  • list **partition

    • 业务上对某些列的 枚举值比较敏感的场景，例如，一张用户画像表，需求上经常会按照用户所在城市去筛选数据

注意点： 结合语法看以下规则

  • 【强制】Partition 列可以为一或多列，分区类必须为 Key 列。

  • 【强制】写分区值时，要加双引号， 例如 VALUES ["2017-04-01", "2017-04-30")

  • 【强制】定义分区时，范围不能重叠

  • 【建议】 range partition 列类型一般为时间类型

  • 【可选】Partition列也可以设置（如果没有需求）

partition 语法：

-- range类型 partition
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")
)
-- 或者
PARTITION BY RANGE(`date`)
(
    PARTITION `p201701` VALUES ["2017-02-01", "2017-02-28"),
    PARTITION `p201702` VALUES ["2017-03-01", "2017-03-31"),
    PARTITION `p201703` VALUES ["2017-04-01", "2017-04-30")
)


-- list类型 partition
PARTITION BY LIST(`city`)
(
    PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"),
    PARTITION `p_usa` VALUES IN ("New York", "San Francisco"),
    PARTITION `p_jp` VALUES IN ("Tokyo")
)

bucket 分桶的设置

使用哪些Key列 进行分桶？

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

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

  • 【建议】优先选择 高基数 的Key 列，避免严重的数据倾斜。

  • 【建议】可以在Key列中优先选择where条件后的列，group by的列、join条件的列

分桶数量如何设置？

  没有固定的公式，看根据集群的磁盘数量和表的数据量决定。

  • 【建议】尽量保证一个分桶的数据量在****2G以内 (官方建议1-10G) 。

    • 注意需要考虑未来数据增长
    • 如果设置了partition 分区，分桶 数据量 = 表数据量 / 分区个数 / 分桶个数
    • 如果没设置partition 分区，分桶 数据量 = 表数据量 / 分桶个数

  • 【建议】尽量比 BE 磁盘总数量 大****一些，例如是2倍， 方便后续扩容，分桶也能迁移到扩容机器

  举例：

  Doris 公共集群 有7台BE，每台BE 2块 SSD。

  A表是唯一模型表用户表，追踪到A表是来源于一张MySQL用户表，发现从source到doris后数据膨胀不明显，A表大概在1G左右，并且即便未来快速增长，A表大小可能也不会膨胀很大。

  设定为 18 个 分桶 。

注意点：

  • 【强制】 分桶 列可以是多列，但必须为 Key 列。

  • 【可选】分桶列可以和 Partition 列相同或不同。

bucket 语法：

DISTRIBUTED BY HASH(k1)   -- 按照哪些 column 分桶
BUCKETS 8         -- 分桶的数量

properties 表属性的设置

我们的场景中，暂时没有特别关注的变化属性。

可以使用以下模板

PROPERTIES (
  "replication_num" = "3",  -- 副本数量
  "in_memory" = "false",
  "storage_medium" = "SSD", -- BE 的数据存储目录选用什么磁盘
  "storage_format" = "V2"
);

Colocation Group 优化

使用

官网介绍

PROPERTIES属性中增加

"colocate_with" = "user_realtor", -- user_realtor为自定义名称，用来绑定一批使用 colocate group 优化的表

FAQ

建表时是否需要设置 Colocation Group：

如果可以判断这个表在业务上要以哪个Key进行 join，可以建表时设置；

如果无法判断，或者使用场景不符合local join，那么就不需要设置，未来使用时再设置即可。

如何对已经存在的表设置 Colocation Group？

  • 方案1：

直接通过命令设置doris.apache.org/zh-CN/admin…

  • 方案2：

假如通过方案1 设置报错（原因可能是Colocation Group 字段不属于 partition Key），可以采用以下步骤，

  • 1、新建Colocation Group new表
  • 2.1、停止写doris 任务
  • 2.2、通过 insert new表 select old表 插入数据
  • 3.1、重命名 new 表 为 old 表
  • 3.2、重启写doris 任务

这种方式会造成 短暂的doris数据不可用。

avatar
文章
阅读
粉丝