全部产品

创建分区表

更新时间:2020-12-01 15:43:03

通常在关系型数据库中为提高查询性能,会对大数据量的表进行分区。同样,在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中准备测试数据:

  1. 开通OSS服务,请参见开通OSS服务

  2. 创建存储空间,请参见创建存储空间

  3. 按照分区列名=分区列值规则新建目录,请参见新建目录

  4. 上传文件数据,请参见上传文件数据

步骤一:创建Schema

  1. 登录DLA控制台

  2. 单击左侧导航栏的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命令将自动添加已经删除的分区。