Hive SQL查询结果过大超内存限制，不扩内存的优化方案求助

1. SQL逻辑精简优化 #

你当前的SQL存在冗余嵌套，且可对字符串匹配逻辑简化，优化后等效SQL如下：

SELECT DISTINCT
    requestid,
    IF (strategy_path LIKE '%4-54-%', 'EXP', 'BASE') AS bucket
FROM log.aar
WHERE
    dt BETWEEN '20210908' AND '20210917'
    AND channel = 'S'
    AND slot_ids LIKE '%50011%'
    AND strategy_path RLIKE '4-5-[12]'
    AND platform = 'AT'

• 去掉无意义的子查询嵌套，减少中间临时数据集的内存占用
• 用RLIKE正则匹配替代多个LIKE OR逻辑，减少字符串匹配的计算开销
• 用DISTINCT替代等效的GROUP BY语义，执行器可选择更轻量化的去重策略

2. 执行参数优化 #

• 开启Map端预聚合：set hive.map.aggr = true，在Map阶段提前做局部去重，大幅减少Shuffle阶段传输、Reducer端加载的数据量
• 开启倾斜数据优化：set hive.groupby.skewindata = true，针对requestid存在大量重复导致的单Reducer数据倾斜，会自动做两轮MR拆分，避免单节点内存过载
• 提高Reducer并行度：调低单Reducer处理的数据量阈值，比如set hive.exec.reducers.bytes.per.reducer = 67108864（即64M，默认通常为256M），自动拆分出更多Reducer，将数据分散到多个节点处理，避免单Reducer数据量超过内存上限
• 开启中间结果压缩：set hive.exec.compress.intermediate = true，使用Snappy等轻量压缩算法压缩Shuffle阶段的中间数据，降低内存和网络传输中的数据体积，CPU开销极低，内存收益明显

3. 执行逻辑拆分 #

如果上述参数优化后仍存在OOM，可将大任务拆分为多个小任务执行：

• 按dt分区分批处理，比如将10天的数据拆分为2次各5天的查询，分别跑出结果后再合并，单次处理的数据量直接减半，内存压力大幅降低

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