shell脚本例子1-JLNX

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