二百二十一、HiveSQL报错：return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

一、目的

在运行HiveSQL时，执行报错

tatement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

二、在yarn上查看任务报错

The required MAP capability is more than the supported max container capability in the cluster. Killing the Job. mapResourceRequest: <memory:10150, vCores:1> maxContainerCapability:<memory:4096, vCores:4>

Job received Kill while in RUNNING state.

REDUCE capability required is more than the supported max container capability in the cluster. Killing the Job. reduceResourceRequest: <memory:10150, vCores:1> maxContainerCapability:<memory:4096, vCores:4>

三、解决报错

在尝试一堆Hive调优方式之后都不行

--hive调优
set hive.vectorized.execution.enabled=false;
set hive.auto.convert.join=false;
--map阶段内存不足
set mapreduce.map.memory.mb=10150;
set mapreduce.map.java.opts=-Xmx6144m;
-- reduce阶段内存不足
set mapreduce.reduce.memory.mb=10150;
set mapreduce.reduce.java.opts=-Xmx8120m;
--hive执行内存不足
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true;
set hive.exec.parallel=true;
set hive.support.concurrency=false;
set mapreduce.map.memory.mb=4128;

最后发现只需要一下一条调优语句即可

--hive调优
set hive.auto.convert.join=false;

四、调优后再次执行SQL

（一）执行动态加载SQL

动态加载数据执行成功！

（二）查看表数据

经此一遭才发现，Hive调优其实并不需要运行很多SQL，甚至运行调优SQL多了后会导致问题，这个需要尝试。

SQL运行报错后，可以到yarn里面看任务报错详情，然后根据报错详情再针对性的优化

set hive.auto.convert.join=false;