描述

在当前或指定的数据库中创建一个新表。一个表可以有多个列，每个列的定义包括名称、数据类型，以及可选的以下属性：

• 是否是 key
• 是否有聚合语义
• 是否是生成列
• 是否要求值（NOT NULL）
• 是否是自增列
• 是否有插入时的默认值
• 是否有更新时的默认值

此外，此命令还支持以下变体：

• CREATE TABLE … AS SELECT（创建一个已填充数据的表；也称为 CTAS）
• CREATE TABLE … LIKE（创建一个现有表的空副本）

语法

1CREATE [ EXTERNAL ] TABLE [ IF NOT EXISTS ] <table_name>
2    (<columns_definition> [ <indexes_definition> ])
3    [ ENGINE = <table_engine_type> ]
4    [ <key_type> KEY (<key_cols>)
5        [ CLUSTER BY (<cluster_cols>) ]
6    ]
7    [ COMMENT '<table_comment>' ]
8    [ <partitions_definition> ]
9    [ DISTRIBUTED BY { HASH (<distribute_cols>) | RANDOM }
10        [ BUCKETS { <bucket_count> | AUTO } ]
11    ]
12    [ <roll_up_definition> ]
13    [ PROPERTIES (
14          -- 表属性
15          <table_property>
16          -- 其他表属性
17          [ , ... ]) 
18    ]

其中：

1columns_definition
2  : -- 列定义
3    <col_name> <col_type>
4      [ KEY ]
5      [ <col_aggregate_type> ]
6      [ [NOT] NULL ]
7      [ AUTO_INCREMENT(<col_auto_increment_start_value>) ]
8      [ DEFAULT <col_default_value> ]
9      [ ON UPDATE CURRENT_TIMESTAMP (<col_on_update_precision>) ]
10      [ COMMENT '<col_comment>' ]
11    -- 其他列定义
12    [ , <col_name> <col_type> [ ... ] ]

1indexes_definition
2  : -- 索引定义
3    INDEX [ IF NOT EXISTS ]
4      <index_name> (<index_cols>)
5      [ USING <index_type> ]
6      [ PROPERTIES (
7            -- 表属性
8            <index_property>
9            -- 其他表属性
10            [ , ... ]) 
11      ]
12      [ COMMENT '<index_comment>' ]
13    -- 其他索引定义
14    [ , <index_name> (<index_cols>) [ ... ] ]

1partitions_definition
2  : AUTO PARTITION BY RANGE(
3      <auto_partition_function>(<auto_partition_arguments>))
4      ()
5  | PARTITION BY <partition_type>
6    (<partition_cols>)
7    (
8        -- 分区定义
9        <one_partition_definition>
10        -- 其他分区定义
11        [ , ... ]
12    )

• 其中： <one_partition_definition>

  SQL 复制

  1<one_partition_definition>
  2: PARTITION [ IF NOT EXISTS ] <partition_name>
  3    VALUES LESS THAN <partition_value_list>
  4| PARTITION [ IF NOT EXISTS ] <partition_name>
  5    VALUES [ <partition_lower_bound>, <partition_upper_bound>)
  6| FROM <partition_lower_bound> TO <partition_upper_bound>
  7    INTERVAL <n> [ <datetime_unit> ]
  8| PARTITION [ IF NOT EXISTS ] <partition_name>
  9    VALUES IN {
  10        (<partition_value> [, <partition_value> [ ... ] ])
  11        | <partition_value>
  12    }

1roll_up_definition
2  : ROLLUP (
3        -- 聚合定义
4        <rollup_name> (<rollup_cols>)
5        [ DUPLICATE KEY (<duplicate_cols>) ]
6        -- 其他聚合定义
7        [ , <rollup_name> (<rollup_cols>) [ ... ] ]
8    )

变种语法

CREATE TABLE … AS SELECT（也称为 CTAS）

创建一个填充 query 返回数据的表：

1CREATE
2    [ EXTERNAL ]
3  TABLE [ IF NOT EXISTS ] <table_name>
4    [(<col_name> [ , <col_name> [ ... ] ] )]
5    [ <indexesDefinition> ]
6    [ ENGINE = <table_engine_type> ]
7    [ <key_type> KEY (<key_cols>)
8        [ CLUSTER BY (<cluster_cols>) ]
9    ]
10    [ COMMENT '<table_comment>' ]
11    [ <partitionsDefinition> ]
12    [ DISTRIBUTED BY { HASH (<distribute_cols>) | RANDOM }
13        [ BUCKETS { <bucket_count> | AUTO } ]
14    ]
15    [ <rollUpDefinition> ]
16    [ PROPERTIES (
17          -- 表属性
18          <table_property>
19          -- 其他表属性
20          [ , ... ]) 
21    ]
22    AS <query>

CREATE TABLE … LIKE

创建一个新表，其列定义与现有表相同，但不从现有表中复制数据。列的所有属性将被复制到新的表中。如果指定了 rollup 列表，那么原表中对应名字的 rollup 也将复制到新的表中：

1CREATE TABLE <table_name> LIKE <source_table>
2[ WITH ROLLUP (<rollup_names>) ]

必选参数

指定表的标识符（即名称）；在创建表的数据库（Database）中必须唯一。

标识符必须以字母字符（如果开启 unicode 名字支持，则可以是任意语言文字的字符）开头，并且不能包含空格或特殊字符，除非整个标识符字符串用反引号括起来（例如My Object）。

标识符不能使用保留关键字。

<col_name>

指定列标识符（即名称）。在创建的表中必须唯一。

标识符必须以字母字符（如果开启 unicode 名字支持，则可以是任意语言文字的字符），数字或符号@开头，并且不能包含空格或特殊字符，除非整个标识符字符串用反引号括起来（例如My Object）。

<col_type>

指定列的数据类型。

在 CTAS 中为必选参数。指定填充数据的 SELECT 语句。

<source_table>

在 CREATE TABLE ... LIKE 中为必选参数。指定被复制的原表。

可选参数

数据模型相关参数

<key_type>

表的数据模型。可选值为 DUPLICATE（明细模型）、UNIQUE（主键模型）、AGGREGATE（聚合模型）。

<key_cols>

表的 key 列。PALO 中 Key 列必须是表的前 K 个列。单个 tablet 中的数据会按照这些列保持有序。

<cluster_cols>

数据局部排序列，只能在数据模型为 UNIQUE（主键模型）时使用。当指定 <cluster_cols> 后，数据按照<cluster_cols>排序，而不再使用<key_cols>。

<col_aggregate_type>

列的聚合方式。只有当表是聚合模型时可以使用。

分桶相关参数

<distribute_cols> 和 <bucket_count>

分桶列和分桶数。明细模型的分桶列可以是任意的列，聚合模型和主键模型的分桶列必须和 key 列保持一致。分桶数是任意的正整数。

列的默认值相关参数

AUTO_INCREMENT(<col_auto_increment_start_value>)

在导入数据时，PALO 会为在自增列上没有指定值的数据行分配一个表内唯一的值。<col_auto_increment_start_value> 指定自增列的起始值。

DEFAULT <col_default_value>

列的默认值。当写入时不包含此列时，使用此默认值填充。当默认值不显式设置时，使用 NULL 填充。可用的默认值包括：

• NULL：全部类型均可用，用 NULL 作为默认值。
• 数值字面量：只能是数值类型时使用。
• 字符串字面量：只能是字符串类型时使用。
• CURRENT_DATE：只能是 date 类型时使用。用当前日期作为默认值。
• CURRENT_TIMESTAMP [ ]：只能是 datetime 类型时使用。用当前时间作为默认值。 可以指定时间精度。
• PI：只能是 double 类型时使用。用圆周率作为默认值。
• E：只能是 double 类型时使用。用数学常数作为默认值。
• BITMAP_EMPTY：只能当列是 bitmap 类型时使用。填充空的 bitmap。

ON UPDATE CURRENT_TIMESTAMP (<col_on_update_precision>)

当数据更新时，如果没有指定此列的值，则使用当前时间戳更新此列数据。只能在 UNIQUE（主键模型）的表上使用。

索引相关参数

<index_name>

指定索引标识符（即名称）。在创建的表中必须唯一。

<index_cols>

添加索引的列列表。必须是表中存在的列。

<index_type>

索引的类型。当前仅支持 INVERTED

<index_property>

索引的属性。

自动分区相关参数

<auto_partition_function>(<auto_partition_arguments>)

自动分区方式。<auto_partition_function> 目前只支持date_trunc。<auto_partition_arguments> 填写自动分区的列和 date_trunc 的单位。例如 date_trunc(col_1, 'day')。

当使用自动分区时，分区列必须为 NOT NULL。

手动分区相关参数

有关分区的详细介绍，请参阅“手动分区”章节。

<partition_type>

PALO 支持 RANGE 分区和 LIST 分区。

<partition_name>

分区标识符（即名称）。在创建的表中必须唯一。

VALUES LESS THAN <partition_value_list>

range 分区。分区数据范围从下界到 <partition_value_list>。

如果是表示上界，<partition_value_list> 可以用 MAX_VALUE 简化书写。

<partition_value_list> 的格式如下：((col_1_value, ...), (col_1_value, ...), ...)

VALUES [ <partition_lower_bound>, <partition_upper_bound>)

range 分区。分区数据范围从 <partition_lower_bound> 到 <partition_upper_bound>。只创建一个分区。

<partition_lower_bound> 和 <partition_upper_bound> 格式如下：(col_1_value, ...)

FROM <partition_lower_bound> TO <partition_upper_bound>

INTERVAL [ <datetime_unit> ]

range 分区。分区数据范围从 <partition_lower_bound> 到 <partition_value_list>。每建个 创建一个分区。

<partition_lower_bound> 和 <partition_upper_bound> 格式如下：(col_1_value, ...)

VALUES IN {

​ (<partition_value> [, <partition_value> [ ... ] ])

​ | <partition_value>

​ }

list 分区。分区列等于 <partition_value> 的属于此分区。

<partition_value> 格式如下：(col_1_value, ...)

同步物化视图相关

:::caution 注意 rollup 可以创建的同步物化视图功能有限。已不再推荐使用。推荐使用独立的语句创建同步物化视图。 :::

<rollup_name>

同步物化视图的标示符（即名称）。在创建的表中必须唯一。

<rollup_cols>

同步物化视图包含的列。

表属性相关参数

<table_property>

权限控制

执行此 SQL 命令的用户必须至少具有以下权限：

注意事项

• 数据库（Database）中不能包含具有相同名称的表（Table），视图（View）。
• 表名，列名，rollup 名，不能使用保留关键字。

• CREATE TABLE … LIKE：

  • 只能对 PALO 内表使用此命令
  • 显示指定的 rollup 才会被复制
  • 所有的同步物化视图都不会被复制

• CREATE TABLE … AS SELECT (CTAS)：

  • 如果 SELECT 列表中列名的别名是有效的列，则在 CTAS 语句中不需要列定义；如果省略，列名和数据类型将从基础查询中推断出来：

    SQL 复制

    1CREATE TABLE <table_name> AS SELECT ...

  • 或者，可以使用以下语法明确指定名称：

    SQL 复制

    1CREATE TABLE <table_name> ( <col1_name> , <col2_name> , ... ) AS SELECT ...

• 分区和分桶

  • 一个表必须指定分桶列，但可以不指定分区。
  • PALO 中的表可以分为分区表和无分区的表。这个属性在建表时确定，之后不可更改。即对于分区表，可以在之后的使用过程中对分区进行增删操作，而对于无分区的表，之后不能再进行增加分区等操作。
  • 分区列和分桶列在表创建之后不可更改，既不能更改分区和分桶列的类型，也不能对这些列进行任何增删操作。

• 动态分区

  • 动态分区功能主要用于帮助用户自动的管理分区。通过设定一定的规则，PALO 系统定期增加新的分区或删除历史分区。

• 同步物化视图

  • 用户可以在建表的同时创建多个同步物化视图（ROLLUP）。同步物化视图也可以在建表之后添加。写在建表语句中可以方便用户一次性创建所有同步物化视图。
  • 如果在建表时创建好同步物化视图，则后续的所有数据导入操作都会同步生成同步物化视图的数据。同步物化视图的数量可能会影响数据导入的效率。

• 索引

  • 用户可以在建表的同时创建多个列的索引。索引也可以在建表之后再添加。
  • 如果在之后的使用过程中添加索引，如果表中已有数据，则需要重写所有数据，因此索引的创建时间取决于当前数据量。

示例

基础示例

明细模型

1CREATE TABLE t1
2(
3  c1 INT,
4  c2 STRING
5)
6DUPLICATE KEY(c1)
7DISTRIBUTED BY HASH(c1)
8PROPERTIES (
9  'replication_num' = '1'
10)

聚合模型

1CREATE TABLE t2
2(
3  c1 INT,
4  c2 INT MAX
5)
6AGGREGATE KEY(c1)
7DISTRIBUTED BY HASH(c1)
8PROPERTIES (
9  'replication_num' = '1'
10)

主键模型

1CREATE TABLE t3
2(
3  c1 INT,
4  c2 INT
5)
6UNIQUE KEY(c1)
7DISTRIBUTED BY HASH(c1)
8PROPERTIES (
9  'replication_num' = '1'
10)

指定列的默认值

1CREATE TABLE t5
2(
3  c1 INT,
4  c2 INT DEFAULT 10
5)
6DUPLICATE KEY(c1)
7DISTRIBUTED BY HASH(c1)
8PROPERTIES (
9  'replication_num' = '1'
10)

分桶方式

1CREATE TABLE t6
2(
3  c1 INT,
4  c2 INT
5)
6DUPLICATE KEY(c1)
7DISTRIBUTED BY RANDOM
8PROPERTIES (
9  'replication_num' = '1'
10)

自动分区

1CREATE TABLE t7
2(
3  c1 INT,
4  c2 DATETIME NOT NULL
5)
6DUPLICATE KEY(c1)
7AUTO PARTITION BY RANGE(date_trunc(c2, 'day')) ()
8DISTRIBUTED BY RANDOM
9PROPERTIES (
10  'replication_num' = '1'
11)

range 分区

1CREATE TABLE t8
2(
3  c1 INT,
4  c2 DATETIME NOT NULL
5)
6DUPLICATE KEY(c1)
7PARTITION BY RANGE(c2) (
8  FROM ('2020-01-01') TO ('2020-01-10') INTERVAL 1 DAY
9)
10DISTRIBUTED BY RANDOM
11PROPERTIES (
12  'replication_num' = '1'
13)

list 分区

1CREATE TABLE t9
2(
3  c1 INT,
4  c2 DATE NOT NULL
5)
6DUPLICATE KEY(c1)
7PARTITION BY LIST(c2) (
8  PARTITION p1 VALUES IN (('2020-01-01'),('2020-01-02'))
9)
10DISTRIBUTED BY RANDOM
11PROPERTIES (
12  'replication_num' = '1'
13)

存储介质和冷却时间

1CREATE TABLE example_db.table_hash
2(
3    k1 BIGINT,
4    k2 LARGEINT,
5    v1 VARCHAR(2048),
6    v2 SMALLINT DEFAULT "10"
7)
8UNIQUE KEY(k1, k2)
9DISTRIBUTED BY HASH (k1, k2) BUCKETS 32
10PROPERTIES(
11    "storage_medium" = "SSD",
12    "storage_cooldown_time" = "2015-06-04 00:00:00"
13);

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

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

   SQL 复制

   1-- 非分区表
   2CREATE TABLE IF NOT EXISTS create_table_use_created_policy 
   3(
   4    k1 BIGINT,
   5    k2 LARGEINT,
   6    v1 VARCHAR(2048)
   7)
   8UNIQUE KEY(k1)
   9DISTRIBUTED BY HASH (k1) BUCKETS 3
   10PROPERTIES(
   11    "storage_policy" = "test_create_table_use_policy",
   12    "replication_num" = "1"
   13);
   14
   15-- 分区表
   16CREATE TABLE create_table_partion_use_created_policy
   17(
   18    k1 DATE,
   19    k2 INT,
   20    V1 VARCHAR(2048) REPLACE
   21) PARTITION BY RANGE (k1) (
   22    PARTITION p1 VALUES LESS THAN ("2022-01-01") ("storage_policy" = "test_create_table_partition_use_policy_1" ,"replication_num"="1"),
   23    PARTITION p2 VALUES LESS THAN ("2022-02-01") ("storage_policy" = "test_create_table_partition_use_policy_2" ,"replication_num"="1")
   24) DISTRIBUTED BY HASH(k2) BUCKETS 1;

Colocation Group

1CREATE TABLE t1 (
2    id int(11) COMMENT "",
3    value varchar(8) COMMENT ""
4)
5DUPLICATE KEY(id)
6DISTRIBUTED BY HASH(id) BUCKETS 10
7PROPERTIES (
8    "colocate_with" = "group1"
9);
10
11CREATE TABLE t2 (
12    id int(11) COMMENT "",
13    value1 varchar(8) COMMENT "",
14    value2 varchar(8) COMMENT ""
15)
16DUPLICATE KEY(`id`)
17DISTRIBUTED BY HASH(`id`) BUCKETS 10
18PROPERTIES (
19    "colocate_with" = "group1"
20);

索引

1CREATE TABLE example_db.table_hash
2(
3    k1 TINYINT,
4    k2 DECIMAL(10, 2) DEFAULT "10.5",
5    v1 CHAR(10) REPLACE,
6    v2 INT SUM,
7    INDEX k1_idx (k1) USING INVERTED COMMENT 'my first index'
8)
9AGGREGATE KEY(k1, k2)
10DISTRIBUTED BY HASH(k1) BUCKETS 32
11PROPERTIES (
12    "bloom_filter_columns" = "k2"
13);

设置表的副本属性

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

动态分区

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

1[types: [DATE]; keys: [2020-01-08]; ‥types: [DATE]; keys: [2020-01-09]; )
2[types: [DATE]; keys: [2020-01-09]; ‥types: [DATE]; keys: [2020-01-10]; )
3[types: [DATE]; keys: [2020-01-10]; ‥types: [DATE]; keys: [2020-01-11]; )
4[types: [DATE]; keys: [2020-01-11]; ‥types: [DATE]; keys: [2020-01-12]; )
5CREATE TABLE example_db.dynamic_partition
6(
7    k1 DATE,
8    k2 INT,
9    k3 SMALLINT,
10    v1 VARCHAR(2048),
11    v2 DATETIME DEFAULT "2014-02-04 15:36:00"
12)
13DUPLICATE KEY(k1, k2, k3)
14PARTITION BY RANGE (k1) ()
15DISTRIBUTED BY HASH(k2) BUCKETS 32
16PROPERTIES(
17    "dynamic_partition.time_unit" = "DAY",
18    "dynamic_partition.start" = "-3",
19    "dynamic_partition.end" = "3",
20    "dynamic_partition.prefix" = "p",
21    "dynamic_partition.buckets" = "32" 
22);

设置动态分区的副本属性

1CREATE TABLE example_db.dynamic_partition
2(
3    k1 DATE,
4    k2 INT,
5    k3 SMALLINT,
6    v1 VARCHAR(2048),
7    v2 DATETIME DEFAULT "2014-02-04 15:36:00"
8)
9PARTITION BY RANGE (k1) ()
10DISTRIBUTED BY HASH(k2) BUCKETS 32
11PROPERTIES(
12    "dynamic_partition.time_unit" = "DAY",
13    "dynamic_partition.start" = "-3",
14    "dynamic_partition.end" = "3",
15    "dynamic_partition.prefix" = "p",
16    "dynamic_partition.buckets" = "32",
17    "dynamic_partition.replication_allocation" = "tag.location.group_a:3"
18 );

CTAS 示例

1CREATE TABLE t10
2PROPERTIES (
3  'replication_num' = '1'
4)
5AS SELECT * FROM t1

CREATE TABLE ... LIKE 示例

1CREATE TABLE t11 LIKE t10