全部产品
云市场

创建分区表

更新时间:2020-04-09 16:19:37

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

分区表与OSS目录的关系

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

  • 分区列对应表的LOCATION下的一个子目录,目录的命名规则为分区列名=分区列值

  • 如果有多个分区列,则需要按照建表语句中指定的分区列顺序依次嵌套。

例如,以下是OSS中的目录结构:

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

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

  1. CREATE EXTERNAL TABLE dla (
  2. prod_name string,
  3. cnt bigint)
  4. PARTITIONED BY (y string, m string)
  5. STORED AS TEXTFILE
  6. LOCATION 'oss://oss-bucket-name/dla/';

注意事项

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

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

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

    1. CREATE EXTERNAL TABLE dla (
    2. prod_name string,
    3. cnt bigint)
    4. PARTITIONED BY (m string, y string)
    5. STORED AS TEXTFILE
    6. LOCATION 'oss://bucket-name/dla/';
  • 分区表只会扫描分区列所在目录下的数据。

    对于以下目录结构,如果建表语句中指定的分区列为y和m,则通过分区表只能查询kv3.txt中的数据,无法查询kv4.txt中的数据。

    1. https://bucket-name.oss-cn-hangzhou.aliyuncs.com/dla/y=2019/m=3/kv3.txt
    2. https://bucket-name.oss-cn-hangzhou.aliyuncs.com/dla/kv4.txt

前提条件

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

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

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

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

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

步骤一:创建Schema

  1. 登录DLA控制台

  2. 单击左侧导航栏的访问点管理,然后单击登录DMS,执行以下SQL创建OSS Schema。

    您也可以通过MySQL客户端或者程序代码等方式连接DLA,然后执行以下SQL创建OSS Schema。

    1. CREATE SCHEMA dla_oss_db with DBPROPERTIES(
    2. catalog='oss',
    3. location= 'oss://oss-bucket-name/dla/'
    4. );
  • catalog:指定创建的Schema类型为OSS。

  • location:文件所在的OSS Bucket目录,需以/结尾。

步骤二:创建分区表

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

  1. CREATE EXTERNAL TABLE dla (
  2. prod_name string,
  3. cnt bigint)
  4. PARTITIONED BY (y string, m string)
  5. STORED AS TEXTFILE
  6. LOCATION 'oss://oss-bucket-name/dla/';

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

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

  1. MSCK REPAIR TABLE dla;

注意

  • MSCK命令只能识别符合DLA分区列命名规则的目录,即分区列的目录名为分区列名=分区列值

  • 当分区表对应的OSS目录发生变化时,需重新执行MSCK命令,DLA根据OSS中当前分区值自动同步分区信息。

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

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

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

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

  1. show partitions dla;
  2. +-----------+
  3. | y=2018/m=12|
  4. | y=2019/m=2 |

步骤五:查询分区表数据

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

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

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

管理分区

  • 同步分区信息

    当OSS中的分区目录发生变化,例如OSS中新增分区目录时,执行MSCK命令使分区生效。

    1. MSCK REPAIR TABLE table_name;
  • 添加分区

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

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

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

    注意

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

  • 删除分区

    1. ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...];

    可以一次删除多个分区,分区之间以英文逗号(,)分隔。

    1. ALTER TABLE order_part DROP
    2. PARTITION (dt='2008-08-08', status='ready');

    注意

    • 目前只支持以分区列名=分区列值删除指定分区,不支持以表达式方式,例如partitionCol > 100指定分区值。

    • 如果已经删除的分区目录符合分区命名规则即分区列名=分区列值,则执行MSCK命令将自动添加已经删除的分区。