Doris进阶介绍（二）

Doris数据表设计

基本概念

在Doris中，数据都以关系表（Table）的形式进行逻辑上的描述

Row & Column

一张表包括行（Row）和列（Column）：Row，即用户的一行数据；Column，用于描述一行数据中不同的字段。

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

Tablet & Partition

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

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

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

字段类型

数据模型

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

Aggregate

        假设业务有如下数据表模式

建表

---

   CREATE TABLE IF NOT EXISTS test_db.example_site_visit

(

    `user_id` LARGEINT NOT NULL COMMENT "用户id",

    `date` DATE 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 "用户最小停留时间"

)

AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)

DISTRIBUTED BY HASH(`user_id`) BUCKETS 10

properties(

"replication_num"="1"

);

插入数据

insert into test_db.example_site_visit values\

(10000,'2017-10-01','北京',20,0,'2017-10-01 06:00:00' ,20,10,10),\

(10000,'2017-10-01','北京',20,0,'2017-10-01 07:00:00',15,2,2),\

(10001,'2017-10-01','北京',30,1,'2017-10-01 17:05:45',2,22,22),\

(10002,'2017-10-02','上海',20,1,'2017-10-02 12:59:12' ,200,5,5),\

(10003,'2017-10-02','广州',32,0,'2017-10-02 11:20:00',30,11,11),\

(10004,'2017-10-01','深圳',35,0,'2017-10-01 10:00:15',100,3,3),\

(10004,'2017-10-03','深圳',35,0,'2017-10-03 10:20:22',11,6,6);

注意：Insert into 单条数据这种操作在Doris里只能演示不能在生产使用，会引发写阻塞。

说明:

• 表中的REPLACE SUM MAX MIN 叫AggregationType （聚合类型），目前只有这四种聚合类型。
• 没有设置聚合类型的叫 key（维度列），设置了聚合类型的叫 value（指标列）。
• 当我们导入数据的时候, 会按照 key 对 value 使用他们自己的聚合类型进行聚合。
• 在同一个导入批次中的数据，对于 REPLACE 这种聚合方式，替换顺序不做保证。而对于不同导入批次中的数据，可以保证，后一批次的数据会替换前一批次。
• 经过聚合，Doris 中最终只会存储聚合后的数据。换句话说，即明细数据会丢失，用户不能够再查询到聚合前的明细数据了。
• 如果想要保留明细数据不让 doris 聚合, 则主要保证每条数据的 key 不一样就可以了。（多个key 中有一个不一样就行）

Unique

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

建表

---

CREATE TABLE IF NOT EXISTS test_db.user

(

    `user_id` LARGEINT NOT NULL COMMENT "用户id",

    `username` VARCHAR(50) NOT NULL COMMENT "用户昵称",

    `city` VARCHAR(20) COMMENT "用户所在城市",

    `age` SMALLINT COMMENT "用户年龄",

    `sex` TINYINT COMMENT "用户性别",

    `phone` LARGEINT COMMENT "用户电话",

    `address` VARCHAR(500) COMMENT "用户地址",

    `register_time` DATETIME COMMENT "用户注册时间"

)

UNIQUE KEY(`user_id`, `username`)

DISTRIBUTED BY HASH(`user_id`) BUCKETS 10

properties(

"replication_num"="1"

)

插入语句

insert into test_db.user values\

(10000,'wuyanzu','北京',18,0,12345678910,'北京朝阳区','2017-10-01 07:00:00'),\

(10000,'wuyanzu','北京',19,1,12345678910,'北京朝阳区','2017-10-01 08:00:00'),\

(10000,'zhangsan','北京',20,0,12345678910,'北京海淀区','2017-11-15 06:10:20');

这种表结构完全等价于下面的聚合模型。

---

CREATE TABLE IF NOT EXISTS test_db.user

(

    `user_id` LARGEINT NOT NULL COMMENT "用户id",

    `username` VARCHAR(50) NOT NULL COMMENT "用户昵称",

    `city` VARCHAR(20) REPLACE COMMENT "用户所在城市",

    `age` SMALLINT REPLACE COMMENT "用户年龄",

    `sex` TINYINT REPLACE COMMENT "用户性别",

    `phone` LARGEINT REPLACE COMMENT "用户电话",

    `address` VARCHAR(500) REPLACE COMMENT "用户地址",

    `register_time` DATETIME REPLACE COMMENT "用户注册时间"

)

AGGREGATE KEY(`user_id`, `username`)

DISTRIBUTED BY HASH(`user_id`) BUCKETS 10

        即 Unique 模型完全可以用聚合模型中的 REPLACE 方式替代。其内部的实现方式和数据存储方式也完全一样。这里不再继续举例说明。

Duplicate

在某些多维分析场景下，数据既没有主键，也没有聚合需求。因此，我们引入 Duplicate 数据模型来满足这类需求。举例说明

建表

---

CREATE TABLE IF NOT EXISTS test_db.example_log

(

    `timestamp` DATETIME NOT NULL COMMENT "日志时间",

    `type` INT NOT NULL COMMENT "日志类型",

    `error_code` INT COMMENT "错误码",

    `error_msg` VARCHAR(1024) COMMENT "错误详细信息",

    `op_id` BIGINT COMMENT "负责人id",

    `op_time` DATETIME COMMENT "处理时间"

)

DUPLICATE KEY(`timestamp`, `type`)

DISTRIBUTED BY HASH(`timestamp`) BUCKETS 10

properties(

"replication_num"="1"

);

插入语句

insert into test_db.example_log values\

('2017-10-01 08:00:05',1,404,'not found page', 101, '2017-10-01 08:00:05'),\

('2017-10-01 08:00:05',1,404,'not found page', 101, '2017-10-01 08:00:05'),\

('2017-10-01 08:00:05',2,404,'not found page', 101, '2017-10-01 08:00:06'),\

('2017-10-01 08:00:06',2,404,'not found page', 101, '2017-10-01 08:00:07');

        这种数据模型区别于 Aggregate 和 Unique 模型。数据完全按照导入文件中的数据进行存储，不会有任何聚合。即使两行数据完全相同，也都会保留。而在建表语句中指定的 DUPLICATE KEY，只是用来指明底层数据按照那些列进行排序。

在 DUPLICATE KEY 的选择上，我们建议适当的选择前 2-4 列就可以。

这种数据模型适用于既没有聚合需求，又没有主键唯一性约束的原始数据的存储。

---

建表示例

使用 CREATE TABLE 命令建立一个表（Table）。更多详细参数可以查看：

HELP CREATE TABLE;

建表语法：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [database.]table_name

    (column_definition1[, column_definition2, ...]

    [, index_definition1[, index_definition12,]])

    [ENGINE = [olap|mysql|broker|hive|es]]

    [key_desc]

    [COMMENT "table comment"];

    [partition_desc]

    [distribution_desc]

    [rollup_index]

    [PROPERTIES ("key"="value", ...)]

[BROKER PROPERTIES ("key"="value", ...)];

---

参数解

CREATE [EXTERNAL] TABLE:

EXTERNAL: 创建外部表，外部表的数据存储在外部存储系统中，如 HDFS。

[IF NOT EXISTS]:

如果表已经存在，则不执行创建操作，防止错误。

[database.]table_name:表的名称，可选的数据库名称。

column_definition:列定义，包含列名和数据类型，以及可选的列属性。

index_definition:索引定义，包含索引名称和索引列。

[ENGINE = [olap|mysql|broker|hive|es]]:存储引擎类型：es: 连接到 Elasticsearch。hive: 连接到 Hive 数据仓库。broker: 使用 Broker 进行数据导入。mysql: 连接到 MySQL 数据库。olap: 默认存储引擎，适用于 OLAP 场景。[key_desc]:键描述，定义表的主键或唯一键。

[COMMENT "table comment"]:表注释，提供对表的描述。

[partition_desc]:分区描述，定义表的分区策略。

[distribution_desc]:分布描述，定义数据在不同节点上的分布策略。

[rollup_index]:Rollup 索引，用于加速特定查询。

[PROPERTIES ("key"="value", ...)]:表属性，定义各种配置参数。

[BROKER PROPERTIES ("key"="value", ...)]:Broker 属性，用于外部数据导入时的配置

---

分区区别

        在 Doris 中，Range Partition 和 List Partition 是两种不同的分区方式，它们的主要区别在于数据如何划分到不同的分区中。以下是详细解释：

        分区依据：

                Range Partition：依据某一列的值范围进行分区，适合连续值。

                List Partition：依据某一列的一组具体值进行分区，适合离散值。

       分区定义方式：

                Range Partition：每个分区的定义方式是一个范围，如 VALUES LESS THAN。

                List Partition：每个分区的定义方式是一组具体值，如 VALUES IN。

        适用场景：

                Range Partition：适用于按时间、数值等连续范围进行分区的场景。

                List Partition：适用于按离散的类别或枚举值进行分区的场景

---

Range Partition (范围分区)

CREATE TABLE IF NOT EXISTS test_db.example_range_tbl
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE 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=OLAP
AGGREGATE KEY(`user_id`, `date`, `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 16
PROPERTIES
(
    "replication_num" = "1",
    "storage_cooldown_time" = "2024-01-01 12:00:00"
);

---

特点：

分区依据：通过某一列的值范围来进行分区。在这个示例中，是根据 date 列的范围来分区。

分区定义：每个分区包含一个值范围，如 p201701 包含所有日期小于 2017-02-01 的数据。

使用场景：适用于数据按时间、数值等连续范围进行分区的场景。例如按月、季度、年份分区的数据。

---

List Partition (列表分区)

CREATE TABLE IF NOT EXISTS test_db.example_list_tbl
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) NOT NULL 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=olap
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
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")
)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16
PROPERTIES
(
    "replication_num" = "1",
    "storage_cooldown_time" = "2024-01-01 12:00:00"
);

---

分区依据：通过某一列的值列表来进行分区。在这个示例中，是根据 city 列的值来分区。

分区定义：每个分区包含特定的一组值，如 p_cn 包含 Beijing、Shanghai 和 Hong Kong 等城市的数据。

使用场景：适用于数据按离散的类别或枚举值进行分区的场景。例如按国家、地区、部门等分区的数据。