在执行脚本:
create table liuchengtmp1_%CHINDAASDATE% as
select a.markcode, a.xiangmu,
case when instrfun(xiangmu,'已注册(') >0 THEN '已注册'
when instrfun(xiangmu,'初步审定(') >0 THEN '初步审定'
ELSE XIANGMU
END XIANGMU_new
from f_tm_process_hdfs_ext_%CHINDAASDATE% a
join (
select max(fliuc_id) fliucid1 from f_tm_process_hdfs_ext_%CHINDAASDATE% a join
(select a.markcode,max(a.liuchengdate) maxliuchendate,max(a.fliuc_idt) maxfliucidt
from f_tm_process_hdfs_ext_%CHINDAASDATE% a join
(select
markcode,
max(liuchengdate) maxliuchendate
from f_tm_process_hdfs_ext_%CHINDAASDATE% group by markcode) b
on a.markcode = b.markcode and a.liuchengdate = b.maxliuchendate
group by a.markcode) c
on a.markcode = c.markcode and a.liuchengdate = c.maxliuchendate and a.fliuc_idt = c.maxfliucidt
group by a.markcode; ) e on (a.fliuc_id = e.fliucid1);
脚本分拆成12个mr任务,在执行到第5个mr任务的时候报错如下:
网上找了好多资料,有的说这不是个错,有的说是内存不够造成,看上面报错截图,内存使用一直在增加,
因此估计是内存不够了,
后来处理措施为,将上述语句拆分成2步来执行,中间有一次数据落地,这样防止将所有数据都放在内存中执行,如下:
drop table if exists liuchengtmp_%CHINDAASDATE%;
create table liuchengtmp_%CHINDAASDATE% as
select max(fliuc_id) fliucid1 from f_tm_process_hdfs_ext_%CHINDAASDATE% a join
(select a.markcode,max(a.liuchengdate) maxliuchendate,max(a.fliuc_idt) maxfliucidt
from f_tm_process_hdfs_ext_%CHINDAASDATE% a join
(select
markcode,
max(liuchengdate) maxliuchendate
from f_tm_process_hdfs_ext_%CHINDAASDATE% group by markcode) b
on a.markcode = b.markcode and a.liuchengdate = b.maxliuchendate
group by a.markcode) c
on a.markcode = c.markcode and a.liuchengdate = c.maxliuchendate and a.fliuc_idt = c.maxfliucidt
group by a.markcode;
drop table if exists liuchengtmp1_%CHINDAASDATE%;
create table liuchengtmp1_%CHINDAASDATE% as
select a.markcode, a.xiangmu,
case when instrfun(xiangmu,'已注册(') >0 THEN '已注册'
when instrfun(xiangmu,'初步审定(') >0 THEN '初步审定'
ELSE XIANGMU
END XIANGMU_new
from f_tm_process_hdfs_ext_%CHINDAASDATE% a
join liuchengtmp_%CHINDAASDATE% b
on (a.fliuc_id = b.fliucid1);
修改后,执行,不在报错
下面是参考链接,值得看看老外们的说法: