使用DataSource语法创建OBS表_数据湖探索 DLI

功能描述

本节介绍使用DataSource语法创建OBS表。

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

推荐使用OBS并行文件系统进行存储。并行文件系统是一种高性能文件系统，提供毫秒级别访问时延，TB/s级别带宽和百万级别的IOPS，适用于大数据交互式分析场景。

注意事项

创建表时不会统计大小。
添加数据时会修改大小至0。
如需查看表大小可以通过OBS查看。
CTAS建表语句不能指定表的属性。
OBS目录下包含子目录的场景：
创建表时，若指定路径为OBS上的目录，且该目录下包含子目录（或嵌套子目录），则子目录下的所有文件类型及其内容也是表内容。

您需要保证所指定的目录及其子目录下所有文件类型和建表语句中指定的存储格式一致，所有文件内容和表中的字段一致，否则查询将报错。

您可以在建表语句OPTIONS中设置“multiLevelDirEnable”为true以查询子目录下的内容，此参数默认值为false（注意，此配置项为表属性，请谨慎配置。Hive表不支持此配置项)。
关于分区表的使用说明：
- 创建分区表时，PARTITIONED BY中指定分区列必须是表中的列，且必须在Column列表中指定类型。分区列只支持string, boolean, tinyint, smallint, short, int, bigint, long, decimal, float, double, date, timestamp类型。
- 创建分区表时，分区字段必须是表字段的最后一个字段或几个字段，且多分区字段的顺序也必须对应。否则将出错。
- 单表分区数最多允许200000个。
- 2024年1月后新注册使用DLI服务的用户，且使用Spark3.3及以上版本的引擎，在使用DataSource语法创建表时支持使用CTAS创建分区表。

语法格式

    
         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 参数说明
参数	是否必选	描述
db_name	否	Database名称。由字母、数字和下划线（_）组成。不能是纯数字，且不能以数字和下划线开头。
table_name	是	Database中的待创建的表名。由字母、数字和下划线（_）组成。不能是纯数字，且不能以数字和下划线开头。匹配规则为：^(?!_)(?![0-9]+$)[A-Za-z0-9_$]*$。特殊字符需要使用单引号（''）包围起来。表名对大小写不敏感，即不区分大小写。
col_name	是	以逗号分隔的带数据类型的列名。列名由字母、数字和下划线（_）组成。不能是纯数字，且至少包含一个字母。列名为大小写不敏感，即不区分大小写。
col_type	是	列字段的数据类型。数据类型为原生类型。请参考原生数据类型。
col_comment	否	列字段描述。仅支持字符串常量。
file_format	是	file_format是用于创建表的输入格式。支持orc，parquet，json，csv，avro类型。
path	是	数据文件所在的OBS存储路径，推荐使用OBS并行文件系统存储。格式：obs://bucketName/tblPath bucketName即桶名称。 tblPath是目录名称。目录后不需要指定文件名。更多建表时的属性名与属性值请参考表2。 file_format为csv时表的属性名与属性值请参考表2和表3。当OBS的目录下文件夹与文件同名时，创建OBS表指向的路径会优先指向文件而非文件夹。
table_comment	否	表描述信息。仅支持字符串常量。
select_statement	否	用于CTAS命令，将源表的select查询结果或某条数据插入到新创建的OBS表中。

表2 OPTIONS参数描述
参数	是否必选	描述
path	否	指定的表路径，即OBS存储路径。
multiLevelDirEnable	否	嵌套子目录场景下，是否迭代查询子目录中的数据。当配置为true时，查询该表时会迭代读取该表路径中所有文件，包含子目录中的文件。默认值：false
dataDelegated	否	是否需要在删除表或分区时，清除path路径下的数据。默认值：false
compression	否	指定压缩格式。一般为parquet格式时指定该参数，推荐使用'zstd'压缩格式。

当file_format为csv时，支持设置以下OPTIONS参数。

表3 CSV数据格式OPTIONS参数说明
参数	是否必选	描述
delimiter	否	数据分隔符。默认值：逗号（即",”）
quote	否	引用字符。默认值：双引号（即“"”）
escape	否	转义字符。默认值：反斜杠（即“\”）
multiLine	否	列数据中是否包含回车符或转行符，true为包含，false为不包含。默认值：false
dateFormat	否	指定CSV文件中date字段的日期格式。默认值：yyyy-MM-dd
timestampFormat	否	指定CSV文件中timestamp字段的日期格式。默认值： yyyy-MM-dd HH:mm:ss
mode	否	指定解析CSV时的模式，有三种模式。默认值：PERMISSIVE PERMISSIVE：宽容模式，遇到错误的字段时，设置该字段为Null DROPMALFORMED: 遇到错误的字段时，丢弃整行。 FAILFAST：报错模式，遇到错误的字段时直接报错。
header	否	CSV是否包含表头信息，true表示包含表头信息，false为不包含。默认值：false
nullValue	否	设置代表null的字符，例如，nullValue="nl"表示设置nl代表null。
comment	否	设置代表注释开头的字符，例如，comment='#'表示以#开头的行为注释。
compression	否	设置数据的压缩格式。目前支持gzip、bzip2、deflate压缩格式，若不希望压缩，则输入none。默认值：none
encoding	否	数据的编码格式。支持utf-8，gb2312，gbk三种，如果不填写，则默认为utf-8。默认值：utf-8

示例1：创建OBS非分区表

示例说明：创建名为table1的OBS非分区表，使用USING关键字指定该表的存储格式为orc格式。

在您的实际使用中，可以将obs表存储为parquet、json、avro等类型。

    
         CREATE TABLE IF NOT EXISTS table1 (
    col_1   STRING,
    col_2   INT)
USING orc
OPTIONS (path 'obs://bucketName/filePath');

示例2：创建OBS分区表

示例说明：创建一个名为student的分区表，该分区表使用院系编号（facultyNo）和班级编号（classNo）进行分区。该student表会同时按照不同的院系编号（facultyNo）和不同的班级编号（classNo）分区。

在实际的使用过程中，您可以选择合适的分区字段并将其添加到PARTITIONED BY关键字后的括号内。

    
         CREATE TABLE IF NOT EXISTS student (
    Name        STRING,
    facultyNo   INT,
    classNo     INT)
USING csv
OPTIONS (path 'obs://bucketName/filePath')
PARTITIONED BY (facultyNo, classNo);

示例3：使用CTAS将源表的全部数据或部分数据创建新的OBS非分区表

示例说明：根据示例1：创建OBS非分区表中创建的OBS表table1，使用CTAS语法将table1中的数据复制到table1_ctas表中。

在使用CTAS建表的时候，可以忽略被复制的表在建表时所使用的语法，即不论在创建table1时使用的是何种语法，都可以使用DataSource语法的CTAS创建table1_ctas。

此外，本例中table1中OBS表的存储格式为orc，而table1_ctas表的存储格式可以为parquet，即CTAS创建的表存储格式可以不同于原表。

在AS关键字后使用SELECT语句选择需要的数据插入到table1_ctas表中。

SELECT语法为：SELECT <列名称> FROM <表名称> WHERE <相关筛选条件>。

示例中使用“SELECT * FROM table1”，'*'表示会从table1中选择所有列，并将table1中所有数据插入到table1_ctas表中。

      
           CREATE TABLE IF NOT EXISTS table1_ctas
USING parquet
OPTIONS (path 'obs:// bucketName/filePath')
AS
SELECT  *
FROM    table1;

若需要按照自定义方式筛选数据插入table1_ctas中，可以使用如下的SELECT语句“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
OPTIONS (path 'obs:// bucketName/filePath')
AS
SELECT  col_1
FROM    table1
WHERE   col_1 = 'Ann';

示例4：创建OBS非分区表，并自定义列字段数据类型

示例说明：创建名为table2的OBS非分区表，您可以根据业务需求自定义列字段的原生数据类型：

与文字字符有关可以使用STRING、CHAR或者VARCHAR。
与时间有关的可以使用TIMESTAMP、DATE。
与整数有关的可以使用INT、SMALLINT/SHORT、BIGINT/LONG、TINYINT。
涉及小数运算可以使用FLOAT、DOUBLE、DECIMAL。
若数据只涉及逻辑开关可以使用BOOLEAN类型。

具体使用方法与明细可以参照“数据类型 > 原生数据类型”。

请参考原生数据类型。

    
     
       
       
         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
OPTIONS (path 'obs://bucketName/filePath');

        

      

    
   

示例5：创建OBS分区表，自定义表的OPTIONS参数

示例说明：创建OBS表时支持自定义属性名与属性值，OPTIONS参数说明可参考表2。

本例创建名为table3并以col_2为分区依据的OBS分区表。在OPTIONS中配置path、multiLevelDirEnable、dataDelegated和compression。

path：OBS存储路径，本例为“obs ://bucketName/filePath”，其中的bucketName为您存储时所使用桶名称，filePath为您实际使用的目录名称；
请注意大数据场景建议使用OBS并行文件系统进行存储；
multiLevelDirEnable：本例设置为true，表示查询该表时会迭代读取表路径中的所有文件和子目录文件，若不需要此项配置可以设置为false或不设置（默认为false）；
dataDelegated：本例设置为true，表示在删除表或相关分区时，会一并清除改path路径下的所有数据，若不需要此项配置可以设置为false或不设置（默认为false）；
compression：当创建的OBS表需要压缩时，可以使用compression关键字来配置压缩格式，本例中就使用了zstd压缩格式。

    
         CREATE TABLE IF NOT EXISTS table3 (
    col_1   STRING,
    col_2   int
)
USING parquet
PARTITIONED BY (col_2)
OPTIONS (
    path 'obs://bucketName/filePath',
    multiLeveldirenable = true,
    datadelegated = true,
    compression = 'zstd'
);

示例6：创建OBS非分区表，自定义表的OPTIONS参数

示例说明：CSV表是一种以逗号分隔的纯文本文件格式，用于存储和交换数据。它通常用于简单的数据交换，但是它没有结构化数据的概念，因此不适合存储复杂数据类型。于是当file_format为csv时，支持配置更多的OPTIONS参数（参考表3）。

本例创建一个名为table4且存储格式为csv非分区表并使用了额外的OPTIONS参数对数据加以约束。

delimiter：数据分隔符，表示使用逗号（，）作为数据之间的分隔符；
quote：引用字符，表示使用双引号（”）来表示数据中的引用信息；
escape：转义字符，表示使用反斜杠（\）作为数据存储时的转义字符；
multiLine：设置需要存储的列数据中不包含回车符或者换行符；
dataFormat：表示该csv文件中data字段的指定日期格式为yyyy-MM-dd；
timestamoFormat：表示该csv文件中会将时间戳格式指定为yyyy-MM-dd HH:mm:ss；
header：表示该csv表中包含表头信息；
nullValue：表示设置null来表示csv表中的null值；
comment：表示该csv表使用斜杠（/）表示注释的开头；
compression：表示该csv表被压缩，此处csv表支持gzip、bzip2和deflate的压缩格式，若不需要压缩，也可以设置为none；
encoding：表示该表使用utf-8的数据编码格式，在实际使用中，可以根据您的需求选择utf-8、gb2312和gbk中任一种编码格式，其中默认编码格式为utf-8。

    
     
       
       
         CREATE TABLE IF NOT EXISTS table4 (
    col_1 STRING,
    col_2 INT
)
USING csv
OPTIONS (
    path 'obs://bucketName/filePath',
    delimiter       = ',',
    quote            = '#',
    escape           = '|',
    multiline        = false,
    dateFormat       = 'yyyy-MM-dd',
    timestampFormat  = 'yyyy-MM-dd HH:mm:ss',
    mode             = 'failfast',
    header           = true,
    nullValue        = 'null',
    comment          = '*',
    compression      = 'deflate',
    encoding         = 'utf - 8'
);

        

      

    
   

常见问题

使用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语法创建OBS表。
使用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语法创建OBS表。

使用DataSource语法创建OBS表