#!/bin/bash # # # # # # # # # # # # # # # # # # # # # # # @file :create_three.sh # @author :liushuai # @revision:2017-03-14 20:09 # # # # # # # # # # # # # # # # # # # # # # 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} file_dir="s3://mob-emr-test/liushuai/prd/${year}/${month}/${day}/four/three" hadoop fs -mkdir -p $file_dir hive_cmd " use dev; select count(*) as allNum,concat(sum(if(gender='m',1,0)),'(',round(sum(if(gender='m',1,0))/count(*)*100,2),'%',')'), concat(sum(if(gender='f',1,0)),'(',round(sum(if(gender='f',1,0))/count(*)*100,2),'%',')'), concat(sum(if(gender='o',1,0))+sum(if(gender is null,1,0)),'(',round((sum(if(gender='o',1,0))+sum(if(gender is null,1,0)))/count(*)*100,2),'%',')'), concat(sum(if(age='0-17',1,0)),'(',round(sum(if(age='0-17',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='18-24',1,0)),'(',round(sum(if(age='18-24',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='25-44',1,0)),'(',round(sum(if(age='25-44',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='45-59',1,0)),'(',round(sum(if(age='45-59',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='60+',1,0)),'(',round(sum(if(age='60+',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='unknown',1,0))+sum(if(age is null,1,0)),'(',round((sum(if(age='unknown',1,0))+sum(if(age is null,1,0)))/count(*)*100,2),'%',')') from tmp_dmp_and_m; " > mCount.txt if [ $? -ne 0 ];then exit 255 fi hadoop fs -rm $file_dir/mCount.txt hadoop fs -put mCount.txt $file_dir if [ $? -ne 0 ];then exit 255 fi hive_cmd " use dev; select count(*) as allNum,concat(sum(if(gender='m',1,0)),'(',round(sum(if(gender='m',1,0))/count(*)*100,2),'%',')'), concat(sum(if(gender='f',1,0)),'(',round(sum(if(gender='f',1,0))/count(*)*100,2),'%',')'), concat(sum(if(gender='o',1,0))+sum(if(gender is null,1,0)),'(',round((sum(if(gender='o',1,0))+sum(if(gender is null,1,0)))/count(*)*100,2),'%',')'), concat(sum(if(age='0-17',1,0)),'(',round(sum(if(age='0-17',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='18-24',1,0)),'(',round(sum(if(age='18-24',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='25-44',1,0)),'(',round(sum(if(age='25-44',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='45-59',1,0)),'(',round(sum(if(age='45-59',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='60+',1,0)),'(',round(sum(if(age='60+',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='unknown',1,0))+sum(if(age is null,1,0)),'(',round((sum(if(age='unknown',1,0))+sum(if(age is null,1,0)))/count(*)*100,2),'%',')') from tmp_dmp_and_dsp; " > dspCount.txt if [ $? -ne 0 ];then exit 255 fi hadoop fs -rm $file_dir/dspCount.txt hadoop fs -put dspCount.txt $file_dir if [ $? -ne 0 ];then exit 255 fi hive_cmd " use dev; select country,count(*) as allNum,concat(sum(if(gender='m',1,0)),'(',round(sum(if(gender='m',1,0))/count(*)*100,2),'%',')'), concat(sum(if(gender='f',1,0)),'(',round(sum(if(gender='f',1,0))/count(*)*100,2),'%',')'), concat(sum(if(gender='o',1,0))+sum(if(gender is null,1,0)),'(',round((sum(if(gender='o',1,0))+sum(if(gender is null,1,0)))/count(*)*100,2),'%',')'), concat(sum(if(age='0-17',1,0)),'(',round(sum(if(age='0-17',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='18-24',1,0)),'(',round(sum(if(age='18-24',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='25-44',1,0)),'(',round(sum(if(age='25-44',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='45-59',1,0)),'(',round(sum(if(age='45-59',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='60+',1,0)),'(',round(sum(if(age='60+',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='unknown',1,0))+sum(if(age is null,1,0)),'(',round((sum(if(age='unknown',1,0))+sum(if(age is null,1,0)))/count(*)*100,2),'%',')') from tmp_dmp_and_dsp group by country order by allNum desc limit 30; " >dspCountryCount.txt if [ $? -ne 0 ];then exit 255 fi hadoop fs -rm $file_dir/dspCountryCount.txt hadoop fs -put dspCountryCount.txt $file_dir if [ $? -ne 0 ];then exit 255 fi hive_cmd " use dev; select country,count(*) as allNum,concat(sum(if(gender='m',1,0)),'(',round(sum(if(gender='m',1,0))/count(*)*100,2),'%',')'), concat(sum(if(gender='f',1,0)),'(',round(sum(if(gender='f',1,0))/count(*)*100,2),'%',')'), concat(sum(if(gender='o',1,0))+sum(if(gender is null,1,0)),'(',round((sum(if(gender='o',1,0))+sum(if(gender is null,1,0)))/count(*)*100,2),'%',')'), concat(sum(if(age='0-17',1,0)),'(',round(sum(if(age='0-17',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='18-24',1,0)),'(',round(sum(if(age='18-24',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='25-44',1,0)),'(',round(sum(if(age='25-44',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='45-59',1,0)),'(',round(sum(if(age='45-59',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='60+',1,0)),'(',round(sum(if(age='60+',1,0))/count(*)*100,2),'%',')'), concat(sum(if(age='unknown',1,0))+sum(if(age is null,1,0)),'(',round((sum(if(age='unknown',1,0))+sum(if(age is null,1,0)))/count(*)*100,2),'%',')') from tmp_dmp_and_m group by country order by allNum desc limit 30; " > mCountryCount.txt if [ $? -ne 0 ];then exit 255 fi hadoop fs -rm $file_dir/mCountryCount.txt hadoop fs -put mCountryCount.txt $file_dir if [ $? -ne 0 ];then exit 255 fi