dsp_app_gender_age.sh 1.42 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
#!/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