更新时间:2022-08-12 GMT+08:00

使用DataSource语法创建DLI表

功能描述

使用DataSource语法创建DLI表。DataSource语法和Hive语法主要区别在于支持的表数据存储格式范围、支持的分区数等有差异,详细请参考语法格式和注意事项说明。

语法格式

1
2
3
4
5
6
7
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name 
  [(col_name1 col_type1 [COMMENT col_comment1], ...)]
  USING file_format 
  [OPTIONS (key1=val1, key2=val2, ...)] 
  [PARTITIONED BY (col_name1, col_name2, ...)]
  [COMMENT table_comment]
  [AS select_statement];

关键字

  • IF NOT EXISTS:指定该关键字以避免表已经存在时报错。
  • USING:指定存储格式。
  • OPTIONS:指定建表时的属性名与属性值。
  • COMMENT:字段或表描述。
  • PARTITIONED BY:指定分区字段。
  • AS:使用CTAS创建表。

参数说明

表1 参数描述

参数

描述

db_name

Database名称,由字母、数字和下划线(_)组成。不能是纯数字,且不能以数字和下划线开头。

table_name

Database中的表名,由字母、数字和下划线(_)组成。不能是纯数字,且不能以数字和下划线开头。匹配规则为:^(?!_)(?![0-9]+$)[A-Za-z0-9_$]*$。如果特殊字符需要使用单引号('')包围起来。

col_name

以逗号分隔的带数据类型的列名。列名由字母、数字和下划线(_)组成。不能是纯数字,且至少包含一个字母。

col_type

字段类型。

col_comment

字段描述。

file_format

DLI表数据存储格式,支持:parquet、carbon和carbondata格式。

table_comment

表描述。

select_statement

用于CTAS命令,将源表的select查询结果或某条数据插入到新创建的DLI表中。

表2 OPTIONS参数描述

参数

描述

默认值

multiLevelDirEnable

是否迭代查询子目录中的数据。当配置为true时,查询该表时会迭代读取该表路径中所有文件,包含子目录中的文件。

false

compression

指定压缩格式。一般为parquet格式时指定该参数,推荐使用'zstd'压缩格式。

-

注意事项

  • 若没有指定分隔符,则默认为逗号(,)。
  • 创建分区表时,PARTITONED BY中指定分区列必须是表中的列,且必须在Column列表中指定类型。分区列只支持string, boolean, tinyint, smallint, short, int, bigint, long, decimal, float, double, date, timestamp类型。
  • 创建分区表时,分区字段必须是表字段的最后一个字段或几个字段,且多分区字段的顺序也必须对应。否则将出错。
  • 单表分区数最多允许7000个。
  • CTAS建表语句不能指定表的属性,不支持创建分区表。

示例

  • 创建一张名为src的表,该表包含字段key、value,其对应的数据类型分别是INT、STRING,并指定表的压缩格式为'zstd'。
    1
    CREATE TABLE src(key INT, value STRING) USING PARQUET OPTIONS(compression = 'zstd');
    
  • 创建一张名为tb_carbon的表,存储数据格式为carbon,该表包含字段key、value,其对应的数据类型分别是INT、STRING,并可根据需要指定属性。
    1
    CREATE TABLE tb_carbon(key INT, value STRING) USING CARBON OPTIONS('key1' = 'value1');
    
  • 以班级号(classNo)为分区字段,创建一张名为student的表,包含姓名(name)与分数(score)两个字段,存储格式为parquet。
    1
    CREATE TABLE student(name STRING, score INT, classNo INT) USING PARQUET OPTIONS('key1' = 'value1') PARTITIONED BY(classNo) ;
    

    “classNo”为分区字段,在表字段中要放在最后一个,即“student(name STRING, score INT, classNo INT)”。

  • 以班级号(classNo)为分区字段,创建一张名为student的表,包含姓名(name)与分数(score)两个字段,存储格式为carbon。
    1
    CREATE TABLE student(name STRING, score INT, classNo INT) USING CARBON OPTIONS('key1' = 'value1') PARTITIONED BY(classNo) ;
    

    “classNo”为分区字段,在表字段中要放在最后一个,即“student(name STRING, score INT, classNo INT)”。

  • 创建表t1,并将表t2的数据插入到表t1中。
    1
    CREATE TABLE t1 USING parquet AS select * from t2;