使用聚合函数时减少Athena扫描的数据量

Question

下面的查询扫描100 mb的数据。

select * from table where column1 = 'val' and partition_id = '20190309';

但是，以下查询扫描了15 GB的数据（分区超过90个）

select * from table where column1 = 'val' and partition_id in (select max(partition_id) from table);

如何优化第二个查询以扫描与第一个查询相同数量的数据？

Answer 1

这里有两个问题。上面标select max(partition_id) from table量子查询的效率以及一个@PiotrFindeisen指出了动态过滤。

第一个问题是，对Hive表的分区键的查询要比看起来复杂得多。大多数人会认为，如果您想要分区键的最大值，则可以简单地对分区键执行查询，但这是行不通的，因为Hive允许分区为空（并且还允许非空文件不包含任何行）。具体来说，上述标量子查询select max(partition_id) from table要求Trino（以前称为PrestoSQL）查找包含至少一行的最大分区。理想的解决方案是在Hive中具有完美的统计信息，但是引擎还需要具有用于Hive的自定义逻辑，以打开分区的文件，直到找到一个非空的分区。

如果您确定仓库中不包含空分区（或者您可以接受其中的含义），则可以在隐藏$partitions表上用一个替换标量子查询。”

select * 
from table 
where column1 = 'val' and 
    partition_id = (select max(partition_id) from "table$partitions");

第二个问题是@PiotrFindeisen指出的问题，它与查询计划的执行方式有关。大多数人会看上面的查询，发现引擎显然应该select max(partition_id) from "table$partitions"在计划过程中找出值，将其内联到计划中，然后继续进行优化。不幸的是，这通常是一个相当复杂的决定，因此引擎将其简单地建模为广播联接，其中执行的一部分找出了该值，并将该值广播给其他工人。问题是执行的其余部分无法将此新信息添加到现有处理中，因此它仅扫描所有数据，然后滤除您要跳过的值。有一个正在进行的项目正在添加此
动态过滤
，但尚未完成。

这意味着您今天可以做的最好的事情是运行两个单独的查询：一个查询获得最大partition_id，第二个查询具有内联值。

顺便说一句，Presto
0.199中添加了隐藏的“ $
partitions”表，我们修复了0.201中的一些小错误。我不确定Athena所基于的版本，但我认为它已经过时了（我编写此答案时的当前版本是309。