Hive子查询/Join做分区过滤触发全表扫描导致Query1比Query2慢咨询

两者的核心差异是是否触发了分区裁剪，具体差异如下：

• Query2的过滤条件是编译期可确定的常量IN列表，Hive在SQL编译阶段就能拿到所有分区过滤值，直接跳过col1不匹配的分区，仅需扫描符合条件的数十个分区，数据量极小，因此执行速度快。
• Query1的分区过滤值来自子查询的返回结果，默认情况下Hive在SQL编译阶段无法获取子查询的实际返回值，无法触发编译期分区裁剪，只能先扫描源表全部3211个分区的近200亿条数据，再和子查询结果做关联过滤。同时Join逻辑需要执行全量数据Shuffle，额外消耗大量计算资源，最终导致执行时间飙升。

方案1：开启动态分区裁剪（无需修改SQL逻辑） #

Hive提供了运行时动态分区裁剪能力，开启后会优先执行子查询拿到分区过滤值，再下推到源表扫描阶段，避免全表扫描，直接执行以下参数配置即可：

set hive.optimize.dynamic.partition.pruning = true;
set hive.optimize.dynamic.partition.pruning.join.pattern = star;
set hive.exec.dynamic.partition.mode = nonstrict;

该方案适配绝大多数场景，改动成本最低。

方案2：改写为常量过滤逻辑 #

如果动态分区裁剪因版本问题不生效，且子查询返回的col1值数量不多，可以先查询子查询结果拼接为常量IN列表，再嵌入主查询执行，和Query2逻辑完全一致，可以用Shell脚本实现：

# 第一步查询得到日期列表，拼接为逗号分隔的字符串
date_list=$(hive -e "select concat_ws(',', collect_set(cast(col1 as string))) from temp_table where col1 between <start_date> and <end_date>")
# 第二步把常量列表传入主查询执行
hive -e "create table temp1 as select * from sourcetable where col1 in (${date_list})"

方案3：开启MapJoin优化消除Shuffle开销 #

如果子查询返回的temp_table1数据量小于64M，可开启自动MapJoin，将小表广播到所有Mapper节点，在Map阶段直接完成关联过滤，避免全量数据Shuffle到Reducer的开销：

set hive.auto.convert.join = true;
set hive.mapjoin.smalltable.filesize = 67108864; -- 配置小表阈值，单位为字节，默认64M

create table temp1 as 
with temp_table1 as (
    select col1 from temp_table where col1 between <start_date> and <end_date>
)
select /*+ MAPJOIN(f2) */ f1.* from sourcetable f1 join temp_table1 f2 on f1.col1 = f2.col1;

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