全部产品
云市场

通过DLA创建OSS分区表

更新时间:2019-04-18 16:10:38

背景信息

在关系型数据库中,为提高查询的性能,可以对大数据量的表进行分区。Data Lake Analytics(DLA)作为无服务化的大数据分析服务,通过标准的SQL语句直接对存储在阿里云对象存储服务(Object Storage Service,简称 OSS)、表格存储(Table Store)中的数据进行查询分析。并且在DLA中,您也可以使用分区表对OSS、Table Store中的数据进行细化处理,缩短查询响应时间。

本文档将以OSS数据源为例,详细介绍如何通过DLA创建和使用OSS分区表。

分区表在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中创建指向OSS文件的外表dla。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://bucket-name/dla/';

注意事项

  • 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/';
  • 分区表只会扫描分区列所在目录下的数据。

    对于下面的目录结构:

    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

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

前提条件

通过DLA创建和使用OSS分区表之前,您需要通过以下操作在OSS中准备好测试数据。

  1. 开通OSS服务

  2. 创建存储空间

  3. 按照分区列名=分区列值规则,创建文件存放目录。

  4. 上传文件数据

实施步骤

步骤一:在DLA中创建OSS连接

登录DLA控制台登录DMS,在DLA中创建一个到OSS的连接。语法如下:

  1. CREATE SCHEMA dla_oss_db with DBPROPERTIES(
  2. catalog='oss',
  3. location= 'oss://bucket-name/dla/'
  4. );

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

步骤二:在DLA中创建指向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://bucket-name/dla/';

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

外表创建成功后,执行MSCK REPAIR TABLE将分区信息同步到DLA中。MSCK命令只能识别符合DLA分区列命名规则的目录,即分区列的目录名为分区列名=分区列值

  1. MSCK REPAIR TABLE 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命令将自动添加已经删除的分区。