#!/bin/bash
##########################################################
# author: zhangxh
# date: 2022-01-14
# 功能: 将指标数据文件数据导入风控表db_risk.brcp_rdifs_index
# 调用:sh SMY_BRCP_RDIFS_INDEX.sh [20220113]
#
# 参数:$1=ETL日期,默认昨天
#
# 变更记录:
# (1)
# (2)
##########################################################
#HOSTNAME="galileo_db"
#PORT="3316"
#USERNAME="appadmin"
#PASSWORD="appadmin"
HOSTNAME="192.168.116.1"
PORT="3307"
USERNAME="root"
PASSWORD="1234"
DBNAME="db_risk"
TABLE_NAME="brcp_rdifs_index"
TABLE_NAME_SUFFIX_NEW="_new"
TABLE_NAME_SUFFIX_TEMP="_temp"
IMPORT="11810"
TRUNCATE_SQL="truncate table $TABLE_NAME$TABLE_NAME_SUFFIX_NEW"
RENAME1_SQL="rename table $TABLE_NAME to $TABLE_NAME$TABLE_NAME_SUFFIX_TEMP"
RENAME2_SQL="rename table $TABLE_NAME$TABLE_NAME_SUFFIX_NEW to $TABLE_NAME"
RENAME3_SQL="rename table $TABLE_NAME$TABLE_NAME_SUFFIX_TEMP to $TABLE_NAME$TABLE_NAME_SUFFIX_NEW"
GET_TRUNCATE_TABLE_CNT_SQL="select count(1) from $TABLE_NAME$TABLE_NAME_SUFFIX_NEW"
GET_TABLE_CNT_SQL="select count(1) from $TABLE_NAME"
# Get yesterday's date
ETL_DATE=`date -d last-day +%Y%m%d`;
if [ $# -gt 0 ]
then
ETL_DATE=$1;
fi
echo "数据日期:$ETL_DATE"
FILEPATH="/app/appadmin/appdata/in/$ETL_DATE"
APPROC_PATH="/app/appadmin/appproc"
BRCP_FILE_DEL=.del
BRCP_SUFFIX=.del.gz
BRCP_SUFFIX_OK=.del.gz.ok
BRCP_PERFIX=SMY_BRCP_RDIFS_INDEX_
BRCP_NAME=$FILEPATH/$BRCP_PERFIX$ETL_DATE$BRCP_SUFFIX
BRCP_NAME_OK=$FILEPATH/$BRCP_PERFIX$ETL_DATE$BRCP_SUFFIX_OK
DATA_FILE_PATH=$FILEPATH/$BRCP_PERFIX$ETL_DATE$BRCP_FILE_DEL
LOG_PATH=/applog/shell-job/log/$ETL_DATE
LOG_File=$LOG_PATH/${ETL_DATE}_smy_brcp_rdifs_index.log
dealLog(){
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ dealLog()函数开始❤❤❤"
# 判断日志路径是否存在,不存在则创建
if [ ! -d $LOG_PATH ];then
echo "[`date +"%Y-%m-%d %H:%M:%S"`]日志目录[$LOG_PATH]不存在"
mkdir -p $LOG_PATH
chmod -R 700 $LOG_PATH
echo "[`date +"%Y-%m-%d %H:%M:%S"`]日志目录[$LOG_PATH]创建完成"
fi
# 判断日志文件是否存在,不存在则创建
if [ ! -f $LOG_File ];then
echo "[`date +"%Y-%m-%d %H:%M:%S"`]日志文件[$LOG_File]不存在"
touch $LOG_File
chmod -R 700 $LOG_File
echo "[`date +"%Y-%m-%d %H:%M:%S"`]日志文件[$LOG_File]创建完成"
fi
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ dealLog()函数结束❤❤❤"
}
doSleep(){
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ 睡眠[10min]中....◐◑◐◑◐◑"
sleep 5s;
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ 睡眠[10min]结束!◐◑◐◑◐◑"
}
juageDate(){
# 获取今天日期
let todayDate=$(date "+%Y%m%d")
let dateInterval=`expr $todayDate - $ETL_DATE`
echo "[`date +"%Y-%m-%d %H:%M:%S"`]当前日期[$todayDate]-数据日期[$ETL_DATE]间隔天数 : $dateInterval"
if [ $dateInterval -ne 1 ];then
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ 当前日期[$todayDate]-数据日期[$ETL_DATE]>1,则停止/跳过sleep检测"
# 生成一条运维日志
echo "生成一条运维日志: $1"
exit 2
else
# 线程睡眠...
doSleep
fi
}
checkFile(){
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ checkFile()函数开始**************"
while true
do
# 检查[in/日期]目录是否存在
if [ -d $FILEPATH ];then
echo "[`date +"%Y-%m-%d %H:%M:%S"`]检查目录[$FILEPATH]存在!";
# 检查.del.gz.ok文件
if [ -f $BRCP_NAME_OK ]
then
echo "[`date +"%Y-%m-%d %H:%M:%S"`]检查文件[$BRCP_NAME_OK]存在!";
# 检查.del.gz文件
if [ -f $BRCP_NAME ]
then
echo "[`date +"%Y-%m-%d %H:%M:%S"`]检查文件[$BRCP_NAME]存在!";
# 解压文件
echo -e "[`date +"%Y-%m-%d %H:%M:%S"`]解压文件中..."
gunzip -c $BRCP_NAME > $DATA_FILE_PATH
# 检查解压后的.del文件
if [ -f $DATA_FILE_PATH ];then
echo "[`date +"%Y-%m-%d %H:%M:%S"`]解压成功!";
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ checkFile()函数结束**************"
return 1;
else
echo "[`date +"%Y-%m-%d %H:%M:%S"`]检查解压后的文件[$DATA_FILE_PATH]不存在!";
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ checkFile()函数结束**************"
return 2;
fi
else
echo "[`date +"%Y-%m-%d %H:%M:%S"`]检查目录[$BRCP_NAME]不存在!";
# 判断日期
juageDate "检查解压后的文件[$DATA_FILE_PATH]不存在!"
fi
else
echo "[`date +"%Y-%m-%d %H:%M:%S"`]检查目录[$BRCP_NAME_OK]不存在!";
# 判断日期
juageDate "检查目录[$BRCP_NAME_OK]不存在!"
fi
else
echo "[`date +"%Y-%m-%d %H:%M:%S"`]检查目录[$FILEPATH]不存在!";
# 判断日期
juageDate "检查目录[$FILEPATH]不存在!"
fi
done
}
dealData(){
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ dealData()函数开始☠☠☠☠☠"
echo -e "[`date +"%Y-%m-%d %H:%M:%S"`]清空表[$TRUNCATE_SQL]中..."
$APPROC_PATH/mysqlbin/mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${TRUNCATE_SQL}"
cnt1=$($APPROC_PATH/mysqlbin/mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -s -e "${GET_TRUNCATE_TABLE_CNT_SQL}")
echo "[`date +"%Y-%m-%d %H:%M:%S"`]清空表后,[$TABLE_NAME$TABLE_NAME_SUFFIX_NEW]表记录数为:${cnt1}";
if [ ${cnt1} -ne 0 ];then
echo "[`date +"%Y-%m-%d %H:%M:%S"`]清空表SQL[$TRUNCATE_SQL]执行失败!";
# 生成一条运维日志
return 2;
fi
echo "[`date +"%Y-%m-%d %H:%M:%S"`]清空表成功!"
echo -e "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$,sdbimprt导入数据中..."
# $APPROC_PATH/bin/sdbimprt --hosts "${HOSTNAME}":"${IMPORT}" --user "${USERNAME}" --password "${PASSWORD}" --type csv --csname $DBNAME --clname $TABLE_NAME$TABLE_NAME_SUFFIX_NEW --headerline false --delfield '\03' --delrecord '\10' --fields='cert_no String,cust_name String,credit_money String,guarantee_money String,loan_up_count int,loan_in_count int,loan_out_count int,loan_over_count int,pos_or_pay int,phone_or_ebank int,activate_ensure int,three_over_count int,loan_money String,black_list_i int,three_in_badcount int,three_out_badcount int,credit_white_account int' --file ${DATA_FILE_PATH} -j 6 -n 10000;
ret=$($APPROC_PATH/bin/sdbimprt --hosts "${HOSTNAME}":"${IMPORT}" --user "${USERNAME}" --password "${PASSWORD}" --type csv --csname $DBNAME --clname $TABLE_NAME$TABLE_NAME_SUFFIX_NEW --headerline false --delfield '\03' --delrecord '\10' --fields='cert_no String,cust_name String,credit_money String,guarantee_money String,loan_up_count int,loan_in_count int,loan_out_count int,loan_over_count int,pos_or_pay int,phone_or_ebank int,activate_ensure int,three_over_count int,loan_money String,black_list_i int,three_in_badcount int,three_out_badcount int,credit_white_account int' --file ${DATA_FILE_PATH} -j 6 -n 10000);
retArr=(`echo ${ret}|awk '{len=split($0,ret,",");for(i=1;i<=len;i++) print ret[i]}'`)
parsedRecords=${retArr[2]}
parsedFailure=${retArr[5]}
shardingRecords=${retArr[8]}
shardingFailure=${retArr[11]}
importedRecords=${retArr[14]}
importedFailure=${retArr[17]}
echo "--sdbimprt执行信息----------------"
echo "parsedRecords:$parsedRecords"
echo "parsedFailure: $parsedFailure"
echo "shardingRecords: $shardingRecords"
echo "shardingFailure: $shardingFailure"
echo "importedRecords: $importedRecords"
echo "importedFailure: $importedFailure"
echo "----------------------------------"
if [ $parsedFailure -gt 0 ] || [ $shardingFailure -gt 0 ] || [ $parsedFailure -gt 0 ];then
echo "[`date +"%Y-%m-%d %H:%M:%S"`]sdbimprt导入parsed Failure记录数:$parsedFailure";
echo "[`date +"%Y-%m-%d %H:%M:%S"`]sdbimprt导入sharding Failure记录数:$shardingFailure";
echo "[`date +"%Y-%m-%d %H:%M:%S"`]sdbimprt导入imported Failure记录数:$importedFailure";
# 生成一条运维日志
echo "生成一条运维日志: sdbimprt导入完成,存在部分数据导入失败"
return 3;
fi
# 查询导入到数据库表的数据条数
cnt2=$($APPROC_PATH/mysqlbin/mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -s -e "${GET_TRUNCATE_TABLE_CNT_SQL}")
echo "[`date +"%Y-%m-%d %H:%M:%S"`]sdbimprt导入数据后[$TABLE_NAME$TABLE_NAME_SUFFIX_NEW]表记录数为:${cnt2}";
echo -e "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$,sdbimprt导入数据成功!"
echo -e "导入mysql数据中..."
MYSQL_SQL="load data local infile '$DATA_FILE_PATH' into table brcp_rdifs_index_new fields terminated by '' lines terminated by '\n' (cert_no,cust_name,@credit_money,@guarantee_money,@loan_up_count,@loan_in_count,@loan_out_count,@loan_over_count,@pos_or_pay,@phone_or_ebank,@activate_ensure,@three_over_count,@loan_money,@black_list_i,@three_in_badcount,@three_out_badcount,@credit_white_account) set credit_money=NULLif(@credit_money,''),guarantee_money=NULLif(@guarantee_money,''),loan_up_count=NULLif(@loan_up_count,''),loan_in_count=NULLif(@loan_in_count,''),loan_out_count=NULLif(@loan_out_count,''),loan_over_count=NULLif(@loan_over_count,''),pos_or_pay=NULLif(@pos_or_pay,''),phone_or_ebank=NULLif(@phone_or_ebank,''),activate_ensure=NULLif(@activate_ensure,''),three_over_count=NULLif(@three_over_count,''),loan_money=NULLif(@loan_money,''),black_list_i=NULLif(@black_list_i,''),three_in_badcount=NULLif(@three_in_badcount,''),three_out_badcount=NULLif(@three_out_badcount,''),credit_white_account=NULLif(@credit_white_account,'')"
$APPROC_PATH/mysqlbin/mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} --local-infile=1 -e "${MYSQL_SQL}"
echo -e "导入mysql数据完成"
cnt2=$($APPROC_PATH/mysqlbin/mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -s -e "${GET_TRUNCATE_TABLE_CNT_SQL}")
echo "导入mysql数据后[$TABLE_NAME$TABLE_NAME_SUFFIX_NEW]表记录数为:${cnt2}";
echo -e "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$,替换表名中..."
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$,执行SQL[$RENAME1_SQL]"
$APPROC_PATH/mysqlbin/mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${RENAME1_SQL}"
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$,执行SQL[$RENAME2_SQL]"
$APPROC_PATH/mysqlbin/mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${RENAME2_SQL}"
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$,执行SQL[$RENAME3_SQL]"
$APPROC_PATH/mysqlbin/mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${RENAME3_SQL}"
cnt3=$($APPROC_PATH/mysqlbin/mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -s -e "${GET_TABLE_CNT_SQL}")
echo "[`date +"%Y-%m-%d %H:%M:%S"`]查询[$TABLE_NAME]表记录数为:${cnt3}";
if [ ${cnt2} -ne ${cnt3} ];then
# 生成一条运维日志
echo "生成一条运维日志: sdbimprt导入完成,存在部分数据导入失败!"
return 4;
fi
echo -e "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$,替换表名完成!"
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ dealData()函数结束☠☠☠☠☠"
return 1;
}
main(){
echo "start=============================================================================="
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ main()函数开始执行☹☹☹☹☹☹"
# (0)创建日志文件
dealLog
# (1)检查大数据平台推过来的文件是否存在
checkFile
if [ $? -gt 1 ];then
# 生成一条运维日志
echo "生成一条运维日志: 检查解压后的文件[$DATA_FILE_PATH]不存在!"
exit 2
fi
# (2)处理数据及相关操作
dealData
let resultState=$?;
echo "dealData()函数执行结果返回值: $resultState"
if [ $resultState -gt 1 ];then
# 生成一条运维日志
echo "生成一条运维日志!dealData()函数执行结果返回值: $resultState"
fi
echo "[`date +"%Y-%m-%d %H:%M:%S"`]PID=$$ main()函数执行结束☹☹☹☹☹☹"
echo "end==============================================================================="
}
main 2>&1|tee -a $LOG_File;
shell脚本例子1-JLNX
于 2022-01-16 20:28:25 首次发布