DDL语句

DDL语句
- Create Database
- Create Table
- Drop Database
- Drop Table
- Alter Database
- Alter Table
- Cancel Alter

Create Database

该语句用于新建数据库(database)

语法：

CREATE DATABASE [IF NOT EXISTS] db_name;

举例：

新建数据库 db_test
CREATE DATABASE db_test;

Create Table

该语句用于创建表(table)

语法：

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

Column_definition

语法：

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

col_name：列名称
col_type：列类型，可以是INT，DOUBLE，DATE等，参考数据类型章节。
agg_type：聚合类型，目前支持SUM，MAX，MIN、REPLACE和HLL_UNION(仅用于HLL列，为HLL独有的聚合方式)5种。聚合类型是可选选项，如果不指定，说明该列是维度列（key列），否则是事实列（value列）。建表语句中，所有的key列必须在value列之前，一张表可以没有value列，这样的表就是维度表，但不能没有key列。该类型只对聚合模型(key_desc的type为AGGREGATE KEY)有用，其它模型不需要指定这个。
是否允许为NULL: 默认允许为NULL，导入时用\N来表示

说明：

在导入的时候，Palo会自动把相同key列对应的value列按照指定的聚合方法合并（针对聚合模型）。比如，Palo中有一张表包含三列：k1，k2和v，其中v是int类型的value列，聚合方法是SUM，k1和k2是key列。假如原本有数据如下

| k1  | k2  | v   |
|-----|-----|-----|
| 1   | 1   | 10  |
| 1   | 2   | 20  |
| 2   | 2   | 30  |

新导入的数据如下：

| k1  | k2  | v   |
|-----|-----|-----|
| 1   | 1   | 5   |
| 2   | 2   | 10  |
| 3   | 1   | 5   |

导入以后，Palo中的数据如下

| k1  | k2  | v   |
|-----|-----|-----|
| 1   | 1   | 15  |
| 1   | 2   | 20  |
| 2   | 2   | 40  |
| 3   | 1   | 5   |

可以看到，在k1和k2相同的时候，v列使用SUM聚合方法做了聚合。

ENGINE类型

说明：

ENGINE默认为olap，也就是由Palo提供存储支持，也可以选择mysql，broker。

mysql类型用来存储维表，由用户自己维护，方便修改。查询的时候，Palo可以自动实现mysql表和olap表的连接操作。使用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中的名字，二者可以不同。

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) 压缩格式。

key_desc

语法：

key_type(k1[,k2 ...])

说明：

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

key_type支持一下类型：

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

partition_desc

语法：

PARTITION BY RANGE (k1)
(
PARTITION partition_name VALUES LESS THAN MAXVALUE|("value1") [("key"="value")],
PARTITION partition_name VALUES LESS THAN MAXVALUE|("value2") [("key"="value")],
...
)

Partition使用指定的key列和指定的数据范围对数据进行分区，每个分区在物理上对应不同的数据块，便于快速过滤和按分区删除等操作。目前只支持按Range分区，只能有一个分区列，分区列必须是key列。注意，最后一个PARTITION从句之后没有逗号。

说明：

分区名称仅支持字母开头，并且只能由字母、数字和下划线组成
目前只支持以下类型的列作为分区列，且只能指定一个分区列TINYINT, SAMLLINT, INT, BIGINT, LARGEINT, DATE, DATETIME
分区为左闭右开区间，首个分区的左边界做为最小值
如果指定了分区，无法确定分区范围的导入数据会被过滤掉
每个分区后面的key-value键值对可以设置该分区的一些属性，目前支持如下属性：
storage_medium：用于指定该分区的初始存储介质，可选择SSD或HDD。默认为HDD。单节点SSD容量为50G，可以根据性能需求和数据量选择存储介质。
storage_cooldown_time：当设置存储介质为SSD时，指定该分区在SSD上的存储到期时间。默认存放7天。格式为："yyyy-MM-dd HH:mm:ss"。到期后数据会自动迁移到HDD上。
replication_num:指定分区的副本数。默认为3

distribution_desc

distribution用来指定如何分桶，可以选择Random分桶和Hash分桶两种分桶方式。

Random分桶语法：

DISTRIBUTED BY RANDOM [BUCKETS num]

Hash分桶语法：

DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]

说明：

Random使用所有key列进行哈希分桶，默认分区数为10。Hash使用指定的key列进行分桶，默认分区数为10。如果ENGINE类型为olap，必须指定分桶方式；如果是mysql则无须指定。
不建议使用Random分桶，建议使用Hash分桶。

properties

如果ENGINE类型为olap，则可以在properties中指定行存或列存

如果ENGINE类型为olap，且没有指定partition信息，可以在properties设置存储介质、存储到期时间和副本数等属性。如果指定了partition信息，需要为每个partition分别指定属性值，参考partition_desc

PROPERTIES (
"storage_medium" = "[SSD|HDD]",
["storage_cooldown_time" = "yyyy-MM-dd HH:mm:ss"],
["replication_num" = "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"
）

关于建表的补充说明

Partition和Distribution的说明：
Palo支持复合分区，第一级称为Partition，对应建表语句中的partition_desc从句；第二级称为Distribution，对应建表语句中的distribution_desc从句。Partition是可选的，如果建表时没有指定Partition，系统会自动创建唯一的一个Partition。Distribution必须显式指定。在以下场景中推荐创建Partition：
历史数据删除需求：如有删除历史数据的需求（比如仅保留最近N天的数据）。使用复合分区，可以通过删除历史分区来达到目的。
解决数据倾斜问题：每个分区可以单独指定分桶数量。如按天分区，当每天的数据量差异很大时，可以通过指定分区的分桶数，合理划分不同分区的数据。
如有按时间维度进行数据划分、导入、查询、删除、历史数据回溯等业务需求，推荐使用复合分区功能。
合理的表模式：

Palo中使用类似前缀索引的结构来提高查询性能。数据在Palo内部是按照key列排序的，并且组织为一个个Data Block。每个Data Block的第一行的前几列会被用作这个Data Block的索引，在数据导入时创建。该索引可以帮助Palo快速过滤一些Data Block。考虑到索引大小等因素，Palo最多使用一行的前36个字节作为索引，遇到VARCHAR类型则会中断，并且VARCHAR类型最多只使用字符串的前20个字节。下面举例说明。

表1的schema：

DDL语句 - 图1 )

前三列的长度和为（4+8+24=）36，正好36字节，所以前三列被用作前缀索引。

表2的schema：

DDL语句 - 图2 )

前两列的长度为（4+8=）12，没有达到36，但是第三列为varchar，所以前三列被用作索引，其中k3只去前20字节。

表3的schema：

DDL语句 - 图3 )

该表第一列是varchar类型，所以只有k3列的前20字节作为索引。

表4的schema：

DDL语句 - 图4 )

前四列的长度和为（8+8+8+8=）32，如果加上第五列（8个字节），就会超过36字节。所以只有前四列被用作索引。

如果对于表2和表3执行同样的语句：

SELECT * from tbl WHERE k1 = 12345;

表2的性能会明显由于表3，因为在表2中可以用到k1索引，而表3只有k3作为索引，该查询会进行扫全表的操作。因此，在建表时，应该尽量将频繁使用，选择度高的列放在前面，尽量不要将varchar类型放在前几列，尽量使用整型作为索引列。

举例：

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

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

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

CREATE TABLE example_db.table_hash
   (
   k1 BIGINT,
   k2 LARGEINT,
   v1 VARCHAR(2048),
   v2 SMALLINT DEFAULT "10"
   )
   ENGINE=olap
UNIQUE KEY(k1, k2)
   DISTRIBUTED BY HASH (k1, k2) BUCKETS 32
   PROPERTIES(
   "storage_type"="row"，
   "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")
   ("storage_medium" = "SSD", "storage_cooldown_time" = "2015-06-04 00:00:00"),
   PARTITION p2 VALUES LESS THAN ("2014-06-01")
   ("storage_medium" = "SSD", "storage_cooldown_time" = "2015-06-04 00:00:00"),
   PARTITION p3 VALUES LESS THAN ("2014-12-01")
   )
   DISTRIBUTED BY HASH(k2) BUCKETS 32;

说明：

这个语句会将数据划分成如下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 RANDOM BUCKETS 32
PROPERTIES ("storage_type"="column");

Drop Database

该语句用于删除数据库(database)

语法：

DROP DATABASE [IF EXISTS] db_name;

举例：

删除数据库 db_test

DROP DATABASE db_test;

Drop Table

该语句用于删除表(table)

语法：

DROP TABLE [IF EXISTS] [db_name.]table_name;

举例：

1.删除一个 table

DROP TABLE my_table;

2.如果存在，删除指定 database 的 table

DROP TABLE IF EXISTS example_db.my_table;

Alter Database

该语句用于设置指定数据库的配额。（仅管理员使用）

语法：

ALTER DATABASE db_name SET DATA QUOTA quota;

举例：

设置指定数据库数据量配额为1GB

ALTER DATABASE example_db SET DATA QUOTA 1073741824;

Alter Table

该语句用于对已有的table进行修改。该语句分为三种操作类型：partition、rollup和schema change。Partition是上文提到的复合分区中的第一级分区；rollup是物化索引相关的操作；schema change用来修改表结构。这三种操作不能同时出现在一条ALTER TABLE语句中。其中schema change和rollup是异步操作，任务提交成功则返回，之后可以使用SHOW ALTER命令查看进度。Partition是同步操作，命令返回表示执行完毕。

语法：

ALTER TABLE [database.]table alter_clause1[, alter_clause2, ...];

Alter_clause分为partition、rollup、schema change和rename四种。

partition支持的操作

增加分区

语法：

ADD PARTITION [IF NOT EXISTS] partition_name VALUES LESS THAN [MAXVALUE|("value1")] ["key"="value"] [DISTRIBUTED BY RANDOM [BUCKETS num] | DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]]

注意：

分区为左闭右开区间，用户指定右边界，系统自动确定左边界
如果没有指定分桶方式，则自动使用建表使用的分桶方式
如果已经指定分桶方式，则只能修改分桶数，不可修改分桶方式或分桶列
["key"="value"] 部分可以设置分区的一些属性，具体说明见CREATE TABLE

删除分区

语法：

DROP PARTITION [IF EXISTS] partition_name

注意：

使用分区方式的表至少要保留一个分区
执行 DROP PARTITION 一段时间内，可以通过 RECOVER 语句恢复被删除的 partition。详见 RECOVER 语句

修改分区属性

语法：

MODIFY PARTITION partition_name SET ("key" = "value", ...)

说明：

当前支持修改分区的 storage_medium、storage_cooldown_time 和replication_num 三个属性。
建表时没有指定partition时，partition_name同表名。

rollup支持的操作

rollup index类似于物化视图。建表完成之后，这张表中没有rollup index，只有一个base index，这个index的name和表名相同。用户可以为一张表建立一个或多个rollup index，每个rollup index包含base index中key和value列的一个子集，Palo会为这个子集生成独立的数据，用来提升查询性能。如果一个查询涉及到的列全部包含在一个rollup index中，Palo会选择扫瞄这个rollup index而不是全部的数据。用户可以根据自己应用的特点选择创建rollup index,rollup支持的操作：

创建 rollup index

语法：

ADD ROLLUP rollup_name (column_name1, column_name2, ...) [FROM from_index_name] [PROPERTIES ("key"="value", ...)]

注意：

如果没有指定from_index_name，则默认从base index创建
rollup表中的列必须是from_index中已有的列
在properties中，可以指定存储格式。具体请参阅 CREATE TABLE

删除 rollup index

语法：

DROP ROLLUP rollup_name
[PROPERTIES ("key"="value", ...)]

注意：

不能删除 base index
执行 DROP ROLLUP 一段时间内，可以通过 RECOVER 语句恢复被删除的 rollup index。详见 RECOVER 语句

schema change

Schema change操作用来修改表结构，包括添加列、删除列、修改列类型以及调整列顺序等。可以修改base index和rollup index的结构。

Schema change支持的操作：

向指定index的指定位置添加一列

语法：

ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
[AFTER column_name|FIRST]
[TO index_name]
[PROPERTIES ("key"="value", ...)]

注意：

聚合模型如果增加 value 列，需要指定agg_type
非聚合模型如果增加key列，需要指定KEY关键字
不能在rollup index中增加base index中已经存在的列。如有需要，可以重新创建一个 rollup index

向指定index添加多列

语法：

ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value", ...)
[TO index_name]
[PROPERTIES ("key"="value", ...)]

注意：

聚合模型如果增加 value 列，需要指定agg_type
非聚合模型如果增加key列，需要指定KEY关键字
不能在rollup index中增加base index中已经存在的列，如有需要，可以重新创建一个 rollup index。

从指定 index 中删除一列

语法：

DROP COLUMN column_name [FROM index_name]

注意：

不能删除分区列
如果是从base index中删除列，那么rollup index中如果包含该列，也会被删除

修改指定index的列类型以及列位置

语法：

MODIFY COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
[AFTER column_name|FIRST]
[FROM index_name]
[PROPERTIES ("key"="value", ...)]

注意：

聚合类型如果修改value列，需要指定agg_type
非聚合类型如果修改key列，需要指定KEY关键字
只能修改列的类型，列的其他属性维持原样（即其他属性需在语句中按照原属性显式的写出，参考schema change举例5）
分区列不能做任何修改
目前支持以下类型的转换（精度损失由用户保证）。TINYINT, SMALLINT, INT, BIGINT转换成TINYINT, SMALLINT, INT, BIGINT, DOUBLE。LARGEINT转换成DOUBLE 。VARCHAR支持修改最大长度
不支持从NULL转为NOT NULL

对指定index的列进行重新排序

语法：

ORDER BY (column_name1, column_name2, ...)
[FROM index_name]
[PROPERTIES ("key"="value", ...)]

注意：

index中的所有列都要写出来
value列在key列之后

rename

Rename操作用来修改表名、rollup index名称和partition名称

Rename支持的操作：

修改表名

语法：

RENAME new_table_name

修改rollup index名称

语法：

RENAME ROLLUP old_rollup_name new_rollup_name

修改partition名称

语法：

RENAME PARTITION old_partition_name new_partition_name

举例：

1.增加分区, 现有分区 [MIN, 2013-01-01)，增加分区[2013-01-01, 2014-01-01)，使用默认分桶方式

ALTER TABLE example_db.my_table
ADD PARTITION p1 VALUES LESS THAN ("2014-01-01");

2.增加分区，使用新的分桶方式

ALTER TABLE example_db.my_table
ADD PARTITION p1 VALUES LESS THAN ("2015-01-01")
DISTRIBUTED BY RANDOM BUCKETS 20;

3.删除分区

ALTER TABLE example_db.my_table
DROP PARTITION p1;

4.创建index: example_rollup_index，基于 base index（k1,k2,k3,v1,v2）,列式存储。

ALTER TABLE example_db.my_table
ADD ROLLUP example_rollup_index(k1, k3, v1, v2)
PROPERTIES("storage_type"="column");

5.创建index: example_rollup_index2，基于example_rollup_index（k1,k3,v1,v2）

ALTER TABLE example_db.my_table
ADD ROLLUP example_rollup_index2 (k1, v1)
FROM example_rollup_index;

6.删除index: example_rollup_index2

ALTER TABLE example_db.my_table
DROP ROLLUP example_rollup_index2;

7.向example_rollup_index的col1后添加一个key列new_col(非聚合模型)

ALTER TABLE example_db.my_table
ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
TO example_rollup_index;

8.向example_rollup_index的col1后添加一个value列new_col(非聚合模型)

ALTER TABLE example_db.my_table
ADD COLUMN new_col INT DEFAULT "0" AFTER col1
TO example_rollup_index;

9.向example_rollup_index的col1后添加一个key列new_col(聚合模型)

ALTER TABLE example_db.my_table
ADD COLUMN new_col INT DEFAULT "0" AFTER col1
TO example_rollup_index;

10.向example_rollup_index的col1后添加一个value列new_col SUM聚合类型(聚合模型)

ALTER TABLE example_db.my_table
ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1
TO example_rollup_index;

11.向 example_rollup_index 添加多列（聚合模型）

ALTER TABLE example_db.my_table
ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
TO example_rollup_index;

12.从example_rollup_index删除一列

ALTER TABLE example_db.my_table
DROP COLUMN col2
FROM example_rollup_index;

13.修改base index的col1列的类型为BIGINT，并移动到col2列后面

ALTER TABLE example_db.my_table
MODIFY COLUMN col1 BIGINT DEFAULT "1" AFTER col2;

14.修改base index的val1列最大长度。原val1为(val1 VARCHAR(32) REPLACE DEFAULT "abc")

ALTER TABLE example_db.my_table
MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";

15.重新排序example_rollup_index中的列（设原列顺序为：k1,k2,k3,v1,v2）

ALTER TABLE example_db.my_table
ORDER BY (k3,k1,k2,v2,v1)
FROM example_rollup_index;

16.同时执行两种操作

ALTER TABLE example_db.my_table
ADD COLUMN v2 INT MAX DEFAULT "0" AFTER k2 TO example_rollup_index,
ORDER BY (k3,k1,k2,v2,v1) FROM example_rollup_index;

17.修改表的 bloom filter 列

ALTER TABLE example_db.my_table
PROPERTIES ("bloom_filter_columns"="k1,k2,k3");

18.将名为 table1 的表修改为 table2

ALTER TABLE table1 RENAME table2;

19.将表 example_table 中名为 rollup1 的 rollup index 修改为 rollup2

ALTER TABLE example_table RENAME ROLLUP rollup1 rollup2;

20.将表 example_table 中名为 p1 的 partition 修改为 p2

ALTER TABLE example_table RENAME PARTITION p1 p2;

Cancel Alter

该语句用于撤销一个alter操作

撤销alter table column (即schema change)语法：

CANCEL ALTER TABLE COLUMN FROM db_name.table_name

撤销alter table rollup操作

CANCEL ALTER TABLE ROLLUP FROM db_name.table_name

举例：

1.撤销针对 my_table 的 ALTER COLUMN 操作。

CANCEL ALTER TABLE COLUMN
FROM example_db.my_table;

2.撤销 my_table 下的 ADD ROLLUP 操作。

CANCEL ALTER TABLE ROLLUP
FROM example_db.my_table;