最近在实习期间，使用到了亚马逊AWS云服务提供的Athena, S3。 为此写篇通俗易懂的技术文章介绍一下 什么是 Athena, S3, 推广到 外部表的介绍, presto 分布式SQL引擎下的 的查询优化。
不出意外的话，这应该是掘金上的第一篇 介绍如何 在Athena 上做查询优化的文章了。我会尽量将得通俗易懂， 即使你没用过 athena , S3 ,但只要稍微用过 MySQL，用过百度网盘， 也可以 明白如何优化数据库, 明白如何分区,分桶 存储数据, 优化查询, 和面试官谈笑风生hhh
什么是Amazon Athena 和 S3?
按照亚马逊官网的说法，Amazon Athena 是一种交互式查询服务，让您能够轻松使用标准 SQL 分析 Amazon S3 中的数据。Athena 没有服务器，因此您无需管理任何基础设施，且只需为您运行的查询付费。
用大白话来说， Athena 是一种网页查询数据库的客户端，类似于写python用的 jupyter notebook, 不需要部署查询数据库端的服务器, 直接登录网页编写 SQL 语句即可使用。另外Athena 提供了多种语言(node.js, go等)的客户端，在代码里可以直接运行，同样也无需考虑服务器部署。
Athena 查询页面大概长这样(数据已做脱敏处理), 左边为数据库信息，中间直接编写SQL 语句，点击运行直接输出结果。
另外Athena 使用按照查询收费的计费方式，只需要为扫描到的数据付费。除了S3的存储费和查询扫描到的数据付费，无需额外支付存储费用。
说完Athena， 你可能会好奇，Athena的数据哪里来的？ Athena 建表通常为外部表，表的数据存储在S3中。所谓外部表，就是表与数据存储的地方(S3)分离，表只是对已有的数据进行管理，删除表并不删除数据。这与传统的MYSQL不同，MYSQL 表插入数据 使用 insert 语句，如果 drop 掉表，表里面的数据也就被删除了。
S3 可以理解为一个分布式的文件存储系统, 或者更简单点，就是一个文件夹，只不过你看到的文件夹不一定保存在同一个台机器，可能保存在很多台机器上。 S3 大概长这样(数据已做脱敏处理):
S3文件存储系统的根目录叫做 bucket, 中文存储桶, 桶下面是一个个文件夹。S3中的一个文件叫做一个 object, 中文叫一个对象。S3支持访问权限，服务器地区，监控统计等功能。
在 Athena 中创建外部表
看到这里，你可能会问怎么将Athena 的表与 S3关联起来呢？ 答案非常简单，直接在 Athena 中 写SQL 语句即可，这里的SQL语句和一般的Mysql 略有不同，其背后是 presto 引擎，创建表的时候同时指定S3的路径。比如这样:
CREATE EXTERNAL TABLE `table_X`(
`time_stamp` string,
`column_1` string )
PARTITIONED BY (
`yr` string,
`mt` string,
`dt` string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://aaa/bbb/ccc/'
TBLPROPERTIES (
'create_time'='20220714')
我们创建了一个名叫 table_X 的表格, 里面有两个字段， times_tamp 和 column_2, 表格里的数据存储在S3中，路径是 s3://aaa/bbb/ccc/, aaa 也就是 bucket。此外我们还按照yr,mt,dt 进行分区。
所谓分区, 也就是根据 yr, mt, dt 在 s3://aaa/bbb/ccc 下创建子路径。在开发过程中，很多数据是有时效性的，需要记录产生数据的时间，比如年月日。比如今天是 2022年1月1日那么插入的数据就会被放在 s3://aaa/bbb/ccc/2022/01/01 路径下。
然后S3 中就会自动创建好 aaa/bbb/ccc的路径。聪明的你可能会马上进入S3下的 aaa/bbb/ccc 路径查看一下，你会发现里面什么都没有。那肯定啦，这个路径刚被创建好，里面当然为空了。那么如何往S3中插入数据呢？
这里我用三个例子说明如何向S3中传入文件:
第一种，最简单，直接在S3文件夹中点击"上传" 手动上传。
第二种，通过S3 代码客户端上传,比如 node.js 客户端, S3 官方教程中写到:
// Import required AWS SDK clients and commands for Node.js.
import { PutObjectCommand } from "@aws-sdk/client-s3";
import { s3Client } from "./libs/s3Client.js";
// Helper function that creates an Amazon S3 service client module.
import {path} from "path";
import {fs} from "fs";
const file = "OBJECT_PATH_AND_NAME";
// Path to and name of object. For example '../myFiles/index.js'.
const fileStream = fs.createReadStream(file);
// Set the parameters export const uploadParams = {
Bucket: "BUCKET_NAME",
// Add the required 'Key' parameter using the 'path' module.
Key: path.basename(file),
// Add the required 'Body' parameter
Body: fileStream, };
// Upload file to specified bucket.
export const run = async () => {
try {
const data = await s3Client.send(new PutObjectCommand(uploadParams));
console.log("Success", data);
return data; // For unit tests.
} catch (err) {
console.log("Error", err);
} };
run();\
s3 客户端 send 方法内的 PutObjectCommand对象 主要有3个参数, bucket 名，文件路径，以及读取文件的文件流。
第三种，直接从 Athena 外部表表中迁移数据，本质上是 从S3迁移数据到S3，可以直接在Athena 中写对应的SQL语句，或者在代码中用 athena 客户端执行SQL语句。
create table if not exists "temp_table"
WITH (format = 'TEXTFILE', bucket_count = 10, bucketed_by = ARRAY ['time_stamp'], partitioned_by = ARRAY ['{yr}','{mt}'.'{dt}'],
external_location = 's3://aaa/bbb/ccc/') as
SELECT
times_tamp,column_1
FROM table_Y ;
DROP TABLE if exists `temp_table`;
这句SQL意思是从另外一个表 table_Y 选出 timestamp 和 column_1 两个字段的所有数据, 然后根据timestamp 分桶, 根据传入的 yr,mt,dt 分区, 存储在 s3://aaa/bbb/ccc/yr/mt/dt 下，一共均分成10等分。
你可能会好奇，为什么这里创建了以后 temp_table 表 又把它删掉了。 其实这个表只是一个数据的模板，让存入S3 路径下的数据按照这个表 的格式来。
为外部表添加分区
当你往外部表指向的 S3 路径增加文件了以后， 你很自然地会去 Athena 中查询那张 table_X 表，比如这样:
SELECT * FROM table_X limit 10;
但是意外地是这张表里面什么都没有， 这是为什么呢？明明S3路径下就已经存在东西了。
这就得再次强调一下什么是外部表。外部表表里面的数据和实际的数据存储是分离的，想要往外部表里面加数据得增加分区。
还记得 table_X 这张表是按照 yr, mt, dt 分区的吧， 对应的 s3 路径也是 s://aaa/bbb/ccc/yr/mt/dt 的，当往 S3 路径下放入文件时，table_X 表并不会主动去扫描路径下的数据，而是要为这个表添加分区，添加分区以后， 表才会去S3路径下扫描数据，讲数据加入表中。
添加分区的 SQL 语句如下:
ALTER table_X ADD PARTITION (yr='2022', mt='07', dt='15' )
LOCATION 's3://aaa/bbb/ccc/2022/07/15'
这样 table_X 表中就记录了 s3://aaa/bbb/ccc/2022/07/15 下的全部数据
介绍了这么多 Athena 和 S3 ， 下面终于到了主菜时间, 如何在 Athena 查询中进行优化。
1 数据分区
实际开发场景中，许多数据的产生时间是有意义的，比如计算会员到期时间，自动扣款时间，订单时间。那么按照时间分区是最多最有用的。
再来看一遍上面的第一条 SQL 语句:
CREATE EXTERNAL TABLE `table_X`(
`time_stamp` string,
`column_1` string )
PARTITIONED BY (
`yr` string,
`mt` string,
`dt` string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://aaa/bbb/ccc/'
TBLPROPERTIES (
'create_time'='20220714')
PARTITIONED BY 指明了s3存储路径会按照 yr, mt, dt 分区。简单来说数据会被放到 s3://aaa/bbb/ccc/yr/mt/dt 路径下。不仅如此,分区的 yr, mt,dt 也会成为 表 table_X的3个字段。由于按照 yr,mt,dt 分区，所以往 table_X插入数据时必须也要指明 yr,mt,dt的值。
按照分区进行查询能够大幅度缩短查询数据量 和查询时间。比如这条 SQL:
SELECT * FROM xxx.table_X where concat(yr,mt, dt)='20220101' and column_1=xxx;
这条sql会直接进入 s3://aaa/bbb/ccc/2022/01/01 路径下进行查询，缩小了查询范围。
不同的需求场景可能会有不同的分区策略，按照年月日分期是最常见的, 还有按照地区分区, 比如大陆的用户数据和香港的用户数据存储在不同的路径下。
2. 分桶存储
分桶和分区类似，都是将数据进行分块，每次查询时，尽可能扫描少的数据，使得查询速度变快。 比如上面的SQL语句：
create table if not exists "temp_table"
WITH (format = 'TEXTFILE', bucket_count = 10, bucketed_by = ARRAY ['time_stamp'], partitioned_by = ARRAY ['{yr}','{mt}'.'{dt}'],
external_location = 's3://aaa/bbb/ccc/') as
SELECT
times_tamp,column_1
FROM table_Y ;
DROP TABLE if exists `temp_table`;
这条SQL会将 table_Y 中的数据写入 s3://aaa/bbb/ccc/yr/mt/dt 中， 同时按照 time_stamp 这一列进行分桶存储。所谓分桶，就是将数据均等分。比如这里按照 time_stamp 进行 hash, 然后平均存储到10个文件里面去存储。 在查询分桶列的数据时只扫描关注的桶，这样可以大大减少要读取的数据行数。
你可能会好奇，分区和分桶有什么区别吗？
从实现的效果来说是没有区别的，但是分区分桶的规则是有区别的。分区用于存储基数较小的列，比如年月日，可能月份只有12种可能，日期只有31种。 比如服务器的地区，一般公司也就几个十几个地区部署服务器。而分桶针对的是那些基数较大的列，不方便直接进行分类，比如 time_stamp 这一列 就是时间戳。可以想象，基数越大，hash 出来的每种可能性就会越均匀，每个文件的大小就会更一致。
3. GROUP BY 查询优化
在使用 Athena 使用 group by 时, 将 基数较大的列(也就是可能值较多的列)放在前面，查询会更快。比如下面的第一条SQL 会比第二条SQL在 Athena中执行速度越快：
SELECT class, gender, count(*) FROM table_Student GROUP BY class, gender;
SELECT gender, class count(*) FROM table_Student GROUP BY gender,class;
你可能会好奇，为什么第一条SQL会更快呢？ 说到底，就是因为第一条SQL 提高了并行处理的能力！！！也就是说第一条SQL 能够调用更多的机器同时进行数据的访问。为了方便理解，我画了下面这副图：
假设有100个班级，每个班级只有两种性别，男和女。上面图的左边 先按照班级统计，再按照性别统计人数。那么假设扫描一遍 class 这一个字段用了一台机器，扫描gender这个字段时每一个班级里面的男女生数量在班级和班级之间彼此独立的，那么每一个班级里面都可以用独立的一台机器去扫描数据，统计每一个班级内部的男女生人数。如果有100个班级，就可以用100台机器去扫描数据，大大地提高了并行性。 而上面图的右边，扫描第一例 gender 用一台机器，扫描 class这个字段是每一个班级的人数在性比和性别彼此独立的，那么每一个性别内都可以用独立的一台机器去扫描。但是性别最多只有两种可能性，所以只能用两台机器去扫描。同样是扫描 class 和 gender 两列数据并做统计，上图左边的可以同时调用100台机器，而右边只能同时调用两台机器去扫描。
Athena 查询表背后的引擎是 presto， 是一种分布式的SQL引擎,其内部有一个协调器节点，负责创建多个线程同时工作。上面我为了简化，直接说成多台机器同时工作。上图中的例子并不绝对，并不是一定是100台和两台机器去扫描 gender的区别，只是一个示例， 其背后的思想是 尽可能地提高访问的并行性，独立性，尽可能地同时调用多台机器去扫描数据。
4. JOIN / WHERE 查询 优化
当使用 Athena 做 join 查询时，应该将较大的表放在 join 的左边，较小的表放在 join 的右边,如果有需要可以加上 WHERE 条件。比如有两张表 table_A 和 table_B, table_A的数据量远远大于 table_B 的数据量。那么下面第一条 SQL 的执行速度将会远远大于 第二条 SQL的数量:
SELECT count(*) FROM Table_A JOIN Table_B ON Table_A.date = Table_B.date WHERE Table_B.column_X = "value";
SELECT count(*) FROM Table_B JOIN Table_A ON Table_B.date = Table_A.date WHERE Table_A.column_X = "value";
为了理解，我画了一副图：
Join的原理其实就是两重循环，将 table_A的每一行 拿去和 table_B 的每一行进行匹配，匹配的规则是 colum_X 相等且等于 value_X。上图的左手边对应上面的第一条SQL，prestol SQL 的执行逻辑是先遍历 table_B 的 column_X 筛选出来，再用 table_A的每一行去和从table_B筛选出来的行做匹配。由于 table_B 数据量比较小，所以筛选 table_B 的时间会更快。除此之外，当table_A的 每一行去和 table_B的过程中是彼此独立的，可以同时开多台机器去并行地匹配。比如 table_A 数据量较大，最多可以同时开100台机器去和table_B 进行匹配，实际的查询时间可以近似于只有一台机器去匹配 table_B的时间，而table_B的数据量很小，查询时间就很快。而上图右边对应上面的第二条SQL，首先筛选出 tableA 的 colum_X 就更加费时间。 当 table_B 去和 table_A 匹配时, table_B 数据量很小，最多同时开3台机器去和 table_A 进行匹配，实际的查询时间可以近似于只有一台机器去匹配 table_A的时间，而table_A的数据量很大，所以导致查询时间就很慢。
总结一下
Athena 查询，建表过程中的优化策略：
- 对于 基数较小 的列可以进行分区
- 对于 基数较大 的列进行分桶
- GROUP BY 基数较大的列放前面
- JOIN/WHERE 基数较大的列放前面
简单来说，只要理解什么是外部表, 想办法 通过提高并行性提高访问速度 就可以理解 上面四个优化策略了。这四个优化策略是 Athena 中的外部表和 一般 MySQL 中区别较大的优化策略。当然还有别的优化策略，比如 ORDER BY 跟随 LIMIT，只选择想要的列 等等，这些比较简单，和 MYSQL 里面基本相同，就不单独列出来了。
参考资料
