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