#!/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