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

简介: # 前言 [Data Lake Analytics ](https://www.aliyun.com/product/datalakeanalytics)(后文简称DLA)提供了无服务化的大数据分析服务,帮助用户通过标准的SQL语句直接对存储在OSS、TableStore上的数据进行查询分析。

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

前言

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

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

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

创建分区表

在DLA中,创建一张分区表需要在建表语句中指定 PARTITIONED BY, 例如
创建一张名为tbl3_part的分区表,该表有两个分区列,分别为p和q。

CREATE EXTERNAL TABLE tbl3_part 
(foo int, bar 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上的目录,而且是有特殊命名规则的目录:

  1. 分区列对应表的LOCATION下的一个子目录,目录的命名规则为 分区列名=分区值
  2. 如果有多个分区列,则需要按照建表语句中指定的__分区列的顺序__依次嵌套

对于上面例子中的建表语句,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

使用MSCK命令更新分区信息

建表成功后,需要执行 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 |
+-----------+

MSCK只能识别符合DLA分区列命名规则的目录,即分区列的目录名为 分区列名=分区列值。
因此,当OSS上的分区目录发上变化时,执行MSCK命令,DLA可以根据OSS中当前分区值信息自动同步。

使用ALTER命令添加/删除分区

对于已经存在的但是不满足DLA分区列命名规则的目录,用户可以通过 ALTER命令更新表的分区信息。

添加分区

语法:

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';

对于上面的语句,

  1. 如果新增的分区已经存在,则执行失败,报错 "Partition already exists";
  2. 如果使用了 [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');

对于上面的语句,

  1. 如果删除的分区不存在,执行不会报错;
  2. 如果使用了 [IF EXISTS], 当删除分区不存在时,执行不会报错;
  3. DROP 分区目前只支持 "分区列=分区值" 的指定方式。不支持分区值是一个表达式,比如 partitionCol > 100;
  4. 如果删除的分区目录名符合 分区列名=分区列值 的命名规则,执行MSCK命令仍会将已经删除的分区自动添加。

分区表查询

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

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 命令或者ALTER ADD PARTITION命令使其生效,再进行查询。
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
11月前
|
JavaScript 前端开发 数据挖掘
Echarts数据分析系统Data Analysis Platform使用说明文档
Echarts数据分析系统Data Analysis Platform使用说明文档
78 0
|
存储 NoSQL 关系型数据库
【Cassandra从入门到放弃系列 二】Column-based存储模式
【Cassandra从入门到放弃系列 二】Column-based存储模式
176 0
|
存储 SQL JSON
【数据湖】在 Azure Data Lake Storage gen2 上构建数据湖
【数据湖】在 Azure Data Lake Storage gen2 上构建数据湖
|
分布式计算 资源调度 Java
HBase&MR 集成官方案例 | 学习笔记
快速学习 HBase&MR 集成官方案例。
212 0
|
SQL 分布式计算 对象存储
Data Lake Analytics: 自动推断 OSS 上的 CSV 文件
截止目前,Data Lake Analytics(下面简称 DLA)已经和 OSS 有很多深度的集成,详情可以参见 DLA 的官方文档:DLA 和 OSS 整合。 为了能够提供更好的使用体验,目前可以通过 DLA 自动探测 OSS 上的多种文件格式,并自动生成建表语句,大大减少建表的使用成本。
Data Lake Analytics: 自动推断 OSS 上的 CSV 文件
|
存储 SQL 分布式计算
从 Neo4j 导入 Nebula Graph 实践见 SPark 数据导入原理
本文主要讲述如何使用基于 Spark 的数据导入工具 Nebula Graph Exchange 将数据从 Neo4j 导入到 Nebula Graph Database。
689 0
|
SQL NoSQL 关系型数据库
教程:使用Data Lake Analytics读/写MongoDB数据
Data Lake Analytics 作为云上数据处理的枢纽,最近加入了对于MongoDB 的支持, 这篇教程带你玩转 DLA 的 MongoDB 支持。 创建数据库 在 DLA 里面创建一个底层映射到 MongoDB 的外表的语法如下: CREATE DATABASE `mongo_test`.
2844 0
教程:使用Data Lake Analytics读/写MongoDB数据
|
分布式计算 Spark SQL
【译】Delta Lake 0.4.0 新特性演示:使用 Python API 就地转换与处理 Delta Lake 表
本文以案例演示在最新的 Delta Lake 0.4.0 中,如何转换 Delta Lake 表,使用全新的 Python API 执行 upsert 与删除数据,用时间旅行 (time travel) 查询数据的旧版本,以及 vacuum 语句清理旧版本。
【译】Delta Lake 0.4.0 新特性演示:使用 Python API 就地转换与处理 Delta Lake 表
|
SQL NoSQL 关系型数据库
如何在阿里云上使用Data Lake Analytics分析Table Store数据
数据湖(Data Lake)是时下大数据行业热门的概念:https://en.wikipedia.org/wiki/Data_lake。基于数据湖做分析,可以不用做任何ETL、数据搬迁等前置过程,实现跨各种异构数据源进行大数据关联分析,从而极大的节省成本和提升用户体验。
5850 0

热门文章

最新文章