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
#! /bin/bash
# # # # # # # # # # # # # # # # # # # # # #
# @file :merge_dmp_m.sh
# @author :liushuai
# @revision:2017-03-06 10:45
# @desc :两天前的M日活与3天前的全量join
# # # # # # # # # # # # # # # # # # # # # #
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}
OLD_LOG_TIME=$(date +%Y%m%d -d "-2 day $ScheduleTime")
old_year=${OLD_LOG_TIME:0:4}
old_month=${OLD_LOG_TIME:4:2}
old_day=${OLD_LOG_TIME:6:2}
hive_cmd "
use dev;
drop table tmp_dmp_and_m;
add file age.py;
create table if not exists tmp_dmp_and_m
as
select distinct manddmp.device_id device_id,manddmp.device_type device_type,manddmp.country,age,gender from (
select distinct m.device_id,m.device_type,tmp_dmp.country country from (
select device_id,device_type from
dwh.etl_adn_sdk_request_daily
where date='${LOG_TIME}' and
device_id is not null
) m
join
tmp_dmp
on
m.device_id=tmp_dmp.device_id and m.device_type=tmp_dmp.device_type
) manddmp
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='${old_year}' and
month='${old_month}' and
day='${old_day}'
) age
on
age.device_id=manddmp.device_id and age.device_type=manddmp.device_type
left outer join (
select device_id,device_type,gender from
dwh.dm_device_gender
where year='${old_year}' and
month='${old_month}' and
day='${old_day}'
) gender
on
gender.device_id=manddmp.device_id and gender.device_type=manddmp.device_type;
"
if [ $? -ne 0 ];then
exit 255
fi