大型Hive查询优化求助：OOM内存溢出问题排查与改写

查询改写优化 #

• 先过滤再关联，削减关联数据量
  不要先关联全表再过滤无效数据，优先对两张表做前置过滤，只保留关联和输出必需的字段，避免冗余数据占用资源。示例改写：

  SELECT d.required_col1, d.required_col2, i.col_a, i.col_b
  FROM (
      -- 若dre_output有无效数据，先过滤
      SELECT required_col1, required_col2, item_id FROM dre_output
  ) d
  JOIN (
      -- 提前过滤无效商品，只保留关联和输出需要的字段
      SELECT id, col_a, col_b FROM item_tbl WHERE is_valid = 1
  ) i ON d.item_id = i.id
  INSERT OVERWRITE TABLE service1_db.bottle_input PARTITION(pt)
  SELECT required_col1, required_col2, col_a, col_b, pt_col; -- 替换为实际输出字段和分区字段
  

• 用分桶排序关联（SMB Join）优化大表关联
  针对item_tbl这种超大表，先按关联键id分桶（比如分100桶），dre_output也按item_id分相同数量的桶，开启SMB Join参数后，Hive可直接在分桶间做关联，避免全表数据洗牌：

  SET hive.auto.convert.sortmerge.join=true;
  SET hive.optimize.bucketmapjoin=true;
  SET hive.optimize.bucketmapjoin.sortedmerge=true;
  SET hive.exec.dynamic.partition.mode=nonstrict; -- 适配分区表写入
  
  SELECT d.*, i.col_a, i.col_b
  FROM dre_output d
  JOIN item_tbl i ON d.item_id = i.id
  WHERE i.is_valid = 1
  INSERT OVERWRITE TABLE service1_db.bottle_input PARTITION(pt)
  SELECT ...; -- 替换为实际输出字段
  

• 拆分数据倾斜的热点关联键
  如果某个item_id对应数据量极大（占比超30%），会导致单个Reducer过载。可拆分热点数据单独处理：

  -- 处理非热点数据
  INSERT OVERWRITE TABLE service1_db.bottle_input PARTITION(pt)
  SELECT d.*, i.col_a, i.col_b
  FROM dre_output d
  JOIN item_tbl i ON d.item_id = i.id
  WHERE i.is_valid = 1 AND d.item_id NOT IN ('hot_key_1', 'hot_key_2');
  
  -- 单独处理热点数据，启用MapJoin（若热点对应的dre_output数据量较小）
  SET hive.auto.convert.join=true;
  INSERT INTO TABLE service1_db.bottle_input PARTITION(pt)
  SELECT d.*, i.col_a, i.col_b
  FROM (SELECT * FROM dre_output WHERE item_id IN ('hot_key_1', 'hot_key_2')) d
  JOIN item_tbl i ON d.item_id = i.id
  WHERE i.is_valid = 1;
  

Tez参数调优（解决GC/OOM） #

• 调整Container内存与JVM GC策略
  增大Container内存，同时用G1GC优化GC行为，避免GC overhead和OOM：

  SET tez.container.size=8192; -- 每个Container分配8G内存
  SET tez.java.opts=-Xmx6144m -XX:+UseG1GC -XX:MaxGCPauseMillis=200 -XX:G1HeapRegionSize=32m;
  -- 留2G内存给系统，G1GC可减少GC停顿，避免GC overhead触发任务终止
  

• 优化Reducer数量
  避免单个Reducer处理过多数据，设置阈值让Hive自动计算合适的Reducer数：

  SET hive.exec.reducers.bytes.per.reducer=268435456; -- 每个Reducer处理256MB数据
  -- 也可手动指定Reducer数，比如1000（根据集群资源和数据量调整）
  -- SET mapreduce.job.reduces=1000;
  

• 启用并行执行与中间数据压缩

  SET hive.exec.parallel=true; -- 开启多阶段任务并行执行
  SET hive.exec.parallel.thread.number=8; -- 并行任务数，根据集群资源调整
  SET hive.exec.compress.intermediate=true; -- 压缩中间数据，减少IO和内存占用
  SET mapreduce.map.output.compress=true;
  SET mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
  

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