使用DataSource语法创建DLI表
功能描述
使用DataSource语法创建DLI表。DataSource语法和Hive语法主要区别在于支持的表数据存储格式范围、支持的分区数等有差异,详细请参考语法格式和注意事项说明。
注意事项
- CTAS建表语句不能指定表的属性。
- 若没有指定分隔符,则默认为逗号(,)。
- 关于分区表的使用说明:
- 创建分区表时,PARTITIONED BY中指定分区列必须是表中的列,且必须在Column列表中指定类型。分区列只支持string, boolean, tinyint, smallint, short, int, bigint, long, decimal, float, double, date, timestamp类型。
- 创建分区表时,分区字段必须是表字段的最后一个字段或几个字段,且多分区字段的顺序也必须对应。否则将出错。
- 单表分区数最多允许200000个。
- 2024年1月后新注册使用DLI服务的用户,且使用Spark3.3及以上版本的引擎,在使用DataSource语法创建表时支持使用CTAS创建分区表。
语法格式
1 2 3 4 5 6 7 |
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name [(col_name1 col_type1 [COMMENT col_comment1], ...)] USING file_format [OPTIONS (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创建表。
参数说明
参数 |
是否必选 |
描述 |
---|---|---|
db_name |
否 |
Database名称。 由字母、数字和下划线(_)组成。不能是纯数字,且不能以数字和下划线开头。 |
table_name |
是 |
Database中的表名。 由字母、数字和下划线(_)组成。不能是纯数字,且不能以数字和下划线开头。匹配规则为:^(?!_)(?![0-9]+$)[A-Za-z0-9_$]*$。 特殊字符需要使用单引号('')包围起来。 表名对大小写不敏感,即不区分大小写。 |
col_name |
是 |
以逗号分隔的带数据类型的列名。 列名由字母、数字和下划线(_)组成。不能是纯数字,且至少包含一个字母。 列名为大小写不敏感,即不区分大小写。 |
col_type |
是 |
列字段的数据类型。数据类型为原生类型。 请参考原生数据类型。 |
col_comment |
否 |
列字段描述。仅支持字符串常量。 |
file_format |
是 |
DLI表数据存储格式,支持:parquet和orc格式。 |
table_comment |
否 |
表描述。仅支持字符串常量。 |
select_statement |
否 |
用于CTAS命令,将源表的select查询结果或某条数据插入到新创建的DLI表中。 |
示例1:创建DLI非分区表
示例说明:创建名为table1的DLI非分区表,使用USING关键字指定该表的存储格式为orc格式。
在您的实际使用中,还可以将DLI表存储为parquet类型。
1 2 3 4 |
CREATE TABLE IF NOT EXISTS table1 ( col_1 STRING, col_2 INT) USING orc; |
示例2:创建DLI分区表
示例说明:创建一个名为student的分区表,该分区表使用院系编号(facultyNo)和班级编号(classNo)进行分区,该student表会同时按照不同的院系编号(facultyNo)和不同的班级编号(classNo)分区。
在实际的使用过程中,您可以选择合适的分区字段并将其添加到PARTITIONED BY关键字后。
1 2 3 4 5 6 7 |
CREATE TABLE IF NOT EXISTS student ( Name STRING, facultyNo INT, classNo INT ) USING orc PARTITIONED BY (facultyNo, classNo); |
示例3:使用CTAS将源表的全部数据或部分数据创建新的DLI表
示例说明:根据示例1:创建DLI非分区表中创建的DLI表table1,使用CTAS语法将table1中的数据复制到table1_ctas表中。
在使用CTAS建表的时候,可以忽略被复制的表在建表时所使用的语法,即不论在创建table1时使用的是何种语法,都可以使用DataSource语法的CTAS创建table1_ctas。
此外,本例中table1DLI表的存储格式为orc,而table1_ctas表的存储格式可以为orc或者parquet,即CTAS创建的表存储格式可以不同于原表。
在AS关键字后使用SELECT语句选择需要的数据插入到table1_ctas表中。
SELECT语法为:SELECT <列名称> FROM <表名称> WHERE <相关筛选条件>。
- 示例中使用“SELECT * FROM table1”,'*'表示会从table1中选择所有列,并将table1中所有数据插入到table1_ctas表中。
1 2 3 4 5
CREATE TABLE IF NOT EXISTS table1_ctas USING parquet AS SELECT * FROM table1;
- 若需要按照自定义方式筛选数据插入table1_ctas中,可以使用如下的SELECT语句“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 ) USING parquet; |
示例5:创建DLI分区表,自定义表的OPTIONS参数
示例说明:创建DLI表时支持自定义属性名与属性值,OPTIONS参数说明可参考表2。
本例创建名为table3并以col_2为分区依据的DLI分区表。在OPTIONS中配置pmultiLevelDirEnable和compression。
- multiLevelDirEnable:本例设置为true,表示查询该表时会迭代读取表路径中的所有文件和子目录文件,若不需要此项配置可以设置为false或不设置(默认为false);
- compression:当创建的OBS表需要压缩时,可以使用compression关键字来配置压缩格式,本例中就使用了zstd压缩格式。
1 2 3 4 5 6 7 8 9 10
CREATE TABLE IF NOT EXISTs table3 ( col_1 STRING, col_2 int ) USING parquet PARTITIONED BY (col_2) OPTIONS ( multiLeveldirenable = true, compression = 'zstd' );
常见问题
- 使用default创建DataSource表时提示“xxx dli datasource v2 tables is only supported in spark3.3 or later version.”怎么办?
使用default创建DataSource表时引擎版本不低于Spark 3.3.1,如果引擎版本低于Spark 3.3.1则会提示上述错误信息,此时请切换使用Hive语法创建表。详细操作请参考使用Hive语法创建DLI表。
- 使用Spark 3.3.1执行jar作业报错 "xxx don`t support dli v1 table."怎么办?
该错误提示信息说明使用Spark 3.3.1执行Jar作业时不支持执行与该表相关的操作,请切换使用Hive语法重构表数据结构,例如使用Hive语法[STORED AS file_format] CTAS重新创建表后再执行作业。详细创建表操作请参考使用Hive语法创建DLI表。