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