shell脚本数据处理

新生的阿拉木汗

已于 2022-07-04 14:33:18 修改

阅读量810

点赞数

文章标签：大数据 linux 运维

于 2022-07-04 14:29:51 首次发布

本文链接：https://blog.csdn.net/qq_32330469/article/details/125599126

版权

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#!/bin/sh

code_1=$1
cat ${code_1}.txt |awk -F'|' '{if (NF==3) print $1}' >>1_${code_1}.txt
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#!/bin/sh

_prov_code=$1

echo "${_prov_code}"

_clear_statis_date=`date -d '-120 day' '+%Y%m%d'`
echo "${_clear_statis_date}"

_remote_path="/upload/day/${_clear_statis_date}"
echo "${_remote_path}"

cd /data/interface/mql/
sftp ftp${_prov_code}@IP<<EOF
cd ${_remote_path}
rm *${_clear_statis_date}*
bye
EOF
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#!/bin/sh

#参数
_file_name=$1
_date=$2

#路径
_filepath="/data/interface/ngcard"
_logpath="/data/interface/ngcard/log"
_fail_path="/data/interface/ngcard/fail"
_bak_path="/data/interface/ngcard/bak"
#入库操作
cd ${_filepath}

#生成入库文件

ls ${_file_name} >cop_file.txt

#入库命令
for _file in `cat cop_file.txt`
do

#拼接命令

_cmd="COPY csapdwd.tb_dwd_ct_inre_kafka_ngcard_busi_log_day(statis_date as '${_date}',file_name as '${_file}',msgId,interfaceName,interfaceType,reqTime,reqResult,respTime,respResult,respMsg,busiNo,channelId,cardId,staffId,orgId,cityCode,countryCode,msisdn,provinceCode,queryTime,remark) FROM local '${_filepath}/${_file}' WITH PARSER fjsonParser() DIRECT EXCEPTIONS '${_logpath}/${_file}_${_date}_exceptions.LOG' REJECTED DATA '${_logpath}/${_file}_${_date}_rejected_time.LOG'"

/opt/vertica/bin/vsql -h IP4 -p 5433 -U csapdwd -w 1qaz\!QAZ -c "${_cmd}" > ${_logpath}/${_file}_${_date}_copy.log 2>&1

#判断入库

_fail=`cat ${_logpath}/${_file}_${_date}_copy.log|grep "ERROR"|wc -l`
if [ "${_fail}" != 0 ]
then
mv ${_file} ${_fail_path}
echo "${_file_name}_${_date} 入库失败！！！"
exit 2
else
mv ${_file} ${_bak_path}
fi

done

#删除前一天的日志文件
cd ${_logpath}
_now=$(pwd)
if [ ${_now} == "/data/interface/ngcard/log" ]
then
_rfile=$(date -d "${_date} 1 days ago " "+%Y%m%d")
rm *${_rfile}*.log
rm *${_rfile}*.LOG

fi
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#!/bin/sh
###参数1：要处理的文件名称；
###参数2：处理后输出的文件名称；
###参数3：处理数据过滤去重callId使用。
###具体jdk1.7或更好版本环境
echo "abc"

if [ $# -ne 3 ]
then
echo "no parm 3"
echo "参数1：要处理的文件名称；"
echo "参数2：处理后输出的文件名称；"
echo "参数3：处理数据过滤去重callId使用。"
echo "具体jdk1.7或更好版本环境"
exit
fi

file=$1
out=$2
path=$3
script_path="/home/hadoop/shell"

cd ${script_path}
echo "${file} ${out} ${path}"
echo "start -----"`date +%Y-%m-%d-%H:%M:%S`

java -jar esformate-day-SNAPSHOT.jar ${file} ${out} ${path}

echo "end -----"`date +%Y-%m-%d-%H:%M:%S`
#! /bin/bash
if [ $# -ne 2 ]
then
echo "parameter is error"
exit 1
fi
Date=`date +%Y%m%d`
extract_time=$1
statis_date=`date -d ${extract_time} +%Y%m%d`
#hour=$2
#path=$3
file_path=$2
echo "${statis_date} ------ ${hour}"
/opt/vertica/bin/vsql -U csapdw -p 5433 -h IP -w 密码 -At -F'||' -c "select vdn,entrance_id,'',call_region_id,be
long_prov_id,commu_nbr,person_wav,call_prov_id,if_people,prompt_wav,'','',war_addr,interaction_idx,busi_result,if_inter,'',template_id
,is_yz_node,if_sentence_right,if_right,busi_mark_result,'',grammar,flow_result,fail_reason,nvl(replace(replace(speak_end_time,'-',''),
' ',''),'1970010100:00:00'),inter_success,interaction_no,prompt_text,busi_son_id,if_dx,'',nvl(replace(replace(prompt_play_time,'-','')
,' ',''),'1970010100:00:00'),'',word_mark_result,rec_text,template_nr,mark_text,nvl(replace(replace(flow_result_time,'-',''),' ',''),'
1970010100:00:00'),num_busi_xysc,num_mjhsc,word_result,if_busi_dycg,'',flow_result_type,dx_status,if_yw_fm,commu_type_idx,rec_cost_tim
e,dx_success,simp_word_result,busi_mark_id,commu_remark,'','',flow_type,panoramic_wav,nvl(replace(replace(call_start,'-',''),' ',''),'
1970010100:00:00'),sub_ccno,call_remark,export_id,call_id,par_brand_id,time_id,'',if_sj,call_sor,nvl(replace(replace(call_end,'-',''),
' ',''),'1970010100:00:00'),'',if_yz,belong_region_id,brand_id,call_sc,if_voice,num_dx,'' from csapdw.tb_dw_ct_vona_ftmetrices_mid_day
where statis_date=${statis_date} order by call_id;" >${file_path}

#${path}/extract_tb_dw_ct_vona_ftmetrices_mid_day_${extract_time}_${hour}.txt
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#!/bin/sh

/usr/bin/mysql -hIP -P20003 -ucsap -ps0F041ND2\!sR --default-character-set=utf8 -NB -e "use ngcct_hn; select t.CALL_SWFTNO from ( select t1.BGN_CNTMNG_TIME, t1.END_CNTMNG_TIME, t1.CALL_SWFTNO, t1.FTIME_SATIS_DGR_LVL_CD, t1.SECD_TMS_SATIS_DGR_LVL_CD2, t1.STAFF_ID, t1.ONHOOK_TYPE_CD, t1.CALLING_NUM, t1.CALLED_NUM, t1.ACPT_NUM, t1.CALL_TYPE_CD, t1.FST_CUST_BELG_CITY_NM, t1.CUST_BRAND_NM, t1.CUST_STARGRD_CD, t2.RCDNG_FILE_SAVE_PATH, t1.CLCNT_ID, t1.BIZ_TYPE_ID, t2.RCDNG_FILE_STO_NODE_ID, replace(substr(t2.RCDNG_FILE_SAVE_PATH,3),'\\\','/') file_path , t1.INCALL_RSN_ID, t1.INCALL_RSN_NM, t1.WRKFM_ID, t1.LANG_ID, t1.LANG_NM, t1.DEPT_ID, t1.DEPT_NM from ( select BGN_CNTMNG_TIME, END_CNTMNG_TIME, CALL_SWFTNO, FTIME_SATIS_DGR_LVL_CD, SECD_TMS_SATIS_DGR_LVL_CD2, STAFF_ID, ONHOOK_TYPE_CD, CALLING_NUM, CALLED_NUM, ACPT_NUM, CALL_TYPE_CD, FST_CUST_BELG_CITY_NM, CUST_BRAND_NM, CUST_STARGRD_CD, CLCNT_ID, BIZ_TYPE_ID, INCALL_RSN_ID, INCALL_RSN_NM, WRKFM_ID, LANG_ID, LANG_NM, DEPT_ID, DEPT_NM, CALL_SKIL_ID, CALL_SKIL_NM, CTI_ID, VDN_NO FROM ngcct_hn.t_cct_contact_201812 WHERE BIZ_TYPE_ID='hnytck' and END_CNTMNG_TIME >= '2018-12-13 00:00:00' and END_CNTMNG_TIME < '2018-12-13 23:59:59' )t1 left outer join ngcct_hn.t_cct_callaffixinfo_201812 t2 on t1.CALL_SWFTNO=t2.CALL_SWFTNO where t2.RCDNG_FILE_SAVE_PATH is not null )t left outer join ngcct_hn.t_cct_recordfileftp t3 on substr(t.RCDNG_FILE_SAVE_PATH,1,1)=t3.DISK and t.BIZ_TYPE_ID=t3.BIZ_TYPE_ID and t.CLCNT_ID=t3.CLCNT_ID and t.RCDNG_FILE_STO_NODE_ID=t3.RCDNG_FILE_STO_NODE_ID where t3.OP_TYPE_CD = '0';">/data/interface/mql/test/mysql_ngcct_731.dat
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#!/bin/sh

/usr/bin/mysql -hIP -P20004 -ucsap -p1Va308j\$QNTtia0 --default-character-set=utf8 -NB -e "select t.CALL_SWFTNO from ( select t1.BGN_CNTMNG_TIME, t1.END_CNTMNG_TIME, t1.CALL_SWFTNO, t1.FTIME_SATIS_DGR_LVL_CD, t1.SECD_TMS_SATIS_DGR_LVL_CD2, t1.STAFF_ID, t1.ONHOOK_TYPE_CD, t1.CALLING_NUM, t1.CALLED_NUM, t1.ACPT_NUM, t1.CALL_TYPE_CD, t1.FST_CUST_BELG_CITY_NM, t1.CUST_BRAND_NM, t1.CUST_STARGRD_CD, t2.RCDNG_FILE_SAVE_PATH, t1.CLCNT_ID, t1.BIZ_TYPE_ID, t2.RCDNG_FILE_STO_NODE_ID, replace(substr(t2.RCDNG_FILE_SAVE_PATH,3),'\\','/') file_path, t1.INCALL_RSN_ID, t1.INCALL_RSN_NM, t1.WRKFM_ID, t1.LANG_ID, t1.LANG_NM, t1.DEPT_ID, t1.DEPT_NM from (select BGN_CNTMNG_TIME, END_CNTMNG_TIME, CALL_SWFTNO, FTIME_SATIS_DGR_LVL_CD, SECD_TMS_SATIS_DGR_LVL_CD2, STAFF_ID, ONHOOK_TYPE_CD, CALLING_NUM, CALLED_NUM, ACPT_NUM, CALL_TYPE_CD, FST_CUST_BELG_CITY_NM, CUST_BRAND_NM, CUST_STARGRD_CD, CLCNT_ID, BIZ_TYPE_ID, INCALL_RSN_ID, INCALL_RSN_NM, WRKFM_ID, LANG_ID, LANG_NM, DEPT_ID, DEPT_NM FROM ngcct_nx.t_cct_contact WHERE BIZ_TYPE_ID='nxytck' and END_CNTMNG_TIME >= '2018-10-24 00:00:00' and END_CNTMNG_TIME < '2018-10-24 23:59:59' )t1 left outer join ngcct_nx.t_cct_callaffixinfo t2 on t1.CALL_SWFTNO=t2.CALL_SWFTNO where t2.RCDNG_FILE_SAVE_PATH is not null )t left outer join ngcct_nx.t_cct_recordfileftp t3 on substr(t.RCDNG_FILE_SAVE_PATH,1,1)=t3.DISK and t.BIZ_TYPE_ID=t3.BIZ_TYPE_ID and t.CLCNT_ID=t3.CLCNT_ID and t.RCDNG_FILE_STO_NODE_ID=t3.RCDNG_FILE_STO_NODE_ID where t3.OP_TYPE_CD = '0';">/data/interface/mql/test/mysql_ngcct_951.dat
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#!/bin/sh
tableName=$1
statis_date=$2
statis_month=$3

date_formate=`date -d ${statis_date} '+%Y-%m-%d'`
#statis_month=`date -d ${statis_date} '+%Y%m'`
curMonth=`date '+%Y%m'`
curTime=`date '+%Y%m%d%H%M%s'`
prev_day=`date -d "${statis_date} -10 day" +%Y%m%d`
#last_month=`date -d "${statis_month}01 last month" +%Y%m`

#last_last_month=`date -d "${statis_month}01 last month last month" +%Y%m`

shellDir=/home/csap/extractShell
cfgDir=/home/csap/extractShell/cfg
logDir=/home/csap/extractShell/log
shellDir=/home/csap/extractShell/shell
dataDir=/data/csap/yewuzhongtaiData
cfgFile=/home/csap/extractShell/cfg/${tableName}.cfg

logFile=/home/csap/extractShell/log/${tableName}_${curTime}.log

echo "==============`date '+%Y-%m-%d %H-%M-%S'`================"
if [ ! -f "${cfgFile}" ]
then
echo "${cfgFile} does not exist,please confirm.exit 2"
exit 2
fi

while read extractInfo
do
selectTable1=`echo ${extractInfo}|awk -F '|' '{print $1}'`
selectTable=${selectTable1:0:19}
selectSql=`echo ${extractInfo}|awk -F '|' '{print $2}'|sed "s/yyyy-MM-dd/${date_formate}/g"`
dbIP=`echo ${extractInfo}|awk -F '|' '{print $3}'`
dbPort=`echo ${extractInfo}|awk -F '|' '{print $4}'`
dbName=`echo ${extractInfo}|awk -F '|' '{print $5}'`
dbUser=`echo ${extractInfo}|awk -F '|' '{print $6}'`
dbPwd=`echo ${extractInfo}|awk -F '|' '{print $7}'`

prov_code_l=`echo ${extractInfo}|awk -F '|' '{print $8}'`

if [ ! -f "${cfgDir}/${tableName}.col" ]
then
echo "${cfgDir}/${tableName}.col does not exist,please confirm.exit 4."
exit 4
fi

if [ ! -d ${dataDir} ]
then
echo "data directory ${dataDir} does not exist,please confirm.exit 5."
exit 5
else
#判断是否有文件，有文件就先删除
delFileCnt=`find ${dataDir} -name "${DataFileName}*"|wc -l`
if [ ${delFileCnt} -gt 0 ]
then
cd ${dataDir}
rm ${DataFileName}
fi
fi
columnList=`cat ${cfgDir}/${tableName}.col`
DataFileName=${dataDir}/${selectTable}_${statis_date}_${statis_month}_${dbName}.dat
Count=`mysql -h${dbIP} -P${dbPort} -D${dbName} -u${dbUser} -p${dbPwd} -NB -e "select count(*) from ${selectTable} where ${selectSql}"`
if [ $? -ne 0 ]
then
echo "mysql connect failure,connect info :${dbIP} ${dbPort} ${dbName} ${dbUser} ${selectTable},exit 3."
exit 3
fi
echo "${dbName}.${selectTable} Count is ${Count}"
echo "${DataFileName}"

#抽成文件
mysql -h${dbIP} -P${dbPort} -D${dbName} -u${dbUser} -p${dbPwd} -NB -e "set max_execution_time=300000;select ${columnList} from ${selectTable} where ${selectSql}">${DataFileName}

if [ $? -ne 0 ]
then
echo "mysql connect failure,connect info :${dbIP} ${dbPort} ${dbName} ${dbUser} ${selectTable},exit 6."
exit 6
fi
echo "The data file ${DataFileName} was successfully exported"

#判断抽出来的文件数据量和生产表数据量是否一致
fileCount=`cat ${DataFileName}|wc -l`
if [ ${Count} -ne ${fileCount} ]
then
echo "The number of db table records is not equal to the number of file records,exit 6"
else
echo "The number of records was compared successfully:${dbName}.${selectTable} ${DataFileName}"
fi

cp ${DataFileName} ${dataDir}/bak
done</home/csap/extractShell/cfg/${tableName}.cfg

#mv ${DataFileName} ${dataDir}/bak
rm -f ${dataDir}/bak/t_centre_work_order*_${prev_day}_*_${dbName}.dat
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#!/bin/sh
tableName=$1
statis_date=$2
statis_month=$3

#last_last_month=`date -d "${statis_month}01 last month last month" +%Y%m`

logFile=/home/csap/extractShell/log/${tableName}_${curTime}.log

echo "==============`date '+%Y-%m-%d %H-%M-%S'`================"
if [ ! -f "${cfgFile}" ]
then
echo "${cfgFile} does not exist,please confirm.exit 2"
exit 2
fi

while read extractInfo
do
selectTable=`echo ${extractInfo}|awk -F '|' '{print $1}'|sed "s/YYYYMM/${statis_month}/g"`
selectSql=`echo ${extractInfo}|awk -F '|' '{print $2}'|sed "s/yyyy-MM-dd/${date_formate}/g"`
dbIP=`echo ${extractInfo}|awk -F '|' '{print $3}'`
dbPort=`echo ${extractInfo}|awk -F '|' '{print $4}'`
dbName=`echo ${extractInfo}|awk -F '|' '{print $5}'`
dbUser=`echo ${extractInfo}|awk -F '|' '{print $6}'`
dbPwd=`echo ${extractInfo}|awk -F '|' '{print $7}'`

prov_code_l=`echo ${extractInfo}|awk -F '|' '{print $8}'`

if [ ! -f "${cfgDir}/${tableName}.col" ]
then
echo "${cfgDir}/${tableName}.col does not exist,please confirm.exit 4."
exit 4
fi

#抽成文件
mysql -h${dbIP} -P${dbPort} -D${dbName} -u${dbUser} -p${dbPwd} -NB -e "set max_execution_time=300000;select ${columnList} from ${selectTable} where ${selectSql}">${DataFileName}

if [ $? -ne 0 ]
then
echo "mysql connect failure,connect info :${dbIP} ${dbPort} ${dbName} ${dbUser} ${selectTable},exit 6."
exit 6
fi
echo "The data file ${DataFileName} was successfully exported"