Doris概念及常用操作

码客说

发布于 2023-09-22 08:38:54

1.2K0

发布于 2023-09-22 08:38:54

文章被收录于专栏：码客

Doris

个人更推荐Doris

字段类型

数字

TINYINT 1字节范围：-2^7 + 1 ~ 2^7 - 1
SMALLINT 2字节范围：-2^15 + 1 ~ 2^15 - 1
INT 4字节范围：-2^31 + 1 ~ 2^31 - 1
BIGINT 8字节范围：-2^63 + 1 ~ 2^63 - 1 对应Java中的Long
LARGEINT 16字节范围：-2^127 + 1 ~ 2^127 - 1
FLOAT 4字节支持科学计数法
DOUBLE 12字节支持科学计数法
DECIMAL[(precision, scale)] 16字节,保证精度的小数类型。默认是DECIMAL(10, 0)
- precision: 1 ~ 27
- scale: 0 ~ 9
- 其中整数部分为 1 ~ 18
- 不支持科学计数法

Java

日期

DATE 3字节范围：0000-01-01 ~ 9999-12-31
DATETIME 8字节范围：0000-01-01 00:00:00 ~ 9999-12-31 23:59:59

字符串

CHAR[(length)] 定长字符串。长度范围：1 ~ 255。默认为1
VARCHAR[(length)] 变长字符串。长度范围：1 ~ 65533
STRING 变长字符串，0.15版本支持，最大支持2147483643 字节（2GB-4），长度还受be 配置string_type_soft_limit, 实际能存储的最大长度取两者最小值。只能用在value 列，不能用在 key 列和分区、分桶列。

其他

BOOLEAN 与TINYINT一样，0代表false，1代表true
HLL 1~16385个字节 hll列类型，不需要指定长度和默认值、长度根据数据的聚合程度系统内控制，并且HLL列只能通过配套的hll_union_agg、Hll_cardinality、hll_hash进行查询或使用
BITMAP bitmap列类型，不需要指定长度和默认值。表示整型的集合，元素最大支持到2^64 - 1

数据模型

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

聚合模型：Aggregate（聚合，合计）模型，表中key值不重复，对于插入的数据数据按照key值对value值进行聚合函数合并。
更新模型：UNIQUE 模型，聚合类型的特殊情况，key满足唯一性，最新插入的数据替换掉对应key的数据行。
明细模型：Duplicate（重复，复制）模型，表中的Key值（类似关系模型中的主键）可以重复，和插入数据行一一对应。

注意

不设置模型的时候默认使用Duplicate模型。

Duplicate、Aggregate、Unique 模型，都会在建表指定 key 列。

然而实际上是有所区别的：

对于 Duplicate 模型，表的key列，可以认为只是 “排序列”，没有唯一标识的作用。
而 Aggregate、Unique 模型这种聚合类型的表，key 列是兼顾 “排序列” 和 “唯一标识列”，是真正意义上的“ key 列”。

我们看一个配置

CREATE TABLE IF NOT EXISTS zdb.t_student (
  `id` BIGINT NOT NULL COMMENT "用户id",
  `name` VARCHAR(255) COMMENT "用户名",
  `subject` VARCHAR(255) COMMENT "科目",
  `score` BIGINT COMMENT "分数"
) 
DUPLICATE KEY(`id`) 
DISTRIBUTED BY HASH(`name`) BUCKETS 1 
PROPERTIES (
  "replication_allocation" = "tag.location.default: 1"
);

其中

DUPLICATE KEY() 设置使用的模型和Key，使用Unique 模型时可以省略。
DISTRIBUTED BY HASH() BUCKETS 1，分桶配置不能省略，可以设置为DISTRIBUTED BY ... BUCKETS auto。
PROPERTIES()配置，不能省略。

聚合(AGGREGATE模型)

特征

聚合模型必须有维和度量，维度全部相同的度量值会自动合并。

创建表

CREATE TABLE IF NOT EXISTS zdb.t_user01 (
  `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 1 PROPERTIES (
  "replication_allocation" = "tag.location.default: 1"
);

表中的列按照是否设置了 AggregationType，分为 Key (维度列) 和 Value（指标列）。

没有设置 AggregationType 的，如 user_id、date、age … 等称为 Key，而设置了 AggregationType 的称为 Value。

如下

`max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间"

其中MAX就是AggregationType。

AggregationType 目前有以下几种聚合方式：

SUM：求和，多行的 Value 进行累加。
REPLACE：替代，下一批数据中的 Value 会替换之前导入过的行中的 Value。
MAX：保留最大值。
MIN：保留最小值。
REPLACE_IF_NOT_NULL：非空值替换。和 REPLACE 的区别在于对于null值，不做替换。
HLL_UNION：HLL 类型的列的聚合方式，通过 HyperLogLog 算法聚合。
BITMAP_UNION：BIMTAP 类型的列的聚合方式，进行位图的并集聚合。

插入数据

insert into zdb.t_user01 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);

查看表信息

desc zdb.t_user01;

Unique模型

这种模式会根据主键聚合，分两种模式

读时合并（与聚合模型相同的实现方式）
写时合并（没有聚合，直接在写入的时候替换相同主键的值）

这是一个典型的用户基础信息表。

这类数据没有聚合需求，只需保证主键唯一性。（这里的主键为 user_id + username）。

那么我们的建表语句如下：

CREATE TABLE IF NOT EXISTS zdb.t_user02
(
    `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 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

读时合并

Unique 模型的读时合并实现完全可以用聚合模型中的 REPLACE 方式替代。

CREATE TABLE IF NOT EXISTS example_db.example_tbl
(
    `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 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

写时合并

写时合并要添加配置"enable_unique_key_merge_on_write" = "true"

CREATE TABLE IF NOT EXISTS example_db.example_tbl
(
    `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 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"enable_unique_key_merge_on_write" = "true"
);

非聚合(Duplicate 模型)

数据既没有主键，也没有聚合需求时使用。

这种数据模型区别于 Aggregate 和 Unique 模型。数据完全按照导入文件中的数据进行存储，不会有任何聚合。

而在建表语句中指定的 DUPLICATE KEY，只是用来指明底层数据按照那些列进行排序。

CREATE TABLE IF NOT EXISTS zdb.t_student (
  `id` BIGINT NOT NULL COMMENT "用户id",
  `name` VARCHAR(255) COMMENT "用户名",
  `subject` VARCHAR(255) COMMENT "科目",
  `score` BIGINT COMMENT "分数"
) DUPLICATE KEY(`id`) DISTRIBUTED BY HASH(`name`) BUCKETS auto PROPERTIES (
  "replication_allocation" = "tag.location.default: 1"
);

最省略的写法

CREATE TABLE IF NOT EXISTS zdb.t_student (
  `id` BIGINT NOT NULL COMMENT "用户id",
  `name` VARCHAR(255) COMMENT "用户名",
  `subject` VARCHAR(255) COMMENT "科目",
  `score` DOUBLE COMMENT "分数"
) DISTRIBUTED BY HASH(`name`) BUCKETS auto PROPERTIES (
  "replication_allocation" = "tag.location.default: 1"
);

数据模型的选择建议

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

Aggregate 模型可以通过预聚合，极大地降低聚合查询时所需扫描的数据量和查询的计算量，非常适合有固定模式的报表类查询场景。但是该模型对 count(*) 查询很不友好。同时因为固定了 Value 列上的聚合方式，在进行其他类型的聚合查询时，需要考虑语意正确性。
Unique 模型针对需要唯一主键约束的场景，可以保证主键唯一性约束。但是无法利用 ROLLUP 等预聚合带来的查询优势。
1. 对于聚合查询有较高性能需求的用户，推荐使用自1.2版本加入的写时合并实现。
2. Unique 模型仅支持整行更新，如果用户既需要唯一主键约束，又需要更新部分列（例如将多张源表导入到一张 doris 表的情形），则可以考虑使用 Aggregate 模型，同时将非主键列的聚合类型设置为 REPLACE_IF_NOT_NULL。具体的用法可以参考语法手册
Duplicate 适合任意维度的 Ad-hoc 查询。虽然同样无法利用预聚合的特性，但是不受聚合模型的约束，可以发挥列存模型的优势（只读取相关列，而不需要读取所有 Key 列）。

分桶数

自动分桶

CREATE TABLE IF NOT EXISTS zdb.t_student03 (
  `id` BIGINT NOT NULL COMMENT "用户id",
  `name` VARCHAR(255) COMMENT "用户名",
  `subject` VARCHAR(255) COMMENT "科目",
  `score` BIGINT COMMENT "分数"
) DISTRIBUTED BY HASH(`name`) BUCKETS auto;

伪代码

分桶数伪代码，我们想自己设置具体的值也可以参考

int N = 计算数据量N值;
int M = 计算M值;

int y = BE节点个数;
int x = min(M, N, 128);

if (x < N && x < y) {
  return y;
}
return x;

N的算法

先根据数据量得出一个桶数 N。

N = estimate_partition_size / 5

首先使用 estimate_partition_size 的值除以 5（按文本格式存入 Doris 中有 5 比 1 的数据压缩比计算），得到的结果为：

(, 100MB)，则取 N=1
[100MB, 1GB)，则取 N=2
[1GB, )，则每GB 一个分桶

estimate_partition_size 的默认值取为 10GB。也就是说按照默认设置N=2。

M的算法

根据 BE 节点数以及每个 BE 节点的磁盘容量，计算出桶数 M。

其中每个 BE 节点算 1，每 50G 的磁盘容量算 1，

那么 M 的计算规则为：M = BE 节点数 *( 一块磁盘块大小 / 50GB)* 磁盘块数

例如有 3 台 BE，每台 BE 都有 4 块 500GB 的磁盘，那么 M = 3 *(500GB / 50GB)* 4 = 120

获取实际分桶数

开启autobucket之后，

在show create table的时候看到的schema也是BUCKETS AUTO：

show create table t_student03;

如果想要查看确切的bucket数，可以通过以下语法来查看：

show partitions from t_student03;

JDBC同步数据

注意以下几点：

JDBC 连接串需添加 rewriteBatchedStatements=true 参数，并使用 PreparedStatement 方式。

目前 Doris 暂不支持服务器端的 PrepareStatemnt，所以 JDBC Driver 会在客户端进行批量 Prepare。

rewriteBatchedStatements=true 会确保 Driver 执行批处理。

并最终形成如下形式的 INSERT 语句发往 Doris：

INSERT INTO example_tbl VALUES
(1000, "baidu1", 3.25)
(2000, "baidu2", 4.25)
(3000, "baidu3", 5.25);

批次大小因为是在客户端进行批量处理，所以一批次过大的话，会占用客户端的内存资源，需关注。 Doris 后续会支持服务端的 PrepareStatemnt，敬请期待。
导入原子性和其他到导入方式一样，INSERT 操作本身也支持原子性。每一个 INSERT 操作都是一个导入事务，能够保证一个 INSERT 中的所有数据原子性的写入。前面提到，我们建议在使用 INSERT 导入数据时，采用 ”批“ 的方式进行导入，而不是单条插入。

SQL查询

创建库

create database zdb;

创建表

CREATE TABLE IF NOT EXISTS t_student (
  `id` BIGINT NOT NULL COMMENT "用户id",
  `name` VARCHAR(255) COMMENT "用户名",
  `subject` VARCHAR(255) COMMENT "科目",
  `score` DOUBLE COMMENT "分数"
) DISTRIBUTED BY HASH(`name`) BUCKETS auto PROPERTIES (
  "replication_allocation" = "tag.location.default: 1"
);

删除表

drop table t_student;

查询表结构

SELECT
  c.COLUMN_NAME AS fieldname,
  c.data_type AS fieldtype,
  c.COLUMN_COMMENT AS fieldalias,
  c.CHARACTER_MAXIMUM_LENGTH AS fieldlength,
  c.NUMERIC_PRECISION as fieldaccuracy,
  IF(c.IS_NULLABLE = 'YES', 1, 0) as fieldnullable,
  c.ORDINAL_POSITION fieldorder
FROM
  INFORMATION_SCHEMA.COLUMNS c
WHERE
  c.TABLE_NAME = 't_student';

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

磁盘