# 使用Hive语法创建External Table
#### 功能描述
使用Hive语法创建OBS表。DataSource语法和Hive语法主要区别在于支持的表数据存储格式范围、支持的分区数等有差异，详细请参考语法格式和注意事项说明。
*推荐使用OBS并行文件系统进行存储。并行文件系统是一种高性能文件系统，提供毫秒级别访问时延，TB/s级别带宽和百万级别的IOPS，适用于大数据交互式分析场景。*
#### 注意事项
- 创建表时会统计大小。
- 添加数据时不会修改大小。
- 如需查看表大小可以通过OBS查看。
- CTAS建表语句不能指定表的属性。
- **关于分区表的使用说明：**
  - 创建分区表时，PARTITIONED BY中指定分区列必须是不在表中的列，且需要指定数据类型。分区列支持STRING, BOOLEAN, TINYINT, SMALLINT, SHORT, INT, BIGINT, LONG, DECIMAL, FLOAT, DOUBLE, DATE, TIMESTAMP等Hive开源支持的类型。
  
  - 支持指定多个分区字段，分区字段只需在PARTITIONED BY关键字后指定，不能像普通字段一样在表名后指定，否则将出错。
  
  - 单表分区数最多允许200000个。
   
- **关于创建表时设置多字符的分隔符：**
  - 只有指定ROW FORMAT SERDE为org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe时，字段分隔符才支持设置为多字符。
  
  - 只有Hive OBS表支持在建表时指定多字符的分隔符，Hive管理表不支持在建表时指定多字符的分隔符。
  
  - 指定了多字符分隔的表不支持INSERT、IMPORT等写数语句。如需添加数据，请将数据文件直接放到表对应的OBS路径下即可。
   
- **External Table与Managed Table建表差异：**
  
  | **差异项**         | **External Table** | **Managed Table**  |
  |:---|:---|:---|
  | **LOCATION**    | 必填，指定OBS存储路径       | 不指定                |
  | **数据删除**        | DROP TABLE仅删除元数据   | DROP TABLE删除元数据和数据 |
  | **EXTERNAL关键字** | 不使用（与开源不同）         | 不使用                |
  | **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], ...)]
  [COMMENT table_comment] 
  [PARTITIONED BY (col_name2 col_type2 [COMMENT col_comment2], ...)] 
  [ROW FORMAT row_format]
  [STORED AS file_format] 
  LOCATION 'obs_path'
  [TBLPROPERTIES (key1=val1, key2=val2, ...)]
  [AS select_statement]
row_format:
  : SERDE serde_cls [WITH SERDEPROPERTIES (key1=val1, key2=val2, ...)]
  | DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]]
      [COLLECTION ITEMS TERMINATED BY char]
      [MAP KEYS TERMINATED BY char]
      [LINES TERMINATED BY char]
      [NULL DEFINED AS char]
```
#### 关键字
- **IF NOT EXISTS**：指定该关键字以避免表已经存在时报错。
- **COMMENT**：字段或表描述。
- **PARTITIONED BY**：指定分区字段。
- **ROW FORMAT**：行数据格式。
- **STORED AS**：指定所存储的文件格式，当前该关键字只支持指定TEXTFILE、AVRO、ORC、SEQUENCEFILE、RCFILE、PARQUET格式。
- **LOCATION**：指定OBS的路径。创建OBS表时必须指定此关键字。
- **TBLPROPERTIES**：允许用户给表添加key/value的属性。
- **AS**：使用CTAS创建表。
 
#### 参数说明
表1基本参数 
| **参数**               | **是否必选** | **描述**                                                                                                                                        |
|:---|:---|:---|
| **db_name**          | 否        | Database名称。由字母、数字和下划线（_）组成。不能是纯数字，且不能以数字和下划线开头。                                                                                               |
| **table_name**       | 是        | Database中的表名。由字母、数字和下划线（_）组成。不能是纯数字，且不能以数字和下划线开头。匹配规则为：\^(?!_)(?!\[0-9\]+$)\[A-Za-z0-9_$\]\*$。特殊字符需要使用单引号（''）包围起来。表名对大小写不敏感，即不区分大小写。          |
| **col_name**         | 是        | 列字段名称。列字段由字母、数字和下划线（_）组成。不能是纯数字，且至少包含一个字母。列名为大小写不敏感，即不区分大小写。                                                                                  |
| **col_type**         | 是        | 列字段的数据类型。数据类型为原生类型。请参考原生数据类型。                                                                                                                 |
| **col_comment**      | 否        | 列字段描述。仅支持字符串常量。                                                                                                                               |
| **row_format**       | 否        | 行数据格式。ROW FORMAT功能只支持TEXTFILE类型的表。                                                                                                            |
| **file_format**      | 是        | OBS表存储格式，支持TEXTFILE、AVRO、ORC、SEQUENCEFILE、RCFILE、PARQUET。                                                                                     |
| **table_comment**    | 否        | 表描述。仅支持字符串常量。                                                                                                                                 |
| **obs_path**         | 是        | 数据文件所在的OBS存储路径，推荐使用OBS并行文件系统存储。格式：obs://bucketName/tblPath，bucketName即桶名称，tblPath是目录名称。目录后不需要指定文件名。当OBS的目录下文件夹与文件同名时，创建OBS表指向的路径会优先指向文件而非文件夹。 |
| **key = value**      | 否        | 设置TBLPROPERTIES具体属性和值。                                                                                                                        |
| **select_statement** | 否        | 用于CTAS命令，将源表的SELECT查询结果或某条数据插入到新创建的OBS表中。                                                                                                     |
   
表2TBLPROPERTIES主要参数 
| **参数**           | **是否必选** | **描述**                                               |
|:---|:---|:---|
| **comment**      | 否        | 表描述信息。                                               |
| **orc.compress** | 否        | ORC存储格式表的一个属性，用来指定ORC存储的压缩方式。支持取值为：ZLIB、SNAPPY、NONE。 |
| **auto.purge**   | 否        | 当设置为true时，删除或者覆盖的数据会不经过回收站，直接被删除。                    |
   
#### 示例1：创建基本Hive格式OBS非分区表
创建名为table1的OBS非分区表，使用STORED AS关键字指定存储格式为ORC。
```
CREATE TABLE IF NOT EXISTS table1 (
    col_1   STRING,
    col_2   INT
)
STORED AS orc
LOCATION 'obs://bucketName/filePath';
```
#### 示例2：创建STORED AS TEXTFILE的OBS表
创建名为table_text的TEXTFILE格式OBS表。
```
CREATE TABLE IF NOT EXISTS table_text (
    id      INT,
    name    STRING
)
STORED AS textfile
LOCATION 'obs://bucketName/textFilePath';
```
#### 示例3：创建带ROW FORMAT的OBS表
创建名为table4的TEXTFILE类型OBS非分区表，并设置ROW FORMAT行格式。
```
CREATE TABLE IF NOT EXISTS table4 (
    col_1   STRING,
    col_2   INT
)
STORED AS textfile
LOCATION 'obs://bucketName/filePath'
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '/'
    COLLECTION ITEMS TERMINATED BY '$'
    MAP KEYS TERMINATED BY '#'
    LINES TERMINATED BY '\n'
    NULL DEFINED AS 'null';
```
#### 示例4：创建带SERDE的OBS表
创建名为table5的OBS表，使用自定义SERDE并设置多字符分隔符。
```
CREATE TABLE IF NOT EXISTS table5 (
    col_1   STRING,
    col_2   INT
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES (
    'field.delim' = '/#'
)
STORED AS textfile
LOCATION 'obs://bucketName/filePath';
```