#!/bin/sh # # # # # # # # # # # # # # # # # # # # # # # @file :dsp_count_country_interest.sh # @author :fengliang # @revision:${year}-09-08 # @desc :DMP 分国家分兴趣统计 # # # # # # # # # # # # # # # # # # # # # # source ../prd_env.sh echo "ScheduleTime=$ScheduleTime" LOG_TIME=$(date +%Y%m%d -d "-2 days $ScheduleTime") dt=$(date +"%Y-%m-%d" -d "-2 days $ScheduleTime") tag_time=$(date +%Y%m%d -d "-1 days $ScheduleTime") ga_time=$(get_recently_date $GA_DAILY_PATH $LOG_TIME "") today=$(ts=Asia/Shanghai date +%Y%m%d -d"1 day ago") device_time=$(get_recently_date $DMP_TOTAL_PATH $today "") date_path=$(date +"%Y/%m/%d" -d "-2 days $ScheduleTime") device_path=$(date +"%Y/%m/%d" -d "$device_time") year=${LOG_TIME:0:4} month=${LOG_TIME:4:2} day=${LOG_TIME:6:2} check_await $INSTALL_DAILY_3S/$date_path/_SUCCESS #check_await $TMP_ADSERVER_PACKAGE_PATH/$date_path/_SUCCESS check_await $DIM_ADN_CAMPAIGN/$date_path/_SUCCESS check_await $ETL_ADN_SDK_REQUEST_DAILY/$date_path/_SUCCESS check_await $ETL_DSP_REQ_DAILY/$date_path/_SUCCESS check_await $MP_REQUEST_DAILY_PATH/$date_path/_SUCCESS check_await $DMP_TOTAL_PATH/$device_path/_SUCCESS OUTPUT_PATH="$REQUEST_PATH/all_count_country_interest/$date_path" hql=" use dwh; drop table tmp_device_country_$LOG_TIME; drop table tmp_country_tag_${LOG_TIME}; drop table tmp_adn_install_${LOG_TIME}; drop table tmp_adn_request_${LOG_TIME}; drop table tmp_ga_install_${LOG_TIME}; set mapreduce.task.io.sort.mb=512; create table tmp_adn_install_${LOG_TIME} as select a.device_id, a.device_type, b.country from ( select t.device_id, t.device_type from etl_adn_install_daily t where t.date='${LOG_TIME}' ) a left outer join ( select device_id, t.id_type as device_type, t.country_code as country from ods_adn_device_total t where t.year='${year}' and t.month='${month}' and t.day='${day}' ) b on a.device_id=b.device_id and a.device_type=b.device_type; create table tmp_adn_request_${LOG_TIME} as select a.device_id, a.device_type, b.country from ( select t.device_id, t.device_type from etl_adn_sdk_request_daily t where t.date='${LOG_TIME}' ) a left outer join ( select device_id, t.id_type as device_type, t.country_code as country from ods_adn_device_total t where t.year='${year}' and t.month='${month}' and t.day='${day}' ) b on a.device_id=b.device_id and a.device_type=b.device_type; create table tmp_ga_install_${LOG_TIME} as select a.device_id, a.device_type, b.country from ( select t.device_id, t.device_type from ods_ga_install_daily t where t.date='$ga_time' ) a join ( select t.device_id, t.device_type, t.country from ods_ga_device_total t where t.year='${year}' and t.month='$month' and t.day='${day}' ) b on a.device_id=b.device_id and a.device_type=b.device_type; create table tmp_device_country_${LOG_TIME}( device_id string, device_type string, country string ) stored as rcfile; insert overwrite table tmp_device_country_${LOG_TIME} select t.device_id, t.device_type, case when t.country rlike '[A-Z0-9]{2}' then t.country else 'unknow' end as country from ( select t.device_id, t.device_type, t.country from etl_3s_install_daily t where t.date='${LOG_TIME}' union all select device_id, device_type, country from tmp_adn_install_${LOG_TIME} union all select device_id, device_type, country from tmp_adn_request_${LOG_TIME} union all select t.device_id, t.device_type, t.country_code as country from etl_dsp_request_daily t where t.date='${dt}' union all select device_id, device_type, country from tmp_ga_install_${LOG_TIME} ) t group by t.device_id, t.device_type, case when t.country rlike '[A-Z0-9]{2}' then t.country else 'unknow' end ; add jar ../${JAR}; create temporary function expansionTags as 'mobvista.prd.datasource.udf.ExpansionTags'; create table tmp_country_tag_${LOG_TIME} ( device_id string, device_type string, country string, tags string ) STORED as rcfile; insert overwrite table tmp_country_tag_${LOG_TIME} select a.device_id,a.device_type, a.country, b.tags from tmp_device_country_${LOG_TIME} a join ( select a.device_id, a.device_type, a.tags from dm_interest_tag a where a.year='${tag_time:0:4}' and a.month='${tag_time:4:2}' and a.day='${tag_time:6:2}' and a.business='all' ) b on a.device_id=b.device_id and a.device_type=b.device_type; insert overwrite directory '$OUTPUT_PATH' select t.country, t.firstTag, t.secondTag, count(1) as cnt from ( select a.device_id, a.device_type, a.country, b.firstTag, b.secondTag from tmp_country_tag_${LOG_TIME} a LATERAL VIEW expansionTags(a.tags) b as firstTag, secondTag group by a.device_id, a.device_type, a.country, b.firstTag, b.secondTag ) t group by t.country, t.firstTag, t.secondTag; drop table tmp_device_country_${LOG_TIME}; drop table tmp_country_tag_${LOG_TIME}; drop table tmp_adn_install_${LOG_TIME}; drop table tmp_adn_request_${LOG_TIME}; drop table tmp_ga_install_${LOG_TIME}; " hive_cmd "$hql" if [ $? -ne 0 ];then exit 255 fi