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';