#! /bin/bash # # # # # # # # # # # # # # # # # # # # # # # @file :merge_dmp_dsp.sh # @author :liushuai # @revision:2017-03-06 15:15 # @desc :两天前的dsp日活与3天前的全量join # # # # # # # # # # # # # # # # # # # # # # source ../prd_env.sh LOG_TIME=$(date +%Y%m%d -d "-4 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 "-4 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_dsp; add file age.py; create table if not exists tmp_dmp_and_dsp as select dspanddmp.device_id, dspanddmp.device_type, dspanddmp.country,age,gender from ( select distinct dsp.device_id,dsp.device_type,tmp_dmp.country country from ( select device_id,device_type from dwh.etl_dsp_request_daily where date='${year}-${month}-${day}' and device_id is not null ) dsp join tmp_dmp on dsp.device_id=tmp_dmp.device_id and dsp.device_type=tmp_dmp.device_type ) dspanddmp 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=dspanddmp.device_id and age.device_type=dspanddmp.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=dspanddmp.device_id and gender.device_type=dspanddmp.device_type group by dspanddmp.device_id,dspanddmp.device_type,dspanddmp.country,age,gender; " if [ $? -ne 0 ];then exit 255 fi