使用 Hive 语法创建 OBS 表

功能描述

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

语法格式

CREATE [EXTERNAL] 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]

LOCATION 'obs_path'

[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]

关键字

● EXTERNAL：指创建OBS表。

● IF NOT EXISTS：指定该关键字以避免表已经存在时报错。

● COMMENT：字段或表描述。

● PARTITIONED BY：指定分区字段。

● ROW FORMAT：行数据格式。

● STORED AS：指定所存储的文件格式，当前该关键字只支持指定TEXTFILE, AVRO, ORC, SEQUENCEFILE, RCFILE, PARQUET, CARBON格式。

● LOCATION：指定OBS的路径。创建OBS表时必须指定此关键字。

● TBLPROPERTIES：TBLPROPERTIES子句允许用户给表添加key/value的属性。

比如开启数据多版本功能，用于表数据的备份与恢复。开启多版本功能后，在进 行删除或修改表数据时（insert overwrite或者truncate操作），系统会自动备份 历史表数据并保留一定时间，后续您可以对保留周期内的数据进行快速恢复，避 免因误操作而丢失数据。多版本功能其他SQL语法请参考开启或关闭数据多版本 和多版本备份恢复数据章节描述。

创建OBS表时，通过指定TBLPROPERTIES ("dli.multi.version.enable"="true") 开启DLI数据多版本功能，具体可以参考示例说明。

表1-10 TBLPROPERTIES 主要参数说明

key值 value说明

dli.multi.version.enable ● true：开启DLI数据多版本功能。

● false：关闭DLI数据多版本功能。

comment 表描述信息。

orc.compress orc存储格式表的一个属性，用来指定orc存储的压缩 方式。支持取值为：

● ZLIB

● SNAPPY

● NONE

key值 value说明

auto.purge 当设置为true时，删除或者覆盖的数据会不经过回收 站，直接被删除。

● AS：使用CTAS创建表。

参数说明

表1-11 参数说明

参数 描述

db_name Database名称，由字母、数字和下划线（_）组成。不能 是纯数字，且不能以数字和下划线开头。

table_name Database中的表名，由字母、数字和下划线（_）组成。

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

col_name 字段名称。

col_type 字段类型。

col_comment 字段描述。

row_format 行数据格式。

file_format OBS表存储格式，支持TEXTFILE, AVRO, ORC, SEQUENCEFILE, RCFILE, PARQUET, CARBON table_comment 表描述。

obs_path OBS存储路径。

key = value 设置TBLPROPERTIES具体属性和值。

例如开启DLI数据多版本时，可以设置

"dli.multi.version.enable"="true"来开启该功能。

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

注意事项

● 表名与列名为大小写不敏感，即不区分大小写。

● 表名及列名的描述仅支持字符串常量。

● 创建表时要声明列名及对应的数据类型，数据类型为原生类型。

● 当OBS的目录下文件夹与文件同名时，创建OBS表指向的路径会优先指向文件而 非文件夹。

● 创建分区表时，PARTITONED BY中指定分区列必须是不在表中的列，且需要指定 数据类型。分区列支持string, boolean, tinyint, smallint, short, int, bigint, long, decimal, float, double, date, timestamp等hive开源支持的类型。

● 支持指定多个分区字段，分区字段只需在PARTITIONED BY关键字后指定，不能像 普通字段一样在表名后指定，否则将出错。

● 单表分区数最多允许100000个。

● CTAS建表语句不能指定表的属性，不支持创建分区表。

示例

说明

执行创建表操作前，需要参考示例中先创建队列和数据库。再在“SQL编辑器”页面右侧的编辑 窗口上方，选择队列和数据库，执行以下SQL语句。

● 创建一张名为student的parquet格式表，该表包含字段id，name，score，其对应 的数据类型分别是INT，STRING，FLOAT。

CREATE TABLE student (id INT, name STRING, score FLOAT) STORED AS PARQUET LOCATION 'obs://

bucketName/filePath';

● 以班级号（classNo）为分区字段，创建一张名为student的表，包含姓名

（name）与分数（score）两个字段。

CREATE TABLE IF NOT EXISTS student(name STRING, score DOUBLE) PARTITIONED BY (classNo INT) STORED AS PARQUET LOCATION 'obs://bucketName/filePath';

说明

“classNo”为分区字段，需要在PARTITIONED BY关键字后指定，即“PARTITIONED BY (classNo INT)”，不能放在表名后作为表字段指定。

● 创建表t1，并将表t2的数据插入到表t1中（Hive语法）。

CREATE TABLE t1 STORED AS parquet LOCATION 'obs://bucketName/filePath' as select * from t2;

● 创建表student，并开启数据多版本功能（Hive语法）。

CREATE TABLE student (id INT, name STRING, score FLOAT) STORED AS PARQUET LOCATION 'obs://

bucketName/filePath' TBLPROPERTIES ("dli.multi.version.enable"="true");