set hive.exec.dynamic.partition.mode=nonstrict; set hive.exec.dynamic.partition=true; set mapred.max.split.size=536870912; set mapred.min.split.size.per.node=536870912; set mapred.min.split.size.per.rack=536870912; set mapreduce.map.memory.mb=4000; set mapreduce.map.java.opts=-Xmx3000m; set mapreduce.reduce.memory.mb=3072; set mapreduce.reduce.java.opts=-Xmx2458m; insert overwrite table dwh.dm_install_list_v2 partition(dt='${dt_today}',business) select device_id, device_type, platform, package_name, '${update_date}' update_date, ali_type business from dwh.etl_ali_user_activation_total lateral view explode(split(packagename,',')) r1 AS package_name where dt ='${dt_today}' UNION select device_id, device_type, platform, package_name, '${update_date}' update_date, ali_type business from dwh.etl_ali_ios_user_activation_total lateral view explode(split(packagename,',')) r1 AS package_name where dt ='${dt_today}'; insert overwrite table dwh.ods_dmp_user_info partition(dt='${dt_today}',business) select device_id, device_type, platform, 'CN' country, '' age, '' gender, '' tags, '${update_date}' first_req_day, '${update_date}' last_req_day, ali_type business from dwh.etl_ali_user_activation_total where dt ='${dt_today}' UNION select device_id, device_type, platform, 'CN' country, '' age, '' gender, '' tags, '${update_date}' first_req_day, '${update_date}' last_req_day, ali_type business from dwh.etl_ali_ios_user_activation_total where dt ='${dt_today}'; insert overwrite table dwh.gdt_data partition(day='${dt_today}',category='append',business) select t2.device_id,t2.filename business from (select device_id,substr(split(INPUT__FILE__NAME,"/")[11],6) as filename from dwh.gdt_data where day='${dt_yesterday}' and category='all' and business='total') t1 right join (select device_id,substr(split(INPUT__FILE__NAME,"/")[11],6) as filename from dwh.gdt_data where day='${dt_today}' and category='all' and business='total') t2 on t1.device_id=t2.device_id and t1.filename=t2.filename where t1.device_id is null and t1.filename is null; insert overwrite table dwh.gdt_data partition(day='${dt_today}',category='delete',business) select t1.device_id,t1.filename business from (select device_id,substr(split(INPUT__FILE__NAME,"/")[11],6) as filename from dwh.gdt_data where day='${dt_yesterday}' and category='all' and business='total') t1 left join (select device_id,substr(split(INPUT__FILE__NAME,"/")[11],6) as filename from dwh.gdt_data where day='${dt_today}' and category='all' and business='total') t2 on t1.device_id=t2.device_id and t1.filename=t2.filename where t2.device_id is null and t2.filename is null; insert overwrite table dwh.dm_install_list_v2_tmp_deviceid partition(dt='${dt_today}',device_type='219343imei') select distinct t1.device_id from (select device_id from dwh.dm_install_list_v2 where dt='${dt_today}' and business='ali_activation' and device_type='imei' and package_name in ('com.taobao.foractivation.219343')) t1 inner join (select device_id from dwh.dm_install_list_v2 where dt='${dt_oneday_ago}' and business='dsp_req' and device_type='imei' and package_name in ('com.taobao.taobao_oppo')) t2 on t1.device_id=t2.device_id; insert overwrite table dwh.dm_install_list_v2_tmp_deviceid partition(dt='${dt_today}',device_type='219343imeimd5') select distinct t1.device_id from (select device_id from dwh.dm_install_list_v2 where dt='${dt_today}' and business='ali_activation' and device_type='imeimd5' and package_name in ('com.taobao.foractivation.219343')) t1 inner join (select device_id from dwh.dm_install_list_v2 where dt='${dt_oneday_ago}' and business='dsp_req' and device_type='imeimd5' and package_name in ('com.taobao.taobao_oppo')) t2 on t1.device_id=t2.device_id; insert into table dwh.dm_install_list_v2 partition(dt='${dt_today}',business='ali_activation') select device_id,'imei' device_type,'android' platform,'com.taobao.foractivation.219343_oppo' package_name,'${update_date}' update_date from dwh.dm_install_list_v2_tmp_deviceid where dt='${dt_today}' and device_type='219343imei' UNION select device_id,'imeimd5' device_type,'android' platform,'com.taobao.foractivation.219343_oppo' package_name,'${update_date}' update_date from dwh.dm_install_list_v2_tmp_deviceid where dt='${dt_today}' and device_type='219343imeimd5'; insert overwrite table dwh.dm_install_list_v2_tmp_deviceid partition(dt='${dt_today}',device_type='219809imei') select distinct t1.device_id from (select device_id from dwh.dm_install_list_v2 where dt='${dt_today}' and business='ali_activation' and device_type='imei' and package_name in ('com.taobao.foractivation.219809')) t1 inner join (select device_id from dwh.dm_install_list_v2 where dt='${dt_oneday_ago}' and business='dsp_req' and device_type='imei' and package_name in ('com.taobao.taobao_oppo')) t2 on t1.device_id=t2.device_id; insert overwrite table dwh.dm_install_list_v2_tmp_deviceid partition(dt='${dt_today}',device_type='219809imeimd5') select distinct t1.device_id from (select device_id from dwh.dm_install_list_v2 where dt='${dt_today}' and business='ali_activation' and device_type='imeimd5' and package_name in ('com.taobao.foractivation.219809')) t1 inner join (select device_id from dwh.dm_install_list_v2 where dt='${dt_oneday_ago}' and business='dsp_req' and device_type='imeimd5' and package_name in ('com.taobao.taobao_oppo')) t2 on t1.device_id=t2.device_id; insert into table dwh.dm_install_list_v2 partition(dt='${dt_today}',business='ali_activation') select device_id,'imei' device_type,'android' platform,'com.taobao.foractivation.219809_oppo' package_name,'${update_date}' update_date from dwh.dm_install_list_v2_tmp_deviceid where dt='${dt_today}' and device_type='219809imei' UNION select device_id,'imeimd5' device_type,'android' platform,'com.taobao.foractivation.219809_oppo' package_name,'${update_date}' update_date from dwh.dm_install_list_v2_tmp_deviceid where dt='${dt_today}' and device_type='219809imeimd5'; insert into table dwh.dm_install_list_v2 partition(dt='${dt_today}',business='ali_activation') select device_id,'idfamd5' device_type,'ios' platform,'2020092201' package_name,'${update_date}' update_date from dwh.dm_install_list_v2_tmp_deviceid where dt='20200912test' and device_type='testios'; insert into table dwh.ods_dmp_user_info partition(dt='${dt_today}',business='ali_activation') select device_id, 'idfamd5' device_type, 'ios' platform, 'ID' country, '' age, '' gender, '' tags, '${update_date}' first_req_day, '${update_date}' last_req_day from dwh.dm_install_list_v2_tmp_deviceid where dt='20200912test' and device_type='testios';