1. dump_data.sh调用sql_dump.sh导出数据
2. load_data.sh将导出的数据加载至KADB
1. dump_data.sh
#!/bin/bash
begin_time=$(date +%Y%m%d -d '-1 day')
end_time=$(date +%Y%m%d)
echo "数据导出日期:"$begin_time
echo "数据导出日期:"$begin_time >> .//log/dump_data_$begin_time.log
echo "数据导出终止日期:"$end_time
echo "数据导出终止日期:"$end_time >> .//log/dump_data_$begin_time.log
while read LINE
do
echo "表名:"$LINE
echo "表名:"$LINE >> .//log/dump_data_$begin_time.log
echo "实验参数: 表名 导出类型 开始时间 结束时间: "$LINE $begin_time $end_time
echo "实验参数: 表名 导出类型 开始时间 结束时间: "$LINE $begin_time $end_time >> .//log/dump_data_$begin_time.log
echo "./sql_dump.sh $LINE $begin_time $end_time"
echo [`date +%Y-%m-%d_%H:%M:%S`]"实验导出操作:./sql_dump.sh $LINE $begin_time $end_time"
echo [`date +%Y-%m-%d_%H:%M:%S`]"实验导出操作:./sql_dump.sh $LINE $begin_time $end_time" >> .//log/dump_data_$begin_time.log
while [[ 1==1 ]]
do
echo '查抄当前export历程数......'
echo '查抄当前export历程数......' >> .//log/dump_data_$begin_time.log
sleep 2
ips=`ps -ef | grep -v grep| grep sql_dump | wc -l`
echo 当前export历程数: $ips
echo 当前export历程数: $ips >> .//log/dump_data_$begin_time.log
if [ ${ips} -ge 4 ];then
sleep 2
else
#nohup sh ./sql_dump.sh $LINE $begin_time $end_time > .//"${LINE%% *}_`date +%Y%m%d`".log 2>&1 &
nohup sh ./sql_dump.sh $LINE $begin_time $end_time >> .//log/dump_data_$begin_time.log 2>&1 &
break;
fi
done
done < table.lst
2. sql_dump.sh
test=$1
flag=$2
begin_time=$3
end_time=$4
line=`wc -l $test.sql | cut -d ' ' -f 1`
export line
#echo 表名 test 导出类型flag 列数line
echo 表名test 导出类型flag 列数line >> .//log/dump_data_$begin_time.log
if [ $flag = 'E' ];then
# echo 当前表导出类型flag
#echo 当前表导出类型flag >> .//log/"${test}_$begin_time".log
awk -v line="$line" 'BEGIN{sql="select "}{if(NR<line){if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')\n""||'\'''\''||"}else{sql='sql'$1"||'\'''\''||"}}else{if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')"}else{sql='sql'$1}}}END{print 'sql'" from ""'$test'"" where fd_ie_flag=""'\''""'$flag'""'\''"" and fd_cus_release_time >= to_date(""'\''""'$begin_time'""'\'','\''yyyymmdd'\'')"" and fd_cus_release_time < to_date(""'\''""'$end_time'""'\'','\''yyyymmdd'\'')"";"}' ${test}.sql > ${test}_E.out
cat ${test}_E.out
# echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据
echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据 >> .//log/dump_data_$begin_time.log
sqlplus -s / as sysdba << EOF
set trimspool on;
set heading off;
set term off;
set feedback off;
set echo off;
SET SQLPROMPT "";
SET NEWPAGE NONE;
spool /home/oracle/${test}_E_${begin_time}.txt
@ ${test}_E.out
spool off;
set SQLPROMPT "SQL>";
exit;
EOF
touch ${test}_E_${begin_time}.done
# echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成
echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成 >> .//log/dump_data_$begin_time.log
chmod 777 /home/oracle/${test}_E_${begin_time}.txt
elif [ $flag = 'I' ];then
echo 当前表导出类型flag
# echo 当前表导出类型flag >> .//"${test}_$begin_time".log
awk -v line="$line" 'BEGIN{sql="select "}{if(NR<line){if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')\n""||'\'''\''||"}else{sql='sql'$1"||'\'''\''||"}}else{if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')"}else{sql='sql'$1}}}END{print 'sql'" from ""'$test'"" where fd_ie_flag=""'\''""'$flag'""'\''"" and fd_cus_clear_time >= to_date(""'\''""'$begin_time'""'\'','\''yyyymmdd'\'')"" and fd_cus_clear_time < to_date(""'\''""'$end_time'""'\'','\''yyyymmdd'\'')"";"}' ${test}.sql > ${test}_I.out
cat ${test}_I.out
# echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据
echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据 >> .//log/dump_data_$begin_time.log
sqlplus -s / as sysdba << EOF
set trimspool on;
set heading off;
set term off;
set feedback off;
set echo off;
SET SQLPROMPT "";
SET NEWPAGE NONE;
spool /home/oracle/${test}_I_${begin_time}.txt
@ ${test}_I.out
spool off;
set SQLPROMPT "SQL>";
exit;
EOF
touch ${test}_I_${begin_time}.done
# echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成
echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成 >> .//log/dump_data_$begin_time.log
chmod 777 /home/oracle/${test}_I_${begin_time}.txt
else
echo 当前表导出类型:$flag
echo 当前表导出类型:$flag >> .//"${test}_$begin_time".log
awk -v line="$line" 'BEGIN{sql="select "}{if(NR<line){if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')\n""||'\'''\''||"}else{sql='sql'$1"||'\'''\''||"}}else{if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')"}else{sql='sql'$1}}}END{print 'sql'" from ""'$test'"" where fd_cus_release_time >= to_date(""'\''""'$begin_time'""'\'','\''yyyymmdd'\'')"" and fd_cus_release_time < to_date(""'\''""'$end_time'""'\'','\''yyyymmdd'\'')"";"}' ${test}.sql > ${test}.out
cat ${test}.out
# echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据
echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据 .//log/dump_data_$begin_time.log
sqlplus -s / as sysdba << EOF
set trimspool on;
set heading off;
set term off;
set feedback off;
set echo off;
SET SQLPROMPT "";
SET NEWPAGE NONE;
spool /home/oracle/${test}_${begin_time}.txt
@ $test.out
spool off;
set SQLPROMPT "SQL>";
exit;
EOF
touch ${test}_${begin_time}.done
# echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成
echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成 .//log/dump_data_$begin_time.log
chmod 777 /home/oracle/${test}_${begin_time}.txt
fi
3. load_data.sh
#!/bin/bash
begin_time=$(date +%Y%m%d -d '-1 day')
end_time=$(date +%Y%m%d)
echo 导入开始日期: $begin_time
echo 导入结束日期: $end_time
WORKPATH=`dirname $0`
get_port()
{
kbport=$1
while [ $kbport -le 50000 ]
do
/usr/sbin/lsof -i:$kbport > /dev/null
if [ $? -eq 1 ]
then
echo $kbport
break
else
let kbport+=2
fi
done
}
while read LINE
do
#echo 表名: $LINE
type=${LINE##* }
if [ $type = 'O' ];then
table_name=${LINE%% *}
else
table_name=${LINE%% *}_$type
fi
echo 待导入表: $table_name
KB_PORT=`get_port 40000` #获取gpfdist可用的端口
while true
do
/usr/sbin/lsof -i:${KB_PORT}
if [ $? -eq 1 ]
then
if [ ! -d $WORKPATH/${KB_PORT} ];
then
mkdir $WORKPATH/${KB_PORT}
if [ $? -ne 0 ];then
echo $KB_PORT端口已经被使用
let KB_PORT+=2
KB_PORT=`get_port $KB_PORT`
continue;
fi
if [[ "$table_name" =~ _I$ ]];then
table_name_r=${table_name%_*}
echo $table_name_r
sed -e "s#THISISDATADIRECTORY#\./${table_name}_${begin_time}.txt#g;s#zhuyongzhuyong#${table_name_r}#g;s/KBPORT/${KB_PORT}/g;s/EXECSQLSTAT/delete from ${table_name_r} where fd_ie_flag='I' and fd_cus_clear_time >= to_date('${begin_time}','yyyymmdd') and fd_cus_clear_time < to_date('${end_time}','yyyymmdd');/g" z.mod > $table_name.yml
cat $table_name.yml
break;
elif [[ "$table_name" =~ _E$ ]]; then
table_name_r=${table_name%_*}
echo $table_name_r
sed -e "s#THISISDATADIRECTORY#\./${table_name}_${begin_time}.txt#g;s#zhuyongzhuyong#${table_name_r}#g;s/KBPORT/${KB_PORT}/g;s/EXECSQLSTAT/delete from ${table_name_r} where fd_ie_flag='E' and fd_cus_release_time >= to_date('${begin_time}','yyyymmdd') and fd_cus_release_time < to_date('${end_time}','yyyymmdd');/g" z.mod > $table_name.yml
cat $table_name.yml
break;
else
echo $table_name
sed -e "s#THISISDATADIRECTORY#\./${table_name}_${begin_time}.txt#g;s#zhuyongzhuyong#${table_name}#g;s/KBPORT/${KB_PORT}/g;s/EXECSQLSTAT/delete from ${table_name} where fd_cus_release_time >= to_date('${begin_time}','yyyymmdd') and fd_cus_release_time < to_date('${end_time}','yyyymmdd');/g" z.mod > $table_name.yml
cat $table_name.yml
break;
fi
else
let KB_PORT+=2
KB_PORT=`get_port $KB_PORT`
fi
else
sleep $(($RANDOM%5))
let KB_PORT+=2
KB_PORT=`get_port $KB_PORT`
fi
done
while true
do
if [ -f ${table_name}_${begin_time}.done ];then
ips=`ps -ef | grep -v grep| grep sql_dump | wc -l`
if [ ${ips} -ge 4 ];then
sleep 2
else
echo "begin load"
#echo "`date "+%Y-%m-%d_%H:%M:%S"`开始装载表:${table_name}"
#nohup gpload -f $table_name.yml > ./log/gpload_$table_name_`date +%Y%m%d`.log &
#grep "errors = [^0]\|failed\|ERROR" ./log/gpload_${table_name}_`date +%Y%m%d`.log > /dev/null #查抄gpload是否乐成
#if [ $? -eq 0 ]; then
# echo "GPLOAD ERROR!"
# echo $table_name >> ./log/error_`date +%Y%m%d`.lst
#fi
#echo "`date "+%Y-%m-%d_%H:%M:%S"`结束装载表:${table_name}"
#ROWSLOAD=`cat $WORKPATH/load_log/gpload_${table_name}_$(date +%Y%m%d).log | grep "rows Inserted" | cut -d "=" -f 2 | sed 's/^ //'` #获取装载数据行数
#echo $ROWSLOAD
#echo "`date "+%Y-%m-%d_%H:%M:%S"`本次装载数据${ROWSLOAD}行"
rm -rf $WORKPATH/${KB_PORT}
break;
fi
else
echo ${table_name}_${begin_time}.txt have not done
sleep 10
continue;
fi
done
done < table.lst
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
