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
#!/bin/bash
# # # # # # # # # # # # # # # # # # # # # #
# @file :dsp_app_gender_age.sh
# @author :liushuai
# @revision:2017-03-14 20:09
# # # # # # # # # # # # # # # # # # # # # #
source ../prd_env.sh
LOG_TIME=$(date +%Y%m%d -d "-2 day $ScheduleTime")
year=${LOG_TIME:0:4}
month=${LOG_TIME:4:2}
day=${LOG_TIME:6:2}
hive_cmd "
use dev;
drop table dsp_app_gender_age;
add file age.py;
create table if not exists dsp_app_gender_age
as
select distinct app.device_id,app.device_type,tmp_dmp.country,gender,age,app.package_name,app.url,app.category,app.platform from
(select * from dsp_app_url_category where year='${year}' and month='${month}' and day='${day}') app
join (
select *
from tmp_dmp t
where t.country in (${REPORT_COUNTRIES})
) tmp_dmp
on app.device_id=tmp_dmp.device_id and app.device_type=tmp_dmp.device_type
left outer join (
select transform(*)
using 'python age.py' as (device_id,device_type,package_names,age,tag) from
dwh.dm_device_age
where year='${year}' and
month='${month}' and
day='${day}'
) age
on
age.device_id=app.device_id and age.device_type=app.device_type
left outer join (
select device_id,device_type,gender from
dwh.dm_device_gender
where year='${year}' and
month='${month}' and
day='${day}'
) gender
on
gender.device_id=app.device_id and gender.device_type=app.device_type
where app.url is not null;
"
if [ $? -ne 0 ];then
exit 255
fi