使用Hive语法创建OBS表
功能描述
使用Hive语法创建OBS表。DataSource语法和Hive语法主要区别在于支持的表数据存储格式范围、支持的分区数等有差异,详细请参考语法格式和注意事项说明。
使用说明
- 创建表时会统计大小。
- 添加数据时不会修改大小。
- 如需查看表大小可以通过OBS查看。
语法格式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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格式。
- LOCATION:指定OBS的路径。创建OBS表时必须指定此关键字。
- TBLPROPERTIES:TBLPROPERTIES子句允许用户给表添加key/value的属性。
比如开启数据多版本功能,用于表数据的备份与恢复。开启多版本功能后,在进行删除或修改表数据时(insert overwrite或者truncate操作),系统会自动备份历史表数据并保留一定时间,后续您可以对保留周期内的数据进行快速恢复,避免因误操作而丢失数据。多版本功能其他SQL语法请参考开启或关闭数据多版本和多版本备份恢复数据章节描述。
创建OBS表时,通过指定TBLPROPERTIES ("dli.multi.version.enable"="true")开启DLI数据多版本功能,具体可以参考示例说明。
表1 TBLPROPERTIES主要参数说明 key值
value说明
dli.multi.version.enable
- true:开启DLI数据多版本功能。
- false:关闭DLI数据多版本功能。
comment
表描述信息。
orc.compress
orc存储格式表的一个属性,用来指定orc存储的压缩方式。支持取值为:
- ZLIB
- SNAPPY
- NONE
auto.purge
当设置为true时,删除或者覆盖的数据会不经过回收站,直接被删除。
- AS:使用CTAS创建表。
参数说明
参数 |
描述 |
---|---|
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 |
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建表语句不能指定表的属性,不支持创建分区表。
示例
- 创建一张名为student的parquet格式表,该表包含字段id,name,score,其对应的数据类型分别是INT,STRING,FLOAT。
1
CREATE TABLE student (id INT, name STRING, score FLOAT) STORED AS PARQUET LOCATION 'obs://bucketName/filePath';
- 以班级号(classNo)为分区字段,创建一张名为student的表,包含姓名(name)与分数(score)两个字段。
1
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语法)。
1
CREATE TABLE t1 STORED AS parquet LOCATION 'obs://bucketName/filePath' as select * from t2;
- 创建表student,并开启数据多版本功能(Hive语法)。
1
CREATE TABLE student (id INT, name STRING, score FLOAT) STORED AS PARQUET LOCATION 'obs://bucketName/filePath' TBLPROPERTIES ("dli.multi.version.enable"="true");