Hive SQL查询优化求助：嵌套子查询资源占用过高问题

原SQL核心问题分析 #

• 重复扫描同一张分区表：两个子查询分别对dws.dws_clt_video_event_log_simple_hour的同一分区执行全表扫描，双倍消耗IO和计算资源
• 重复调用UDF：exp_ab函数在WHERE条件和子查询中多次重复计算，增加CPU开销
• 多层嵌套+FULL JOIN：子查询嵌套提升执行计划复杂度，FULL JOIN操作需要额外的shuffle和排序资源

具体优化方案 #

• 单次扫描表，复用中间结果：将过滤、UDF计算逻辑合并为一次扫描，后续聚合直接基于中间结果计算，避免重复IO
• 减少UDF调用次数：通过WHERE条件一次性完成UDF计算与过滤，避免子查询中重复执行UDF
• 合并聚合逻辑，移除JOIN：在同一个GROUP BY中同时计算UV和VP，省掉JOIN带来的资源消耗

优化后的SQL #

create temporary function exp_ab as 'com.udf.OverseasForuAbExpUDF' using jar 'hdfs://nameservice1/user/hive/udf/dz-ark-1.0-SNAPSHOT.jar';

SELECT
    ext_map['book_name'] AS book_name,
    COUNT(DISTINCT user_id) AS UV,
    COUNT(*) AS VP
FROM dws.dws_clt_video_event_log_simple_hour
WHERE dt = '02024061023'
  AND exp_ab(user_id, "B552B171", 1000, "15D3271B", 99) = 'foru-base'
GROUP BY ext_map['book_name'];

额外优化建议 #

• 优化Map字段访问：如果ext_map['book_name']是高频访问字段，建议将其抽取为表的一级字段，避免每次解析Map的开销
• 调整Hive执行参数：针对大分区查询，可根据集群资源调整以下参数：
  • set mapreduce.map.memory.mb=4096;
  • set mapreduce.reduce.memory.mb=8192;
  • set hive.exec.reducers.bytes.per.reducer=1073741824;（调整每个Reducer处理的数据量）
• 先采样验证逻辑：使用TABLESAMPLE对数据采样，确认优化后SQL逻辑正确再全量执行

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