#!/usr/bin/env bash # # # # # # # # # # # # # # # # # # # # # # # @file :connection_database.sh # @author :liushuai # @revision:2017-05-31 20:09 # @desc :dmp数据源结果放入数据库 # # # # # # # # # # # # # # # # # # # # # # 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} source ../prd_env.sh #纯新增 NEW_INPUT_PATH="s3://mob-emr-test/liushuai/prd/${year}/${month}/${day}/new_all_merge/" #交叉日活 CROSS_PATH="${REQUEST_PATH}/all_cross/${year}/${month}/${day}/" #其他id OTHER_ID_PATH="${REQUEST_PATH}/count_other_id/$year/$month/$day/" data_dir="../tmp" new_data_file="$data_dir/new_add_${LOG_TIME}.data" new_load_file="$data_dir/new_add_${LOG_TIME}.load" cross_data_file="$data_dir/cross_${LOG_TIME}.data" cross_load_file="$data_dir/cross_${LOG_TIME}.load" daily_data_file="$data_dir/daily_${LOG_TIME}.data" if [ ! -d $data_dir ];then mkdir -p $data_dir fi hadoop fs -text ${NEW_INPUT_PATH}/* > ${new_data_file} if [ $? -ne 0 ];then exit 255 fi hadoop fs -text ${CROSS_PATH}/* > ${cross_data_file} if [ $? -ne 0 ];then exit 255 fi hadoop fs -text ${OTHER_ID_PATH}/* > ${daily_data_file} if [ $? -ne 0 ];then exit 255 fi cat $new_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP新增数据\t"$1"\t-\t-\t-\t-\t"$2"\t"$2}' > $new_load_file del_sql=" DELETE FROM dmp_report_reuslt where day_key='${LOG_TIME}' and dimension_type1='DMP新增数据' and dimension_type2 in ('GA', 'DSP', 'M', '3S', 'ALL', 'gaid', 'idfa');" load_sql="$del_sql;load data local infile '${new_load_file}' into table dmp_report_reuslt;" $MYSQL_ETL "$load_sql" if [ $? -ne 0 ];then exit 255 fi > $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP交叉数据\tGADSP\t-\t-\t-\t-\t"$1"\t"$1}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP交叉数据\tGAM\t-\t-\t-\t-\t"$2"\t"$2}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP交叉数据\tGA3S\t-\t-\t-\t-\t"$3"\t"$3}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP交叉数据\tDSPM\t-\t-\t-\t-\t"$4"\t"$4}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP交叉数据\tDSP3S\t-\t-\t-\t-\t"$5"\t"$5}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP交叉数据\tM3S\t-\t-\t-\t-\t"$6"\t"$6}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP日活数据\tGA\t-\t-\t-\t-\t"$7"\t"$7}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP日活数据\tDSP\t-\t-\t-\t-\t"$8"\t"$8}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP日活数据\tM\t-\t-\t-\t-\t"$9"\t"$9}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP日活数据\t3S\t-\t-\t-\t-\t"$10"\t"$10}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP日活数据\tgaid\t-\t-\t-\t-\t"$11"\t"$11}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP日活数据\tidfa\t-\t-\t-\t-\t"$12"\t"$12}' >> $cross_load_file cat $cross_data_file | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP日活数据\tALL\t-\t-\t-\t-\t"$13"\t"$13}' >> $cross_load_file cat ${daily_data_file} | awk -F '\t' -v date=${LOG_TIME} '{print date"\tDMP日活数据\t"$1"\t-\t-\t-\t-\t"$1"\t"$2}' >> $cross_load_file del_sql=" DELETE FROM dmp_report_reuslt where day_key='${LOG_TIME}' and dimension_type1 in ('DMP交叉数据', 'DMP日活数据') and dimension_type2 in ('GA', 'DSP', 'M', '3S', 'gaid', 'idfa', 'imei', 'mac', 'android', 'GADSP', 'GAM', 'GA3S', 'DSPM', 'DSP3S', 'M3S')" load_sql="$del_sql;load data local infile '${cross_load_file}' into table dmp_report_reuslt;" $MYSQL_ETL "$load_sql" if [ $? -ne 0 ];then exit 255 fi rm $new_data_file $new_load_file $cross_data_file $cross_load_file