Doris 开发实践建表，模型选择，分区使用，函数使用问题汇总

最新推荐文章于 2024-04-30 16:44:46 发布

HD0do(迪答数据)

最新推荐文章于 2024-04-30 16:44:46 发布

阅读量2.1k

点赞数

分类专栏： Doris 文章标签：实时大数据数据库

本文链接：https://blog.csdn.net/HD0do/article/details/123802816

版权

Doris 专栏收录该内容

6 篇文章 5 订阅

订阅专栏

建表注意事项

1.建表时候主键需要手动指定not null语句

2.主键指定的顺序需要按照建表的语句的字段顺序

3.建表中comment语句放置的位置也要注意，否则会报错。


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=olap
AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
comment '聚合模型'
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" = "3",
    "storage_medium" = "SSD",
    "storage_cooldown_time" = "2018-01-01 12:00:00"
);

数据引擎的选择：

Doris和clickhouse不同，clickhouse有mergetree家族，log等表引擎等多中数据引擎，也是clickhouse的一个特色。

但是Doris则不同Doris主要是使用olap数据引擎，也是Doris建表默认指定的一种数据引擎，其他映射mysql等数据只是一种延伸。但是对应的Doris中使用的则是数据模型

数据模型

对于数据模型如何选择和使用可以参考文章:

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

分区

而在分析型数据库，数据量通常是比较大，所以根据需要字段进行分区是十分重要的，对于Doris来说有单分区，复合分区，以及动态分区，同时也会在涉及分桶的设计。

分区分桶介绍

Doris支持支持单分区和复合分区两种建表方式。

在复合分区中：

第一级称为 Partition，即分区。用户可以指定某一维度列作为分区列（当前只支持整型和时间类型的列），并指定每个分区的取值范围。
第二级称为 Distribution，即分桶。用户可以指定一个或多个维度列以及桶数对数据进行 HASH 分布。

以下场景推荐使用复合分区

有时间维度或类似带有有序值的维度，可以以这类维度列作为分区列。分区粒度可以根据导入频次、分区数据量等进行评估。
历史数据删除需求：如有删除历史数据的需求（比如仅保留最近N 天的数据）。使用复合分区，可以通过删除历史分区来达到目的。也可以通过在指定分区内发送 DELETE 语句进行数据删除。
解决数据倾斜问题：每个分区可以单独指定分桶数量。如按天分区，当每天的数据量差异很大时，可以通过指定分区的分桶数，合理划分不同分区的数据,分桶列建议选择区分度大的列。

用户也可以不使用复合分区，即使用单分区。则数据只做 HASH 分布。

下面以聚合模型为例，分别演示两种分区的建表语句。

#单分区

建立一个名字为 table1 的逻辑表。分桶列为 siteid，桶数为 10。

这个表的 schema 如下：

siteid：类型是INT（4字节）, 默认值为10
citycode：类型是SMALLINT（2字节）
username：类型是VARCHAR, 最大长度为32, 默认值为空字符串
pv：类型是BIGINT（8字节）, 默认值是0; 这是一个指标列, Doris内部会对指标列做聚合操作, 这个列的聚合方法是求和（SUM）

建表语句如下:

CREATE TABLE table1
(
    siteid INT DEFAULT '10',
    citycode SMALLINT,
    username VARCHAR(32) DEFAULT '',
    pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(siteid, citycode, username)
DISTRIBUTED BY HASH(siteid) BUCKETS 10
PROPERTIES("replication_num" = "1");

#复合分区

建立一个名字为 table2 的逻辑表。

这个表的 schema 如下：

event_day：类型是DATE，无默认值
siteid：类型是INT（4字节）, 默认值为10
citycode：类型是SMALLINT（2字节）
username：类型是VARCHAR, 最大长度为32, 默认值为空字符串
pv：类型是BIGINT（8字节）, 默认值是0; 这是一个指标列, Doris 内部会对指标列做聚合操作, 这个列的聚合方法是求和（SUM）

我们使用 event_day 列作为分区列，建立3个分区: p201706, p201707, p201708

p201706：范围为 [最小值, 2017-07-01)
p201707：范围为 [2017-07-01, 2017-08-01)
p201708：范围为 [2017-08-01, 2017-09-01)

注意区间为左闭右开。

每个分区使用 siteid 进行哈希分桶，桶数为10

建表语句如下:

CREATE TABLE table2
(
    event_day DATE,
    siteid INT DEFAULT '10',
    citycode SMALLINT,
    username VARCHAR(32) DEFAULT '',
    pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(event_day, siteid, citycode, username)
PARTITION BY RANGE(event_day)
(
    PARTITION p201706 VALUES LESS THAN ('2017-07-01'),
    PARTITION p201707 VALUES LESS THAN ('2017-08-01'),
    PARTITION p201708 VALUES LESS THAN ('2017-09-01')
)
DISTRIBUTED BY HASH(siteid) BUCKETS 10
PROPERTIES("replication_num" = "1");

表建完之后，可以查看 example_db 中表的信息:

MySQL> SHOW TABLES;
+----------------------+
| Tables_in_example_db |
+----------------------+
| table1               |
| table2               |
+----------------------+
2 rows in set (0.01 sec)

MySQL> DESC table1;
+----------+-------------+------+-------+---------+-------+
| Field    | Type        | Null | Key   | Default | Extra |
+----------+-------------+------+-------+---------+-------+
| siteid   | int(11)     | Yes  | true  | 10      |       |
| citycode | smallint(6) | Yes  | true  | N/A     |       |
| username | varchar(32) | Yes  | true  |         |       |
| pv       | bigint(20)  | Yes  | false | 0       | SUM   |
+----------+-------------+------+-------+---------+-------+
4 rows in set (0.00 sec)

MySQL> DESC table2;
+-----------+-------------+------+-------+---------+-------+
| Field     | Type        | Null | Key   | Default | Extra |
+-----------+-------------+------+-------+---------+-------+
| event_day | date        | Yes  | true  | N/A     |       |
| siteid    | int(11)     | Yes  | true  | 10      |       |
| citycode  | smallint(6) | Yes  | true  | N/A     |       |
| username  | varchar(32) | Yes  | true  |         |       |
| pv        | bigint(20)  | Yes  | false | 0       | SUM   |
+-----------+-------------+------+-------+---------+-------+
5 rows in set (0.00 sec)

注意事项：

上述表通过设置 replication_num 建的都是单副本的表，Doris建议用户采用默认的 3 副本设置，以保证高可用。
可以对复合分区表动态的增删分区。详见 HELP ALTER TABLE 中 Partition 相关部分。
数据导入可以导入指定的 Partition。详见 HELP LOAD。
可以动态修改表的 Schema。
可以对 Table 增加上卷表（Rollup）以提高查询性能，这部分可以参见高级使用指南关于 Rollup 的描述。
表的列的Null属性默认为true，会对查询性能有一定的影响。

分区Doris使用参考

函数使用

1.日期函数

select now();
#2022-03-28 22:02:07

select date_sub(now(),1);
#2022-03-27 22:02:07

求取分钟转换：

select minutes_diff('2020-12-25 22:00:00','2020-12-25 21:00:00');

+-----------------------------------------------------------+
| months_diff('2020-12-25 00:00:00', '2020-05-25 00:00:00') |
+-----------------------------------------------------------+
|                                                         7 |
+-----------------------------------------------------------+
1 row in set (0.02 sec)

官网参考