创建分区表
通常在关系型数据库中为提高查询性能,会对大数据量的表进行分区。同样,在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中准备测试数据:
步骤一:创建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命令将自动添加已经删除的分区。