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;