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
#!/bin/bash
# # # # # # # # # # # # # # # # # # # # # #
# @file :app_gender_age.sh
# @author :liushuai
# @revision:2017-03-14 20:09
# @desc :得到M系统的性别年龄表
# # # # # # # # # # # # # # # # # # # # # #
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 if exists app_gender_age;
add file age.py;
create table if not exists app_gender_age
as
select app.device_id,app.device_type,tmp_dmp.country,gender,age,app.package_name,app.url,app.category,app.platform
from
(
select *
from app_url_category
where year='${year}' and month='${month}' and day='${day}' and url is not null and url <> ''
) 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
group by app.device_id, app.device_type, tmp_dmp.country,
gender, age, app.package_name, app.url, app.category, app.platform
;
"
if [ $? -ne 0 ];then
exit 255
fi