更新时间:2024-07-04 GMT+08:00

使用Hive语法创建DLI表

功能描述

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

注意事项

  • CTAS建表语句不能指定表的属性。
  • Hive DLI表不支持在建表时指定多字符的分隔符。
  • 关于分区表的使用说明:
    • 创建分区表时,PARTITONED BY中指定分区列必须是不在表中的列,且需要指定数据类型。分区列支持string, boolean, tinyint, smallint, short, int, bigint, long, decimal, float, double, date, timestamp等hive开源支持的类型。
    • 支持指定多个分区字段,分区字段只需在PARTITIONED BY关键字后指定,不能像普通字段一样在表名后指定,否则将出错。
    • 单表分区数最多允许200000个。
    • Spark 3.3及以上版本支持使用Hive语法的CTAS语句创建分区表。

语法格式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name 
  [(col_name1 col_type1 [COMMENT col_comment1], ...)]
  [COMMENT table_comment] 
  [PARTITIONED BY (col_name2 col_type2, [COMMENT col_comment2], ...)] 
  [ROW FORMAT row_format]
  STORED AS file_format 
  [TBLPROPERTIES (key = value)]
  [AS select_statement];

row_format:
  : SERDE serde_cls [WITH SERDEPROPERTIES (key1=val1, key2=val2, ...)]
  | DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]]
      [COLLECTION ITEMS TERMINATED BY char]
      [MAP KEYS TERMINATED BY char]
      [LINES TERMINATED BY char]
      [NULL DEFINED AS char]

关键字

  • IF NOT EXISTS:指定该关键字以避免表已经存在时报错。
  • COMMENT:字段或表描述。
  • PARTITIONED BY:指定分区字段。
  • ROW FORMAT:行数据格式。
  • STORED AS:指定所存储的文件格式,当前该关键字只支持指定TEXTFILE, AVRO, ORC, SEQUENCEFILE, RCFILE, PARQUET几种格式。创建DLI表时必须指定此关键字。
  • TBLPROPERTIES:用于为表添加key/value的属性。
    • 在表存储格式为PARQUET时,可以通过指定TBLPROPERTIES(parquet.compression = 'zstd')来指定表压缩格式为zstd。
  • AS:使用CTAS创建表。

参数说明

表1 参数描述

参数

是否必选

描述

db_name

Database名称。

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

table_name

Database中的表名。

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

col_name

列字段名称。

列字段由字母、数字和下划线(_)组成。不能是纯数字,且至少包含一个字母。

列名为大小写不敏感,即不区分大小写。

col_type

列字段的数据类型。数据类型为原生类型。

请参考原生数据类型

col_comment

列字段描述。仅支持字符串常量。

row_format

行数据格式。row format功能只支持textfile类型的表。

file_format

DLI表数据存储格式:支持textfile, avro, orc, sequencefile, rcfile, parquet。

table_comment

表描述。仅支持字符串常量。

key = value

设置TBLPROPERTIES具体属性和值。

在表存储格式为PARQUET时,可以通过指定TBLPROPERTIES(parquet.compression = 'zstd')来指定表压缩格式为zstd。

select_statement

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

示例1:创建DLI非分区表

示例说明:创建名为table1的DLI非分区表,并用STORED AS关键字指定该表的存储格式为orc格式。

在您的实际使用中,可以将DLI表存储为textfile, avro, orc, sequencefile, rcfile, parquet等类型。

1
2
3
4
5
CREATE TABLE IF NOT EXISTS table1 (
    col_1   STRING,
    col_2   INT
)
STORED AS orc;

示例2:创建DLI分区表

示例说明:创建一个名为student的分区表,该分区表使用院系编号(facultyNo)和班级编号(classNo)进行分区,该student表会同时按照不同的院系编号(facultyNo)和不同的班级编号(classNo)分区。

在实际的使用过程中,您可以选择合适的分区字段并将其添加到PARTITIONED BY关键字后。

1
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS student(
    id      int,
    name    STRING
)
STORED AS avro
PARTITIONED BY (
    facultyNo   INT,
    classNo     INT
);

示例3:使用CTAS语句将源表的全部数据或部分数据创建新的DLI表

示例说明:根据示例1:创建DLI非分区表中创建的DLI表table1,使用CTAS语法将table1中的数据复制到table1_ctas表中。

在使用CTAS建表的时候,可以忽略被复制的表在建表时所使用的语法,即不论在创建table1时使用的是何种语法,都可以使用DataSource语法的CTAS创建table1_ctas。

本例中table1中DLI表的存储格式为orc,而table1_ctas表的存储格式可以为parquet,即CTAS创建的表存储格式可以不同于原表。

在AS关键字后使用select语句选择需要插入到table1_ctas表中的数据。

SELECT语法为:SELECT <列名称> FROM <表名称> WHERE <相关筛选条件>。

  • 示例中使用select * from table1,表示会从table1中选择所有语句,并将这些语句复制到table1_ctas表中。
    1
    2
    3
    4
    5
    CREATE TABLE IF NOT EXISTS table1_ctas
    STORED AS sequencefile
    AS
    SELECT  *
    FROM table1;
    
  • 若不需要table1中的全部数据,可以将“AS SELECT * FROM table1”改为“AS SELECT col_1 FROM table1 WHERE col_1 = “Ann”,这样就可以通过执行SELECT语句从table1中单独指定col_1列等于'Ann'的所有行插入到table1_ctas中。
    1
    2
    3
    4
    5
    6
    CREATE TABLE IF NOT EXISTS table1_ctas
    USING parquet
    AS
    SELECT col_1
    FROM table1
    WHERE col_1 = 'Ann';
    

示例4:创建DLI非分区表,并自定义列字段数据类型

示例说明:创建名为table2的DLI非分区表,您可以根据业务需求自定义列子段的原生数据类型:

  • 与文字字符有关可以使用STRING、CHAR或者VARCHAR。
  • 与时间有关的可以使用TIMESTAMP、DATE。
  • 与整数有关的可以使用INT、SMALLINT/SHORT、BIGINT/LONG、TINYINT。
  • 涉及小数运算可以使用FLOAT、DOUBLE、DECIMAL。
  • 若数据只涉及逻辑开关可以使用BOOLEAN类型。

具体使用方法与明细可以参照“数据类型 >原生数据类型”。

请参考原生数据类型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
CREATE TABLE IF NOT EXISTS table2 (
    col_01  STRING,
    col_02  CHAR (2),
    col_03  VARCHAR (32),
    col_04  TIMESTAMP,
    col_05  DATE,
    col_06  INT,
    col_07  SMALLINT,
    col_08  BIGINT,
    col_09  TINYINT,
    col_10  FLOAT,
    col_11  DOUBLE,
    col_12  DECIMAL (10, 3),
    col_13  BOOLEAN
)
STORED AS parquet;

示例5:创建DLI分区表,自定义表的TBLPROPERTIES参数

示例说明:本例创建名为table3并以col_3为分区依据的DLI分区表。在TBLPROPERTIES中配置dli.multi.version.enable、comment、orc.compress和auto.purge。

  • dli.multi.version.enable:本例配置为true,即代表开启DLI数据多版本功能,用于表数据的备份与恢复。
  • comment:表描述信息,TBLPROPERTIES内的描述信息支持后续修改。
  • orc.compress:指定orc存储的压缩方式,本例定义为ZLIB。
  • auto.purge:本例配置为true,即删除或者覆盖的数据会不经过回收站,直接被删除。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE TABLE IF NOT EXISTs table3 (
    col_1 STRING,
    col_2 STRING
)
PARTITIONED BY (col_3 DATE)
STORED AS rcfile
TBLPROPERTIES (
    dli.multi.version.enable    = true,
    comment                     = 'Created by dli',
    orc.compress                = 'ZLIB',
    auto.purge                  = true
);

示例6:创建textfile格式的非分区表,并设置ROW FORMAT

示例说明:本例创建名为table4的textfile类型的非分区表,并设置ROW FORMAT相关格式(ROW FORMAT功能只支持textfile类型的表)。

  • 字段(Fields)是表格中的列,每个字段有一个名称和数据类型,表中字段之间以'/'分隔。
  • 集合项(COLLECTION ITEMS)指的是一组数据中的元素,可以是数组、列表或集合等,table4中集合项以'$'分隔。
  • 映射键(MAP KEYS)是一种键值对的数据结构,用于存储一组相关联的数据,表中Map键以'#'分隔。
  • 行(Rows)表格中的行,每一行包含一组字段值,表中行以'\n'结束(注意,只支持用'\n'作为行分隔符)。
  • NULL表示缺少值或未知值的特殊值。在表格中,NULL表示该字段没有值或该值未知。如果数据中存在null值,则用字符串“null”表示。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE IF NOT EXISTS table4 (
    col_1   STRING,
    col_2   INT
)
STORED AS TEXTFILE
ROW FORMAT
DELIMITED FIELDS TERMINATED   BY '/'
COLLECTION ITEMS TERMINATED   BY '$'
MAP KEYS TERMINATED           BY '#'
LINES TERMINATED              BY '\n'
NULL DEFINED                  AS 'NULL';