ali_userinfo_postback_activation_daily.sql 6.94 KB
Newer Older
wang-jinfeng committed
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 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153
  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';