hive问题-return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

执行hive sql时遇到问题：
FAILED:Execution Error,return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

这个其实一般是个hive内部错误，处理这个问题就需要找日志了；
找日志

1. hive.log(比较简略)
   hive.log ===> 缺省情况下 /tmp/root/hive.log (不然就在你hive-site.conf配置的日志路径里)
   我这里在/tmp/root目录下
   
   去里面看了日志，没有什么有价值的信息，只得去找MR日志了。
2. MR的日志（详细些）
   看MR日志，需要启动historyserver、日志聚合 + SQL运行在集群模式
   因为我在hive-site.conf中配置了本地模式
   
   所以我从历史日志中没有找到相关日志记录，所以在运行sql前执行

   set hive.exec.mode.local.auto=false
   1

   然后再执行之前的sql
   
   再就可以从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"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

改动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"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

其实sql改动不大，无非是，改动后的是，创建一个临时表，在插入到对应的表中