创建分区表 - 云原生数据湖分析 DLA - 文档停止维护

通常在关系型数据库中为提高查询性能，会对大数据量的表进行分区。同样，在DLA中您也可以使用分区表对OSS、Tablestore中的数据进行细化处理，缩短查询响应时间。本文档以OSS数据为例，介绍如何通过DLA创建、使用和管理分区表。

分区表与OSS目录的关系

在DLA中，可以将存储在OSS中的目录或文件映射成一张分区表，表中的数据是OSS的文件内容，分区列对应OSS中的目录，该目录需遵守以下命名规则：

分区列对应表的LOCATION下的一个子目录，目录的命名规则为分区列名=分区列值。
如果有多个分区列，则需要按照建表语句中指定的分区列顺序依次嵌套。

例如，以下是OSS中的目录结构：

https://bucket-name.oss-cn-hangzhou.aliyuncs.com/dla/y=2018/m=12/kv2.txt
https://bucket-name.oss-cn-hangzhou.aliyuncs.com/dla/y=2019/m=2/kv1.txt

针对上述OSS目录结构，在DLA中创建分区表时，有两个分区列分别为y（年份）和m（月份）。

CREATE EXTERNAL TABLE dla (
    prod_name string,
    cnt bigint)
PARTITIONED BY (y string, m string)
STORED AS TEXTFILE
LOCATION 'oss://oss-bucket-name/dla/';

注意事项

在DLA中创建和使用OSS分区表时，以下注意事项需要您知晓：

OSS中分区列的目录结构嵌套顺序应与表中定义的顺序一致。

针对上述OSS目录结构，以下创建分区表的语句为错误语句。

  CREATE EXTERNAL TABLE dla (
      prod_name string,
      cnt bigint)
  PARTITIONED BY (m string, y string)
  STORED AS TEXTFILE
  LOCATION 'oss://bucket-name/dla/';

分区表只会扫描分区列所在目录下的数据。
对于以下目录结构，如果建表语句中指定的分区列为y和m，则通过分区表只能查询kv3.txt中的数据，无法查询kv4.txt中的数据。
```
  https://bucket-name.oss-cn-hangzhou.aliyuncs.com/dla/y=2019/m=3/kv3.txt
  https://bucket-name.oss-cn-hangzhou.aliyuncs.com/dla/kv4.txt
```

前提条件

在DLA中创建和使用OSS分区表时，需要通过以下操作在OSS中准备测试数据：

开通OSS服务，请参见开通OSS服务。
创建存储空间，请参见创建存储空间。
按照分区列名=分区列值规则新建目录，请参见新建目录。
上传文件数据，请参见上传文件数据。

步骤一：创建Schema

登录DLA控制台。
单击左侧导航栏的SQL访问点，然后单击登录DMS，执行以下SQL创建OSS Schema。
您也可以通过MySQL客户端或者程序代码等方式连接DLA，然后执行以下SQL创建OSS Schema。
```
 CREATE SCHEMA dla_oss_db with DBPROPERTIES(
  catalog='oss',
  location= 'oss://oss-bucket-name/dla/'
  );
```

catalog：指定创建的Schema类型为OSS。
location：文件所在的OSS Bucket目录，需以/结尾。

步骤二：创建分区表

在DLA中执行以下SQL创建为OSS文件创建分区表。

CREATE EXTERNAL TABLE dla (
    prod_name string,
    cnt bigint)
PARTITIONED BY (y string, m string)
STORED AS TEXTFILE
LOCATION 'oss://oss-bucket-name/dla/';

步骤三：使用MSCK命令更新分区信息

分区表创建成功后，需要执行MSCK REPAIR TABLE将分区信息同步到DLA中。

MSCK REPAIR TABLE dla;

注意

MSCK命令只能识别符合DLA分区列命名规则的目录，即分区列的目录名为分区列名=分区列值。
当分区表对应的OSS目录发生变化时，需重新执行MSCK命令，DLA根据OSS中当前分区值自动同步分区信息。

MSCK命令执行成功后，返回以下提示信息。

Repair: Added partition to metastore dla_oss_db.dla:y=2018/m=12
Repair: Added partition to metastore dla_oss_db.dla:y=2019/m=2

步骤四：使用SHOW PARTITIONS命令查看分区信息

MSCK执行成功后，可以通过SHOW PARTITIONS查看分区表中所有的分区信息。

show partitions dla;
+-----------+
| y=2018/m=12|
| y=2019/m=2 |

步骤五：查询分区表数据

select count(*) from dla;
+-------+
| _col0 |
+-------+
|  4    |
select * from dla;
+---------------------------+
|prod_name | cnt | y  |  m  |
+---------------------------+
|显示器    | 2   |2019 |  2  |
|硬盘      | 6   |2018 |  12 |
|键盘      | 3   |2018 |  12 |
|鼠标      | 1   |2019 |  2  |

可以使用分区列作为查询过滤条件：

select * from dla where y='2019';
+---------------------------+
|prod_name | cnt | y  |  m  |
+---------------------------+
|显示器    | 2   |2019 |  2  |
|鼠标      | 1   |2019 |  2  |

管理分区

同步分区信息
当OSS中的分区目录发生变化，例如OSS中新增分区目录时，执行MSCK命令使分区生效。
```
  MSCK REPAIR TABLE table_name;
```

添加分区

  ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];
   partition_spec:
    : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

可以一次添加多个分区，分区之间用逗号分隔。

  ALTER TABLE order_part ADD 
      PARTITION (dt='2008-08-08', status='ready') location '/path/to/ready/part080808',
      PARTITION (dt='2008-08-09', status='new') location '/path/to/new/part080809';

注意

新增分区时可以使用IF NOT EXISTS判断分区是否存在，若新增分区已存在，则新的LOCATION会覆盖原有分区所指向的目录。

删除分区
```
  ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...];
```
可以一次删除多个分区，分区之间以英文逗号（,）分隔。
```
  ALTER TABLE order_part DROP
      PARTITION (dt='2008-08-08', status='ready');
```
注意
- 目前只支持以分区列名=分区列值删除指定分区，不支持以表达式方式，例如partitionCol > 100指定分区值。
- 如果已经删除的分区目录符合分区命名规则即分区列名=分区列值，则执行MSCK命令将自动添加已经删除的分区。