# 使用DataSource语法创建External Table
#### 功能描述
使用DataSource语法创建external表。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个。
   
- External Table与Managed Table建表差异：
  
  | **差异项**               | **External Table (external表)** | **Managed Table (管理表)** |
  |:---|:---|:---|
  | **LOCATION**          | 必填，指定OBS存储路径                   | 不指定                     |
  | **数据删除**              | DROP TABLE仅删除元数据               | DROP TABLE删除元数据和数据      |
  | **EXTERNAL关键字**       | 不使用（与开源不同）                     | 不使用                     |
  | **USING / STORED AS** | 必须指定                           | 必须指定                    |
     
  
与开源Spark的差异：开源Spark使用CREATE EXTERNAL TABLE ... LOCATION创建外部表，平台不使用EXTERNAL关键字，而是通过指定LOCATION路径来区分外部表和管理表。
#### 语法格式
```
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**      | 是        | 用于创建表的输入格式。支持ORC、PARQUET、JSON、CSV、AVRO类型。                                                                                                     |
| **path**             | 是        | 数据文件所在的OBS存储路径，推荐使用OBS并行文件系统存储。格式：obs://bucketName/tblPath，bucketName即桶名称，tblPath是目录名称。目录后不需要指定文件名。当OBS的目录下文件夹与文件同名时，创建OBS表指向的路径会优先指向文件而非文件夹。 |
| **table_comment**    | 否        | 表描述信息。仅支持字符串常量。                                                                                                                               |
| **select_statement** | 否        | 用于CTAS命令，将源表的SELECT查询结果或某条数据插入到新创建的OBS表中。                                                                                                     |
   
表2OPTIONS参数 
| **参数**                  | **是否必选** | **描述**                                                          | **默认值** |
|:---|:---|:---|:---|
| **path**                | 否        | 指定的表路径，即OBS存储路径。                                                | -       |
| **multiLevelDirEnable** | 否        | 嵌套子目录场景下，是否迭代查询子目录中的数据。当配置为true时，查询该表时会迭代读取该表路径中所有文件，包含子目录中的文件。 | false   |
| **dataDelegated**       | 否        | 是否需要在删除表或分区时，清除path路径下的数据。                                      | false   |
| **compression**         | 否        | 指定压缩格式。一般为parquet格式时指定该参数，推荐使用zstd压缩格式。                         | -       |
   
表3CSV数据格式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：宽容模式，遇到错误的字段时设置该字段为NULL；DROPMALFORMED：遇到错误的字段时丢弃整行；FAILFAST：报错模式，遇到错误的字段时直接报错。 | PERMISSIVE          |
| **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               |
   
#### 示例1：创建基本OBS非分区表
创建名为table1的OBS非分区表，使用USING关键字指定存储格式为ORC格式。
```
CREATE TABLE IF NOT EXISTS table1 (
    col_1   STRING,
    col_2   INT
)
USING orc
OPTIONS (path 'obs://bucketName/filePath');
```
#### 示例2：创建OBS分区表
创建名为student的分区表，使用院系编号（facultyNo）和班级编号（classNo）进行分区。
```
CREATE TABLE IF NOT EXISTS student (
    Name        STRING,
    facultyNo   INT,
    classNo     INT
)
USING csv
OPTIONS (path 'obs://bucketName/filePath')
PARTITIONED BY (facultyNo, classNo);
```
#### 示例3：创建带注释和属性的OBS分区表
创建名为table3的OBS分区表，附带列注释、表注释以及OPTIONS属性配置。
```
CREATE TABLE IF NOT EXISTS table3 (
    col_1   STRING COMMENT '用户名称',
    col_2   INT COMMENT '用户编号'
)
USING parquet
OPTIONS (
    path 'obs://bucketName/filePath',
    multiLevelDirEnable = true,
    dataDelegated = true,
    compression = 'zstd'
)
PARTITIONED BY (col_2)
COMMENT '用户信息表';
```
#### 示例4：使用不同数据格式创建OBS表
创建使用JSON、AVRO等不同数据格式的OBS表。
```
-- 使用JSON格式
CREATE TABLE IF NOT EXISTS table_json (
    id      INT,
    name    STRING
)
USING json
OPTIONS (path 'obs://bucketName/jsonPath');
-- 使用AVRO格式
CREATE TABLE IF NOT EXISTS table_avro (
    id      INT,
    data    STRING
)
USING avro
OPTIONS (path 'obs://bucketName/avroPath');
```