如何在Amazon Athena中最高效对比日期分区组件实现表连接

核心优先级说明 #

首先明确：分区裁剪（减少扫描数据量）的优先级远高于关联条件的计算开销，千万级数据下第一步永远是先缩小扫描范围，再优化关联逻辑。 #

三种方案性能对比 #

• 直接排除：拼接转日期后对比
  该方案需要先拼接3个分区字段、再做日期格式转换，计算开销最大，且完全无法触发分区裁剪，会全量扫描两张表，性能最差。
• 不推荐：字符串补/去前导零对比
  给不带前导零的tableB字段做lpad(b.partition_1,2,'0')、lpad(b.partition_2,2,'0')后和tableA对比，虽然能实现关联，但字符串操作的开销高于数值对比，且容易因为长度写错出现关联错误。
• 最优选择：分区字段转int后对比
  也就是你提到的写法，性能最高，原因有二：
  1. 整数等值对比的效率远高于字符串操作，Presto对数值运算的优化更成熟，千万级数据下计算开销更低
  2. 写法简洁容错率高，不会出现前导零相关的匹配错误

额外优化建议 #

1. 强制加分区过滤条件
   不要全量关联两张表，在WHERE子句中分别给两张表加上分区范围限制，先把扫描的数据量降下来，这是性能提升最明显的手段，参考示例：

   SELECT a.accnt_num, a.value1, b.value2 
   FROM tableA a 
   JOIN tableB b
   ON 
     a.accnt_num = b.accnt_num
     AND CAST(a.partition_0 AS INT) = CAST(b.partition_0 AS INT)
     AND CAST(a.partition_1 AS INT) = CAST(b.partition_1 AS INT)
     AND CAST(a.partition_2 AS INT) = CAST(b.partition_2 AS INT)
   -- 加分区过滤触发裁剪，大幅减少扫描数据量
   WHERE 
     a.partition_0 = '2021' 
     AND a.partition_1 BETWEEN '01' AND '03'
     AND b.partition_0 = '2021'
     AND b.partition_1 BETWEEN '1' AND '3'
   

2. 避免写SELECT *，只查询需要的字段，减少数据shuffle和传输开销
3. 如果其中一张表数据量明显更小，可以加广播Join提示，把小表分发到所有计算节点，减少shuffle开销，参考示例：

   SELECT /*+ BROADCAST(b) */ a.accnt_num, a.value1, b.value2 
   FROM tableA a 
   JOIN tableB b
   ON ...
   

内容的提问来源于stack exchange，提问作者Runeaway3