教程:如何使用Data Lake Analytics创建分区表

  1. 云栖社区>
  2. 博客列表>
  3. 正文

教程:如何使用Data Lake Analytics创建分区表

金络 2018-08-10 11:17:21 浏览728 评论0

摘要: # 前言 [Data Lake Analytics ](https://www.aliyun.com/product/datalakeanalytics)(后文简称DLA)提供了无服务化的大数据分析服务,帮助用户通过标准的SQL语句直接对存储在OSS、TableStore上的数据进行查询分析。 在关系型数据库中,用户可以对大数据量的表进行分区,提高查询的性能。同样在DLA中,用户可以使用分

前言

Data Lake Analytics (后文简称DLA)提供了无服务化的大数据分析服务,帮助用户通过标准的SQL语句直接对存储在OSS、TableStore上的数据进行查询分析。

在关系型数据库中,用户可以对大数据量的表进行分区,提高查询的性能。同样在DLA中,用户可以使用分区表将数据进行细化,达到缩短查询响应时间的目的。

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

创建分区表

在DLA中,创建一张分区表需要在建表语句中指定 PARTITIONED BY, 例如

CREATE EXTERNAL TABLE tbl3_part 
(col1 int, col2 string)
PARTITIONED BY (p string, q string)
STORED AS TEXTFILE
LOCATION 'oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table3/';

分区表在OSS上的目录结构

DLA可以将存储在OSS上的目录或文件映射成一张表。表中的数据就是OSS中的文件内容。
对于分区表来说,分区列对应OSS上的目录,而且是有特殊命名规则的目录。

对于上面例子中的建表语句,需要有如下目录结构:

$osscmd ls oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table3
prefix list is:
object list is:
2018-08-08 14:23:17 5.68KB Standard oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table3/p=3/q=3/kv1.txt
2018-08-08 18:01:08 5.68KB Standard oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table3/p=30/q=30/kv1.txt
  1. 分区列对应表的LOCATION下的一个子目录,目录的命名规则为 分区列名=分区值
  2. 如果有多个分区列,则需要按照建表语句中指定的__分区列的顺序__依次嵌套

更新分区信息

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

MSCK REPAIR TABLE tbl3_part;

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

mysql> show partitions tbl3_part;
+-----------+
| Result    |
+-----------+
| p=3/q=3   |
| p=30/q=30 |
+-----------+

分区表查询

全表查询时,得到的是所有分区下的数据。

mysql> select count(*) from tbl3_part;
+-------+
| _col0 |
+-------+
|  1000 |
+-------+

当执行 SELECT * 时,可以发现分区列将以列的形式出现在表中定义的数据列的后面。

mysql> select * from tbl3_part limit 3;
+------+---------+------+------+
| foo  | bar     | p    | q    |
+------+---------+------+------+
|  238 | val_238 | 3    | 3    |
|   86 | val_86  | 3    | 3    |
|  311 | val_311 | 3    | 3    |
+------+---------+------+------+

查询时可以使用分区列做filter

mysql> select count(*) from tbl3_part where p='3';
+-------+
| _col0 |
+-------+
|   500 |
+-------+

注意事项

  1. OSS上分区列的目录结构的嵌套顺序需要与表中定义的顺序一致
    比如 对于本文例子中的目录结构,下面的建表语句是错误的
CREATE EXTERNAL TABLE tbl3_part 
(col1 int, col2 string)
PARTITIONED BY (q string, p string)
STORED AS TEXTFILE
LOCATION 'oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table3/';
  1. 分区表只会扫描分区列所在目录下的数据.
    对于下面的目录结构
$osscmd ls oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table4/
prefix list is:
object list is:
2018-08-08 14:23:56 5.68KB Standard oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table4/kv1.txt
2018-08-08 14:23:48 5.68KB Standard oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table4/p=4/kv2.txt
2018-08-08 14:23:40 5.68KB Standard oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table4/p=4/q=4/kv3.txt

如果建表语句中指定的分区列为 p 和 q,则该表的数据文件只有 kv3.txt.
数据文件 kv1.txt 和 kv2.txt 将不会被计算在内。

  1. 如果有新增的OSS分区目录,则需要手动执行 MSCK REPAIR TABLE table_name 命令使其生效,再进行查询。

用云栖社区APP,舒服~

【云栖快讯】诚邀你用自己的技术能力来用心回答每一个问题,通过回答传承技术知识、经验、心得,问答专家期待你加入!  详情请点击

网友评论

金络
文章4篇 | 关注2
关注
提供海量、安全和高可靠的云存储服务。RESTful API的平台无关性,容量和处理能力的弹性... 查看详情
快速、完全托管的TB/PB级数据仓库解决方案,向用户提供了完善的数据导入方案以及多种经典的分... 查看详情
云数据库PPAS版,是阿里云与EnterpriseDB公司合作基于PostgreSQL高度兼... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
阿里云总监课正式启航

阿里云总监课正式启航