执行hive sql时遇到问题:
FAILED:Execution Error,return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
这个其实一般是个hive内部错误,处理这个问题就需要找日志了;
找日志
- hive.log(比较简略)
hive.log ===> 缺省情况下 /tmp/root/hive.log (不然就在你hive-site.conf配置的日志路径里)
我这里在/tmp/root目录下
去里面看了日志,没有什么有价值的信息,只得去找MR日志了。 - MR的日志(详细些)
看MR日志,需要启动historyserver、日志聚合 + SQL运行在集群模式
因为我在hive-site.conf中配置了本地模式
所以我从历史日志中没有找到相关日志记录,所以在运行sql前执行
然后再执行之前的sqlset hive.exec.mode.local.auto=false
再就可以从JobHistory页面上看到对应的执行job,可以进去看详细的错误日志
进去就可以看到详细的错误信息了
解决方式:
其实之前写的sql没有什么大的问题,不过考虑之后还是改写一下sql
改动之前:
#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
insert overwrite table dws.dws_member_retention_day partition(dt='$do_date')
(
select
t2.device_id,
t2.uid,
t2.app_v,
t2.os_type,
t2.language,
t2.channel,
t2.area,
t2.brand,
t2.dt add_date,
1
from
dws.dws_member_start_day t1
join
dws.dws_member_add_day t2
on
t1.device_id=t2.device_id
where
t2.dt=date_add('$do_date',-1)
and
t1.dt='$do_date'
union all
select
t2.device_id,
t2.uid,
t2.app_v,
t2.os_type,
t2.language,
t2.channel,
t2.area,
t2.brand,
t2.dt add_date,
2
from
dws.dws_member_start_day t1
join
dws.dws_member_add_day t2
on
t1.device_id=t2.device_id
where
t2.dt=date_add('$do_date',-2)
and
t1.dt='$do_date'
union all
select
t2.device_id,
t2.uid,
t2.app_v,
t2.os_type,
t2.language,
t2.channel,
t2.area,
t2.brand,
t2.dt add_date,
3
from
dws.dws_member_start_day t1
join
dws.dws_member_add_day t2
on
t1.device_id=t2.device_id
where
t2.dt=date_add('$do_date',-3)
and
t1.dt='$do_date'
);
"
hive -e "$sql"
改动sql之后执行成功:(我这里是写在脚本中的sql)
#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
create table tmp.tmp_member_retention as
(
select
t2.device_id,
t2.uid,
t2.app_v,
t2.os_type,
t2.language,
t2.channel,
t2.area,
t2.brand,
t2.dt add_date,
1
from
dws.dws_member_start_day t1
join
dws.dws_member_add_day t2
on
t1.device_id=t2.device_id
where
t2.dt=date_add('$do_date',-1)
and
t1.dt='$do_date'
union all
select
t2.device_id,
t2.uid,
t2.app_v,
t2.os_type,
t2.language,
t2.channel,
t2.area,
t2.brand,
t2.dt add_date,
2
from
dws.dws_member_start_day t1
join
dws.dws_member_add_day t2
on
t1.device_id=t2.device_id
where
t2.dt=date_add('$do_date',-2)
and
t1.dt='$do_date'
union all
select
t2.device_id,
t2.uid,
t2.app_v,
t2.os_type,
t2.language,
t2.channel,
t2.area,
t2.brand,
t2.dt add_date,
3
from
dws.dws_member_start_day t1
join
dws.dws_member_add_day t2
on
t1.device_id=t2.device_id
where
t2.dt=date_add('$do_date',-3)
and
t1.dt='$do_date'
);
insert overwrite table dws.dws_member_retention_day partition(dt='$do_date')
select * from tmp.tmp_member_retention;
"
hive -e "$sql"
其实sql改动不大,无非是,改动后的是,创建一个临时表,在插入到对应的表中