美文网首页
Doris 自维护的表的语法

Doris 自维护的表的语法

作者: 小李子 | 来源:发表于2023-02-01 14:07 被阅读0次

Description:

该命令用于创建一张表。本文档主要介绍创建 Doris 自维护的表的语法。外部表语法请参阅 CREATE-EXTERNAL-TABLE文档。

CREATE TABLE [IF NOT EXISTS] [database.]table
(
    column_definition_list,
    [index_definition_list]
)
[engine_type]
[keys_type]
[table_comment]
[partition_info]
distribution_desc
[rollup_list]
[properties]
[extra_properties]

  • column_definition_list

    列定义列表：

    column_definition[, column_definition]

    • column_definition

      列定义：

      column_name column_type [KEY] [aggr_type] [NULL] [default_value] [column_comment]

      • column_type

        列类型，支持以下类型：

        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
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
    不支持科学计数法
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。默认为1
HLL (1~16385个字节)
    HyperLogLog 列类型，不需要指定长度和默认值。长度根据数据的聚合程度系统内控制。
    必须配合 HLL_UNION 聚合类型使用。
BITMAP
    bitmap 列类型，不需要指定长度和默认值。表示整型的集合，元素最大支持到2^64 - 1。
    必须配合 BITMAP_UNION 聚合类型使用。

      • aggr_type

        聚合类型，支持以下聚合类型：

        SUM：求和。适用数值类型。
MIN：求最小值。适合数值类型。
MAX：求最大值。适合数值类型。
REPLACE：替换。对于维度列相同的行，指标列会按照导入的先后顺序，后倒入的替换先导入的。
REPLACE_IF_NOT_NULL：非空值替换。和 REPLACE 的区别在于对于null值，不做替换。这里要注意的是字段默认值要给NULL，而不能是空字符串，如果是空字符串，会给你替换成空字符串。
HLL_UNION：HLL 类型的列的聚合方式，通过 HyperLogLog 算法聚合。
BITMAP_UNION：BIMTAP 类型的列的聚合方式，进行位图的并集聚合。

      • default_value

        列默认值，当导入数据未指定该列的值时，系统将赋予该列default_value。

        语法为default default_value。

        当前default_value支持两种形式：

        1. 用户指定固定值，如：
            k1 INT DEFAULT '1',
    k2 CHAR(10) DEFAULT 'aaaa'
        1. 系统提供的关键字，目前支持以下关键字：
            // 只用于DATETIME类型，导入数据缺失该值时系统将赋予当前时间
    dt DATETIME DEFAULT CURRENT_TIMESTAMP

    示例：
    
    ```text
    k1 TINYINT,
    k2 DECIMAL(10,2) DEFAULT "10.5",
    k4 BIGINT NULL DEFAULT "1000" COMMENT "This is column k4",
    v1 VARCHAR(10) REPLACE NOT NULL,
    v2 BITMAP BITMAP_UNION,
    v3 HLL HLL_UNION,
    v4 INT SUM NOT NULL DEFAULT "1" COMMENT "This is column v4"
    ```

  • index_definition_list

    索引列表定义：

    index_definition[, index_definition]

    • index_definition

      索引定义：

      INDEX index_name (col_name) [USING BITMAP] COMMENT 'xxxxxx'

      示例：

      INDEX idx1 (k1) USING BITMAP COMMENT "This is a bitmap index1",
INDEX idx2 (k2) USING BITMAP COMMENT "This is a bitmap index2",
...

  • engine_type

    表引擎类型。本文档中类型皆为 OLAP。其他外部表引擎类型见 CREATE EXTERNAL TABLE 文档。示例：

    ENGINE=olap

  • key_desc

    数据模型。

    key_type(col1, col2, ...)

    key_type 支持以下模型：

    • DUPLICATE KEY（默认）：其后指定的列为排序列。
    • AGGREGATE KEY：其后指定的列为维度列。
    • UNIQUE KEY：其后指定的列为主键列。

    示例：

    DUPLICATE KEY(col1, col2),
AGGREGATE KEY(k1, k2, k3),
UNIQUE KEY(k1, k2)

  • table_comment

    表注释。示例：

    COMMENT "This is my first DORIS table"

  • partition_desc

    分区信息，支持三种写法：

    1. LESS THAN：仅定义分区上界。下界由上一个分区的上界决定。

      PARTITION BY RANGE(col1[, col2, ...])
(
    PARTITION partition_name1 VALUES LESS THAN MAXVALUE|("value1", "value2", ...),
    PARTITION partition_name2 VALUES LESS THAN MAXVALUE|("value1", "value2", ...)
)

    2. FIXED RANGE：定义分区的左闭右开区间。

      PARTITION BY RANGE(col1[, col2, ...])
(
    PARTITION partition_name1 VALUES [("k1-lower1", "k2-lower1", "k3-lower1",...), ("k1-upper1", "k2-upper1", "k3-upper1", ...)),
    PARTITION partition_name2 VALUES [("k1-lower1-2", "k2-lower1-2", ...), ("k1-upper1-2", MAXVALUE, ))
)

    3. <version since="1.2" type="inline"> MULTI RANGE：批量创建RANGE分区，定义分区的左闭右开区间，设定时间单位和步长，时间单位支持年、月、日、周和小时。</version>

      PARTITION BY RANGE(col)
(
   FROM ("2000-11-14") TO ("2021-11-14") INTERVAL 1 YEAR,
   FROM ("2021-11-14") TO ("2022-11-14") INTERVAL 1 MONTH,
   FROM ("2022-11-14") TO ("2023-01-03") INTERVAL 1 WEEK,
   FROM ("2023-01-03") TO ("2023-01-14") INTERVAL 1 DAY
)

  • distribution_desc

    定义数据分桶方式。

    1. Hash 分桶
      语法：
      DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]
      说明：
      使用指定的 key 列进行哈希分桶。
    2. Random 分桶
      语法：
      DISTRIBUTED BY RANDOM [BUCKETS num]
      说明：
      使用随机数进行分桶。

  • rollup_list

    建表的同时可以创建多个物化视图（ROLLUP）。

    ROLLUP (rollup_definition[, rollup_definition, ...])

    • rollup_definition

      rollup_name (col1[, col2, ...]) [DUPLICATE KEY(col1[, col2, ...])] [PROPERTIES("key" = "value")]

      示例：

      ROLLUP (
    r1 (k1, k3, v1, v2),
    r2 (k1, v1)
)

  • properties

    设置表属性。目前支持以下属性：

    • replication_num

      副本数。默认副本数为3。如果 BE 节点数量小于3，则需指定副本数小于等于 BE 节点数量。

      在 0.15 版本后，该属性将自动转换成 replication_allocation 属性，如：

      "replication_num" = "3" 会自动转换成 "replication_allocation" = "tag.location.default:3"

    • replication_allocation

      根据 Tag 设置副本分布情况。该属性可以完全覆盖 replication_num 属性的功能。

    • storage_medium/storage_cooldown_time

      数据存储介质。storage_medium 用于声明表数据的初始存储介质，而 storage_cooldown_time 用于设定到期时间。示例：

      "storage_medium" = "SSD",
"storage_cooldown_time" = "2020-11-20 00:00:00"

      这个示例表示数据存放在 SSD 中，并且在 2020-11-20 00:00:00 到期后，会自动迁移到 HDD 存储上。

    • colocate_with

      当需要使用 Colocation Join 功能时，使用这个参数设置 Colocation Group。

      "colocate_with" = "group1"

    • bloom_filter_columns

      用户指定需要添加 Bloom Filter 索引的列名称列表。各个列的 Bloom Filter 索引是独立的，并不是组合索引。

      "bloom_filter_columns" = "k1, k2, k3"

    • in_memory

      Doris 是没有内存表的概念。

      这个属性设置成 true, Doris 会尽量将该表的数据块缓存在存储引擎的 PageCache 中，已减少磁盘IO。但这个属性不会保证数据块常驻在内存中，仅作为一种尽力而为的标识。

      "in_memory" = "true"

    • compression

      Doris 表的默认压缩方式是 LZ4。1.1版本后，支持将压缩方式指定为ZSTD以获得更高的压缩比。

      "compression"="zstd"

    • function_column.sequence_col

      当使用 UNIQUE KEY 模型时，可以指定一个sequence列，当KEY列相同时，将按照 sequence 列进行 REPLACE(较大值替换较小值，否则无法替换)

      function_column.sequence_col用来指定sequence列到表中某一列的映射，该列可以为整型和时间类型（DATE、DATETIME），创建后不能更改该列的类型。如果设置了function_column.sequence_col, function_column.sequence_type将被忽略。

      "function_column.sequence_col" = 'column_name'

    • function_column.sequence_type

      当使用 UNIQUE KEY 模型时，可以指定一个sequence列，当KEY列相同时，将按照 sequence 列进行 REPLACE(较大值替换较小值，否则无法替换)

      这里我们仅需指定顺序列的类型，支持时间类型或整型。Doris 会创建一个隐藏的顺序列。

      "function_column.sequence_type" = 'Date'

    • light_schema_change

      <version since="1.2" type="inline"> 是否使用light schema change优化。</version>

      如果设置成 true, 对于值列的加减操作，可以更快地，同步地完成。

      "light_schema_change" = 'true'

      该功能在 1.2.1 及之后版本默认开启。

    • disable_auto_compaction

      是否对这个表禁用自动compaction。

      如果这个属性设置成 true, 后台的自动compaction进程会跳过这个表的所有tablet。

      "disable_auto_compaction" = "false"

    • 动态分区相关

      动态分区相关参数如下：

      • dynamic_partition.enable: 用于指定表级别的动态分区功能是否开启。默认为 true。
      • dynamic_partition.time_unit: 用于指定动态添加分区的时间单位，可选择为DAY（天），WEEK(周)，MONTH（月），HOUR（时）。
      • dynamic_partition.start: 用于指定向前删除多少个分区。值必须小于0。默认为 Integer.MIN_VALUE。
      • dynamic_partition.end: 用于指定提前创建的分区数量。值必须大于0。
      • dynamic_partition.prefix: 用于指定创建的分区名前缀，例如分区名前缀为p，则自动创建分区名为p20200108。
      • dynamic_partition.buckets: 用于指定自动创建的分区分桶数量。
      • dynamic_partition.create_history_partition: 是否创建历史分区。
      • dynamic_partition.history_partition_num: 指定创建历史分区的数量。
      • dynamic_partition.reserved_history_periods: 用于指定保留的历史分区的时间段。

    • 数据排序相关

      数据排序相关参数如下:

      • data_sort.sort_type: 数据排序使用的方法，目前支持两种：lexical/z-order，默认是lexical
      • data_sort.col_num: 数据排序使用的列数，取最前面几列，不能超过总的key 列数
        Examples:

  1. 创建一个明细模型的表

    CREATE TABLE example_db.table_hash
(
    k1 TINYINT,
    k2 DECIMAL(10, 2) DEFAULT "10.5",
    k3 CHAR(10) COMMENT "string column",
    k4 INT NOT NULL DEFAULT "1" COMMENT "int column"
)
COMMENT "my first table"
DISTRIBUTED BY HASH(k1) BUCKETS 32

  2. 创建一个明细模型的表，分区，指定排序列，设置副本数为1

    CREATE TABLE example_db.table_hash
(
    k1 DATE,
    k2 DECIMAL(10, 2) DEFAULT "10.5",
    k3 CHAR(10) COMMENT "string column",
    k4 INT NOT NULL DEFAULT "1" COMMENT "int column"
)
DUPLICATE KEY(k1, k2)
COMMENT "my first table"
PARTITION BY RANGE(k1)
(
    PARTITION p1 VALUES LESS THAN ("2020-02-01"),
    PARTITION p2 VALUES LESS THAN ("2020-03-01"),
    PARTITION p3 VALUES LESS THAN ("2020-04-01")
)
DISTRIBUTED BY HASH(k1) BUCKETS 32
PROPERTIES (
    "replication_num" = "1"
);

  3. 创建一个主键唯一模型的表，设置初始存储介质和冷却时间

    CREATE TABLE example_db.table_hash
(
    k1 BIGINT,
    k2 LARGEINT,
    v1 VARCHAR(2048),
    v2 SMALLINT DEFAULT "10"
)
UNIQUE KEY(k1, k2)
DISTRIBUTED BY HASH (k1, k2) BUCKETS 32
PROPERTIES(
    "storage_medium" = "SSD",
    "storage_cooldown_time" = "2015-06-04 00:00:00"
);

  4. 创建一个聚合模型表，使用固定范围分区描述

    CREATE TABLE table_range
(
    k1 DATE,
    k2 INT,
    k3 SMALLINT,
    v1 VARCHAR(2048) REPLACE,
    v2 INT SUM DEFAULT "1"
)
AGGREGATE KEY(k1, k2, k3)
PARTITION BY RANGE (k1, k2, k3)
(
    PARTITION p1 VALUES [("2014-01-01", "10", "200"), ("2014-01-01", "20", "300")),
    PARTITION p2 VALUES [("2014-06-01", "100", "200"), ("2014-07-01", "100", "300"))
)
DISTRIBUTED BY HASH(k2) BUCKETS 32

  5. 创建一个包含 HLL 和 BITMAP 列类型的聚合模型表

    CREATE TABLE example_db.example_table
(
    k1 TINYINT,
    k2 DECIMAL(10, 2) DEFAULT "10.5",
    v1 HLL HLL_UNION,
    v2 BITMAP BITMAP_UNION
)
ENGINE=olap
AGGREGATE KEY(k1, k2)
DISTRIBUTED BY HASH(k1) BUCKETS 32

  6. 创建两张同一个 Colocation Group 自维护的表。

    CREATE TABLE t1 (
    id int(11) COMMENT "",
    value varchar(8) COMMENT ""
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES (
    "colocate_with" = "group1"
);

CREATE TABLE t2 (
    id int(11) COMMENT "",
    value1 varchar(8) COMMENT "",
    value2 varchar(8) COMMENT ""
)
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
    "colocate_with" = "group1"
);

  7. 创建一个带有 bitmap 索引以及 bloom filter 索引的内存表

    CREATE TABLE example_db.table_hash
(
    k1 TINYINT,
    k2 DECIMAL(10, 2) DEFAULT "10.5",
    v1 CHAR(10) REPLACE,
    v2 INT SUM,
    INDEX k1_idx (k1) USING BITMAP COMMENT 'my first index'
)
AGGREGATE KEY(k1, k2)
DISTRIBUTED BY HASH(k1) BUCKETS 32
PROPERTIES (
    "bloom_filter_columns" = "k2",
    "in_memory" = "true"
);

  8. 创建一个动态分区表。

    该表每天提前创建3天的分区，并删除3天前的分区。例如今天为2020-01-08，则会创建分区名为p20200108, p20200109, p20200110, p20200111的分区. 分区范围分别为:

    [types: [DATE]; keys: [2020-01-08]; ‥types: [DATE]; keys: [2020-01-09]; )
[types: [DATE]; keys: [2020-01-09]; ‥types: [DATE]; keys: [2020-01-10]; )
[types: [DATE]; keys: [2020-01-10]; ‥types: [DATE]; keys: [2020-01-11]; )
[types: [DATE]; keys: [2020-01-11]; ‥types: [DATE]; keys: [2020-01-12]; )

    CREATE TABLE example_db.dynamic_partition
(
    k1 DATE,
    k2 INT,
    k3 SMALLINT,
    v1 VARCHAR(2048),
    v2 DATETIME DEFAULT "2014-02-04 15:36:00"
)
DUPLICATE KEY(k1, k2, k3)
PARTITION BY RANGE (k1) ()
DISTRIBUTED BY HASH(k2) BUCKETS 32
PROPERTIES(
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-3",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "32" 
);

  9. 创建一个带有物化视图（ROLLUP）的表。

    CREATE TABLE example_db.rolup_index_table
(
    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)
DISTRIBUTED BY HASH(siteid) BUCKETS 10
ROLLUP (
    r1(event_day,siteid),
    r2(event_day,citycode),
    r3(event_day)
)
PROPERTIES("replication_num" = "3");

  10. 通过 replication_allocation 属性设置表的副本。

    CREATE TABLE example_db.table_hash
(
    k1 TINYINT,
    k2 DECIMAL(10, 2) DEFAULT "10.5"
)
DISTRIBUTED BY HASH(k1) BUCKETS 32
PROPERTIES (
    "replication_allocation"="tag.location.group_a:1, tag.location.group_b:2"
);

    CREATE TABLE example_db.dynamic_partition
(
    k1 DATE,
    k2 INT,
    k3 SMALLINT,
    v1 VARCHAR(2048),
    v2 DATETIME DEFAULT "2014-02-04 15:36:00"
)
PARTITION BY RANGE (k1) ()
DISTRIBUTED BY HASH(k2) BUCKETS 32
PROPERTIES(
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-3",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "32",
    "dynamic_partition.replication_allocation" = "tag.location.group_a:3"
 );

  11. 通过storage_policy属性设置表的冷热分离数据迁移策略

        CREATE TABLE IF NOT EXISTS create_table_use_created_policy 
        (
            k1 BIGINT,
            k2 LARGEINT,
            v1 VARCHAR(2048)
        )
        UNIQUE KEY(k1)
        DISTRIBUTED BY HASH (k1) BUCKETS 3
        PROPERTIES(
            "storage_policy" = "test_create_table_use_policy",
            "replication_num" = "1"
        );

注：需要先创建s3 resource 和 storage policy，表才能关联迁移策略成功

  1. 为表的分区添加冷热分离数据迁移策略
        CREATE TABLE create_table_partion_use_created_policy
        (
            k1 DATE,
            k2 INT,
            V1 VARCHAR(2048) REPLACE
        ) PARTITION BY RANGE (k1) (
            PARTITION p1 VALUES LESS THAN ("2022-01-01") ("storage_policy" = "test_create_table_partition_use_policy_1" ,"replication_num"="1"),
            PARTITION p2 VALUES LESS THAN ("2022-02-01") ("storage_policy" = "test_create_table_partition_use_policy_2" ,"replication_num"="1")
        ) DISTRIBUTED BY HASH(k2) BUCKETS 1;

注：需要先创建s3 resource 和 storage policy，表才能关联迁移策略成功

<version since="1.2.0">

  1. 批量创建分区
        CREATE TABLE create_table_multi_partion_date
        (
            k1 DATE,
            k2 INT,
            V1 VARCHAR(20)
        ) PARTITION BY RANGE (k1) (
            FROM ("2000-11-14") TO ("2021-11-14") INTERVAL 1 YEAR,
            FROM ("2021-11-14") TO ("2022-11-14") INTERVAL 1 MONTH,
            FROM ("2022-11-14") TO ("2023-01-03") INTERVAL 1 WEEK,
            FROM ("2023-01-03") TO ("2023-01-14") INTERVAL 1 DAY,
            PARTITION p_20230114 VALUES [('2023-01-14'), ('2023-01-15'))
        ) DISTRIBUTED BY HASH(k2) BUCKETS 1
        PROPERTIES(
            "replication_num" = "1"
        );

        CREATE TABLE create_table_multi_partion_date_hour
        (
            k1 DATETIME,
            k2 INT,
            V1 VARCHAR(20)
        ) PARTITION BY RANGE (k1) (
            FROM ("2023-01-03 12") TO ("2023-01-14 22") INTERVAL 1 HOUR
        ) DISTRIBUTED BY HASH(k2) BUCKETS 1
        PROPERTIES(
            "replication_num" = "1"
        );

注：批量创建分区可以和常规手动创建分区混用，使用时需要限制分区列只能有一个，批量创建分区实际创建默认最大数量为4096，这个参数可以在fe配置项 max_multi_partition_num 调整

相关文章

  • Apache Doris 系列： 入门篇-创建数据表

    本文档我们介绍 Doris 怎么创建表及其他的相关操作。 连接 Doris 我们在成功部署完成 Doris 之后，...

  • Doris stream load 导入数据

    Doris的导入（Load）功能就是将用户的原始数据导入到 Doris表中。Doris底层实现了统一的流式导入框架...

  • Spring boot 操作doris

    1. 启动doris 1.1 一台fe 三台be 1.2 连接 doris 1.3 创建表 1.4 插入数据 ...

  • Apache Doris——数据表的创建

    前言—Doris 基本概念 Doris 数据分布 1、从表的角度来看数据结构，用户的一张 Table 会拆成多个 ...

  • Doris 源码分析 (三) 基础语法

    常见使用语句 语法解析过程 Doris SQL 解析具体包括了五个步骤：词法分析，语法分析，生成单机逻辑计划，生成...

  • CSS笔记

    引入 外部样式表 内部样式表 内嵌样式 此方式不利于后期维护，较少使用此方法。 语法 选择器属性声明=属性名:属性...

  • Doris系列6-Doris之rollup

    一. 基本概念 在 Doris 中，我们将用户通过建表语句创建出来的表称为 Base 表（Base Table）。...

  • 创建表、插入数据、更新数据

    常见创建表语法 根据当前表创建新表 新表不存在 新表存在 插入数据 更新数据 1.不同表的修改 2.表的自连接更新...

  • Doris系列5-Doris数据模型

    基本概念 在 Doris 中，数据以表（Table）的形式进行逻辑上的描述。一张表包括行（Row）和列（Colum...

  • oracle 添加、修改、删除、约束-语法

    创建表结构： 向表中添加主键约束 向表中添加Sequences 添加字段的语法： 修改字段的语法： 删除字段的语法...

网友评论

      本文标题：Doris 自维护的表的语法

      本文链接：https://www.haomeiwen.com/subject/qveihdtx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|Doris 自维护的表的语法|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！