Doris 创建olap、mysql、broker表

绝地灬酷狼 2023-02-24 15:26 22阅读 0赞

**目录**

一、创建语法

语法

描述

1. column\_definition

2. ENGINE 类型

3. key\_desc

4. partition\_desc

5. distribution\_desc

6. PROPERTIES

二、表创建实例

1. 创建一个 olap 表，使用 HASH 分桶，使用列存，相同key的记录进行聚合

2. 创建一个 olap 表，使用 Hash 分桶，使用列存，相同key的记录进行覆盖设置初始存储介质和冷却时间

3. 创建一个 olap 表，使用 Key Range 分区，使用Hash分桶，默认使用列存，       相同key的记录同时存在，设置初始存储介质和冷却时间

4. 创建一个 mysql 表

5. 创建一个数据文件存储在HDFS上的 broker 外部表, 数据使用 "|" 分割，"\\n" 换行

6. 创建一张含有HLL列的表

--------------------

> 使用Mysql Client 运行 HELP CREATE TABLE 做的笔记。

# 一、创建语法 #

## 语法 ##

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [database.]table_name
            (column_definition1[, column_definition2, ...])
            [ENGINE = [olap|mysql|broker]]
            [key_desc]
            [partition_desc]
            [distribution_desc]
            [PROPERTIES ("key"="value", ...)];
            [BROKER PROPERTIES ("key"="value", ...)];

## 描述 ##

### 1. column\_definition ###

表字段。

语法：

col_name col_type [agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]

说明：

**col\_name**：列名称  
**col\_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字节）
        范围：0 ~ 2^127 - 1
    FLOAT（4字节）
        支持科学计数法
    DOUBLE（12字节）
        支持科学计数法
    DECIMAL[(precision, scale)] (40字节)
        保证精度的小数类型。默认是 DECIMAL(10, 0)
        precision: 1 ~ 27
        scale: 0 ~ 9
        其中整数部分为 1 ~ 18
        不支持科学计数法
    DATE（3字节）
        范围：1900-01-01 ~ 9999-12-31
    DATETIME（8字节）
        范围：1900-01-01 00:00:00 ~ 9999-12-31 23:59:59
    CHAR[(length)]
        定长字符串。长度范围：1 ~ 255。默认为1
    VARCHAR[(length)]
        变长字符串。长度范围：1 ~ 65533
    HLL (1~16385个字节)
        hll列类型，不需要指定长度和默认值、长度根据数据的聚合
        程度系统内控制，并且HLL列只能通过配套的hll_union_agg、Hll_cardinality、hll_hash进行查询或使用

**agg\_type**：聚合类型

*  如果不指定，则该列为 key 列。否则，该列为 value 列
 *  SUM、MAX、MIN、REPLACE、HLL\_UNION(仅用于HLL列，为HLL独有的聚合方式)
 *  该类型只对聚合模型(key\_desc的type为AGGREGATE KEY)有用，其它模型不需要指定这个。

**是否允许为NULL**: 默认不允许为 NULL。NULL 值在导入数据中用 \\N 来表示

### 2. ENGINE 类型 ###

指定表类型，默认为 olap。可选 mysql, broker。

1) 如果是 mysql，则需要在 properties 提供以下信息：

PROPERTIES (
    "host" = "mysql_server_host",
    "port" = "mysql_server_port",
    "user" = "your_user_name",
    "password" = "your_password",
    "database" = "database_name",
    "table" = "table_name"
    )

*  "table" 条目中的 "table\_name" 是 mysql 中的真实表名。而 CREATE TABLE 语句中的 table\_name 是该 mysql 表在 Palo 中的名字，可以不同。
 *  在 Palo 创建 mysql 表的目的是可以通过 Palo 访问 mysql 数据库。而 Palo 本身并不维护、存储任何 mysql 数据。

2) 如果是 broker，表示表的访问需要通过指定的broker, 需要在 properties 提供以下信息：

PROPERTIES (
    "broker_name" = "broker_name",
    "paths" = "file_path1[,file_path2]",
    "column_separator" = "value_separator"
    "line_delimiter" = "value_delimiter"
    )

另外还需要提供Broker需要的Property信息，通过BROKER PROPERTIES来传递，例如HDFS需要传入

BROKER PROPERTIES(
    "username" = "name",
    "password" = "password"
    )

*  这个根据不同的Broker类型，需要传入的内容也不相同
 *  "paths" 中如果有多个文件，用逗号\[,\]分割
 *  如果文件名中包含逗号，那么使用 %2c 来替代
 *  如果文件名中包含 %，使用 %25 代替
 *  现在文件内容格式支持**CSV**，支持GZ，BZ2，LZ4，LZO(LZOP) 压缩格式。

### 3. key\_desc ###

指定数据模型，[数据模型概念][Link 1]。

语法：

key_type(k1[,k2 ...])

数据按照指定的key列进行排序，且根据不同的key\_type具有不同特性。

key\_type支持一下类型：

*  AGGREGATE KEY ：key列相同的记录，value列按照指定的聚合类型进行聚合，适合报表、多维分析等业务场景。
 *  UNIQUE KEY：key列相同的记录，value列按导入顺序进行覆盖，适合按key列进行增删改查的点查询业务。
 *  DUPLICATE KEY：key列相同的记录，同时存在于Palo中，适合存储明细数据或者数据无聚合特性的业务场景。

注意： 除AGGREGATE KEY外，其他key\_type在建表时，value列不需要指定聚合类型。

### 4. partition\_desc ###

指定分区。

**1) Range 分区**  
语法：

PARTITION BY RANGE (k1)
                (
                PARTITION partition_name VALUES LESS THAN MAXVALUE|("value1")
                PARTITION partition_name VALUES LESS THAN MAXVALUE|("value2")
                ...
                )

说明：

使用指定的 key 列和指定的数值范围进行分区。
                1) 分区名称仅支持字母开头，字母、数字和下划线组成
                2) 目前仅支持以下类型的列作为 Range 分区列，且只能指定一个分区列
                    TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME
                3) 分区为左闭右开区间，首个分区的左边界为做最小值
                4) NULL 值只会存放在包含最小值的分区中。当包含最小值的分区被删除后，NULL 值将无法导入。

注意：

1) 分区一般用于时间维度的数据管理
    2) 有数据回溯需求的，可以考虑首个分区为空分区，以便后续增加分区

### 5. distribution\_desc ###

Hash 分桶  
语法：  
            DISTRIBUTED BY HASH (k1\[,k2 ...\]) \[BUCKETS num\]  
说明：  
            使用指定的 key 列进行哈希分桶。默认分区数为10

建议:建议使用Hash分桶方式

### 6. PROPERTIES ###

1) 如果 ENGINE 类型为 olap，则可以在 properties 中指定列存（目前仅支持列存）

PROPERTIES (
                "storage_type" = "[column]",
                )

2) 如果 ENGINE 类型为 olap，可以在 properties 设置该表数据的初始存储介质、存储到期时间和副本数。

PROPERTIES (
               "storage_medium" = "[SSD|HDD]",
               ["storage_cooldown_time" = "yyyy-MM-dd HH:mm:ss"],
               ["replication_num" = "3"]
               )

*   storage\_medium：        用于指定该分区的初始存储介质，可选择 SSD 或 HDD。默认为 HDD。
 *  storage\_cooldown\_time： 当设置存储介质为 SSD 时，指定该分区在 SSD 上的存储到期时间。默认存放 7 天。格式为："yyyy-MM-dd HH:mm:ss"。
 *  replication\_num:        指定分区的副本数。默认为 3。
 *  当表为单分区表时，这些属性为表的属性。
 *  当表为两级分区时，这些属性为附属于每一个分区。
 *  如果希望不同分区有不同属性。可以通过 ADD PARTITION 或 MODIFY PARTITION 进行操作

3) 如果 Engine 类型为 olap, 并且 storage\_type 为 column, 可以指定某列使用 bloom filter 索引

bloom filter 索引仅适用于查询条件为 in 和 equal 的情况，该列的值越分散效果越好

目前只支持以下情况的列:除了 TINYINT FLOAT DOUBLE 类型以外的 key 列及聚合方法为 REPLACE 的 value 列

PROPERTIES (
       "bloom_filter_columns"="k1,k2,k3"
    )

4) 如果希望使用Colocate Join 特性，需要在 properties 中指定

PROPERTIES (
               "colocate_with"="table1"
               )

# 二、表创建实例 #

## 1. 创建一个 olap 表，使用 HASH 分桶，使用列存，相同key的记录进行聚合 ##

CREATE TABLE example_db.table_hash
            (
            k1 TINYINT,
            k2 DECIMAL(10, 2) DEFAULT "10.5",
            v1 CHAR(10) REPLACE,
            v2 INT SUM
            )
            ENGINE=olap
            AGGREGATE KEY(k1, k2)
            DISTRIBUTED BY HASH(k1) BUCKETS 32
            PROPERTIES ("storage_type"="column");

##  2. 创建一个 olap 表，使用 Hash 分桶，使用列存，相同key的记录进行覆盖设置初始存储介质和冷却时间 ##

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

## 3. 创建一个 olap 表，使用 Key Range 分区，使用Hash分桶，默认使用列存，          相同key的记录同时存在，设置初始存储介质和冷却时间 ##

CREATE TABLE example_db.table_range
            (
            k1 DATE,
            k2 INT,
            k3 SMALLINT,
            v1 VARCHAR(2048),
            v2 DATETIME DEFAULT "2014-02-04 15:36:00"
            )
            ENGINE=olap
            DUPLICATE KEY(k1, k2, k3)
            PARTITION BY RANGE (k1)
            (
            PARTITION p1 VALUES LESS THAN ("2014-01-01"),
            PARTITION p2 VALUES LESS THAN ("2014-06-01"),
            PARTITION p3 VALUES LESS THAN ("2014-12-01")
            )
            DISTRIBUTED BY HASH(k2) BUCKETS 32
            PROPERTIES(
            "storage_medium" = "SSD", "storage_cooldown_time" = "2015-06-04 00:00:00"
            );

说明：  
        这个语句会将数据划分成如下3个分区：  
        ( \{    MIN     \},   \{"2014-01-01"\} )  
        \[ \{"2014-01-01"\},   \{"2014-06-01"\} )  
        \[ \{"2014-06-01"\},   \{"2014-12-01"\} )

不在这些分区范围内的数据将视为非法数据被过滤

## 4. 创建一个 mysql 表 ##

CREATE TABLE example_db.table_mysql
            (
            k1 DATE,
            k2 INT,
            k3 SMALLINT,
            k4 VARCHAR(2048),
            k5 DATETIME
            )
            ENGINE=mysql
            PROPERTIES
            (
            "host" = "127.0.0.1",
            "port" = "8239",
            "user" = "mysql_user",
            "password" = "mysql_passwd",
            "database" = "mysql_db_test",
            "table" = "mysql_table_test"
            )

## 5. 创建一个数据文件存储在HDFS上的 broker 外部表, 数据使用 "|" 分割，"\\n" 换行 ##

CREATE EXTERNAL TABLE example_db.table_broker (
            k1 DATE,
            k2 INT,
            k3 SMALLINT,
            k4 VARCHAR(2048),
            k5 DATETIME
            )
            ENGINE=broker
            PROPERTIES (
            "broker_name" = "hdfs",
            "path" = "hdfs://hdfs_host:hdfs_port/data1,hdfs://hdfs_host:hdfs_port/data2,hdfs://hdfs_host:hdfs_port/data3%2c4",
            "column_separator" = "|",
            "line_delimiter" = "\n"
            )
            BROKER PROPERTIES (
            "username" = "hdfs_user",
            "password" = "hdfs_password"
            )

##  6. 创建一张含有HLL列的表 ##

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

[Link 1]: http://doris.apache.org/master/zh-CN/getting-started/data-model-rollup.html#%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5