# 使用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语句创建分区表。 #### 语法格式 ``` 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 | 是 | 列字段的数据类型。数据类型为原生类型。 请参考[原生数据类型](https://support.huaweicloud.com/sqlref-spark-dli/dli_08_0058.html)。 | | 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等类型。 ``` 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关键字后。 ``` 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表中。 ``` 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中。 ``` 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类型。 具体使用方法与明细可以参照"数据类型 \>原生数据类型"。 请参考[原生数据类型](https://support.huaweicloud.com/sqlref-spark-dli/dli_08_0058.html)。 ``` 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，即删除或者覆盖的数据会不经过回收站，直接被删除。 ``` 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"表示。 ``` 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'; ```