# 使用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创建分区表。
   
 
#### 语法格式
```
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创建表。
 
#### 参数说明
表1参数描述 
| **参数**           | **是否必选** | **描述**                                                                                                                                                                                                                                                                                                                                                          |
|:---|:---|:---|
| db_name          | 否        | Database名称。 由字母、数字和下划线（_）组成。不能是纯数字，且不能以数字和下划线开头。                                                                                                                                                                                                                                                            |
| table_name       | 是        | Database中的表名。 由字母、数字和下划线（_）组成。不能是纯数字，且不能以数字和下划线开头。匹配规则为：\^(?!_)(?!\[0-9\]+$)\[A-Za-z0-9_$\]\*$。 特殊字符需要使用单引号（''）包围起来。 表名对大小写不敏感，即不区分大小写。 |
| col_name         | 是        | 以逗号分隔的带数据类型的列名。 列名由字母、数字和下划线（_）组成。不能是纯数字，且至少包含一个字母。 列名为大小写不敏感，即不区分大小写。                                                                                                                      |
| col_type         | 是        | 列字段的数据类型。数据类型为原生类型。 请参考[原生数据类型](https://support.huaweicloud.com/sqlref-spark-dli/dli_08_0058.html)。                                                                                                                                                                                                         |
| col_comment      | 否        | 列字段描述。仅支持字符串常量。                                                                                                                                                                                                                                                                                                                                                 |
| file_format      | 是        | DLI表数据存储格式，支持：parquet和orc格式。                                                                                                                                                                                                                                                                                                                                    |
| table_comment    | 否        | 表描述。仅支持字符串常量。                                                                                                                                                                                                                                                                                                                                                   |
| select_statement | 否        | 用于CTAS命令，将源表的select查询结果或某条数据插入到新创建的DLI表中。                                                                                                                                                                                                                                                                                                                       |
   
 表2OPTIONS参数描述 
| **参数**              | **是否必选** | **描述**                                                 | 默认值   |
|:---|:---|:---|:---|
| multiLevelDirEnable | 否        | 是否迭代查询子目录中的数据。当配置为true时，查询该表时会迭代读取该表路径中所有文件，包含子目录中的文件。 | false |
| compression         | 否        | 指定压缩格式。一般为parquet格式时指定该参数，推荐使用'zstd'压缩格式。              | -     |
   
 #### 示例1：创建DLI非分区表
示例说明：创建名为table1的DLI非分区表，使用USING关键字指定该表的存储格式为orc格式。
在您的实际使用中，还可以将DLI表存储为parquet类型。
```
CREATE TABLE IF NOT EXISTS table1 (
    col_1 STRING,
    col_2 INT)
USING orc;
```
#### 示例2：创建DLI分区表
示例说明：创建一个名为student的分区表，该分区表使用院系编号（facultyNo）和班级编号（classNo）进行分区，该student表会同时按照不同的院系编号（facultyNo）和不同的班级编号（classNo）分区。
在实际的使用过程中，您可以选择合适的分区字段并将其添加到PARTITIONED BY关键字后。
```
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表中。
  ```
  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中。
  ```
  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类型。
具体使用方法与明细可以参照"数据类型 \>原生数据类型"。
请参考[原生数据类型](https://support.huaweicloud.com/sqlref-spark-dli/dli_08_0058.html)。
```
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压缩格式。
  ```
  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表](https://support.huaweicloud.com/sqlref-spark-dli/dli_08_0204.html#dli_08_0204)。
  
- **使用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表](https://support.huaweicloud.com/sqlref-spark-dli/dli_08_0204.html#dli_08_0204)。