使用 DataSource 语法创建 OBS 表

功能描述

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

语法格式

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name [(col_name1 col_type1 [COMMENT col_comment1], ...)]

USING file_format

[OPTIONS (path 'obs_path', 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-7 参数说明

参数 描述

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

table_name Database中的表名，由字母、数字和下划线（_）组成。不能是 纯数字，且不能以数字和下划线开头。匹配规则为：^(?!_)(?!

[0-9]+$)[A-Za-z0-9_$]*$。如果特殊字符需要使用单引号（''）包 围起来。

col_name 字段名称。

col_type 字段类型。

col_comment 字段描述。

file_format OBS表数据存储格式，支持orc，parquet，json，csv，carbon，

avro类型。

path 数据存储路径。

table_commen

t 表描述。

select_stateme

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

表1-8 OPTIONS 参数描述

参数 描述 默认值

path 指定的表路径，即OBS存储路径。

-multiLevelDirE

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

false

dataDelegated 是否需要在删除表或分区时，清除path路径下的

数据。 false

当file_format为csv时，还可以设置以下OPTIONS参数。

表1-9 CSV 数据格式 OPTIONS 参数说明

参数 描述 默认值

delimiter 数据分隔符。 逗号（即",”）

quote 引用字符。 双引号（即

“"”）

escape 转义字符。 反斜杠（即

“\”）

multiLine 列数据中是否包含回车符或转行符，true为包

含，false为不包含 false

dateFormat 指定CSV文件中date字段的日期格式 yyyy-MM-dd timestampF

ormat 指定CSV文件中timestamp字段的日期格式 yyyy-MM-dd HH:mm:ss mode 指定解析CSV时的模式，有三种模式。

● PERMISSIVE：宽容模式，遇到错误的字段 时，设置该行整行为Null

● DROPMALFORMED: 遇到错误的字段时，丢 弃整行。

● FAILFAST：报错模式，遇到错误的字段时直 接报错。

PERMISSIVE

header CSV是否包含表头信息，true表示包含表头信

息，false为不包含。 false

nullValue 设置代表null的字符，例如，nullValue=“\

\N”表示设置\N 代表null。 -comment 设置代表注释开头的字符，例如，

comment='#'表示以#开头的行为注释。

-注意事项

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

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

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

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

● 创建表时，若指定路径为OBS上的目录，且该目录下包含子目录（或嵌套子目 录），则子目录下的所有文件类型及其内容也是表内容。用户需要保证所指定的 目录及其子目录下所有文件类型和建表语句中指定的存储格式一致，所有文件内 容和表中的字段一致，否则查询将报错。用户可以在建表语句OPTIONS中设置

“multiLevelDirEnable”为true以查询子目录下的内容，此参数默认值为false

（注意，此配置项为表属性，请谨慎配置）(Hive表不支持此配置项)。

● OBS存储路径必须为OBS上的目录，该目录必须事先创建好，且为空。

● 创建分区表时，PARTITONED BY中指定分区列必须是表中的列，且必须在 Column列表中指定类型。分区列只支持string, boolean, tinyint, smallint, short, int, bigint, long, decimal, float, double, date, timestamp类型。

● 创建分区表时，分区字段必须是表字段的最后一个字段或几个字段，且多分区字 段的顺序也必须对应。否则将出错。

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

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

示例

说明

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

● 创建名为parquetTable的OBS表。

CREATE TABLE parquetTable (name string, id int) USING parquet OPTIONS (path "obs://bucketName/

filePath");

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

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

CREATE TABLE IF NOT EXISTS student(name STRING, score DOUBLE, classNo INT) USING csv OPTIONS (PATH 'obs://bucketName/filePath') PARTITIONED BY (classNo);

说明

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

● 创建表t1，并将表t2的数据插入到表t1中。

CREATE TABLE t1 USING parquet OPTIONS(path 'obs://bucketName/tblPath') AS select * from t2;