set hive.ignore.mapjoin.hint=flase; set hive.skewjoin.key=10000; set hive.optimize.skewjoin=true; set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; set hive.optimize.bucketmapjoin = true; set hive.optimize.bucketmapjoin.sortedmerge = true; set hive.merge.mapredfiles=true; set hive.exec.parallel=true; set hive.exec.parallel.thread.number=8; insert overwrite table tkdm.tkdm_base_device_pay_info partition(ds='2017-02-24') select /*+mapjoin(a)*/ nvl(b.appid,a.appid) as appid, nvl(b.deviceid,a.deviceid) as deviceid, nvl(b.first_payment_ds,'2017-02-24') as first_payment_ds, if(isnull(a.deviceid),'2017-02-24',nvl(b.last_payment_ds,'1970-01-01')) as last_payment_ds, nvl(b.num_payment_cnt,0)+nvl(a.num_payment_cnt,0) as num_payment_cnt, nvl(b.num_payment_day,0)+nvl(a.num_payment_day,0) as num_payment_day, round(nvl(b.amt_payment,0) + nvl(a.amt_payment,0),2) as amt_payment from (select appid, deviceid, first_payment_ds, last_payment_ds, num_payment_cnt, num_payment_day, amt_payment from tkdm.tkdm_base_device_pay_info where ds= date_sub('2017-02-24',1) ) b left join (select appid, deviceid, is_new, amt_payment, num_payment_cnt, 1 as num_payment_day from tkdm.tkdm_data_payment_detial_day where ds='2017-02-24' and deviceid!='00000000-0000-0000-0000-000000000000' and deviceid!='' and deviceid!='0' ) a on a.appid=b.appid and a.deviceid=b.deviceid union all select /*+mapjoin(aa)*/ nvl(bb.appid,aa.appid) as appid, nvl(bb.deviceid,aa.deviceid) as deviceid, nvl(bb.first_payment_ds,'2017-02-24') as first_payment_ds, if(isnull(aa.deviceid),'2017-02-24',nvl(bb.last_payment_ds,'1970-01-01')) as last_payment_ds, nvl(bb.num_payment_cnt,0)+nvl(aa.num_payment_cnt,0) as num_payment_cnt, nvl(bb.num_payment_day,0)+nvl(aa.num_payment_day,0) as num_payment_day, round(nvl(bb.amt_payment,0) + nvl(aa.amt_payment,0),2) as amt_payment from (select appid, deviceid, is_new, amt_payment, num_payment_cnt, 1 as num_payment_day from tkdm.tkdm_data_payment_detial_day where ds='2017-02-24' and deviceid!='00000000-0000-0000-0000-000000000000' and deviceid!='' and deviceid!='0' ) aa left join (select appid, deviceid, first_payment_ds, last_payment_ds, num_payment_cnt, num_payment_day, amt_payment from tkdm.tkdm_base_device_pay_info where ds= date_sub('2017-02-24',1) ) bb on aa.appid=bb.appid and aa.deviceid=bb.deviceid where bb.deviceid is null