将csv文件导入到数据库中
代码示例
#!/bin/bash
############ ---------- Usage: sh imp.sh 264 or ./imp.sh 264 --------------#################
#require a integer parameter to indicate n+1 days to n days before sysdate
num=1
if (($# == 0));then
num=1
else
num=$1
fi
flag_file=/etc/oracle/ddcx/import_flag
#prev_date
prev_date=`date -d "-${num} day" +%Y-%m-%d`
#flag
flag=`cat $flag_file |grep ${prev_date}|wc -l`
if (($flag==1));then
echo 'file was already imported to table========='
exit 1
else
echo '=========beginning of importing data to respective table======='
fi
#prog path
prog_path=/home/oracle/lxm/imp_exp
#import path
imp_path=${prog_path}/imp
#backup path
backup_path=$prog_path/backup_file
#log file
logfile=${imp_path}/ctl/imp_data.log
# data file base path
data_file_base_path=${prog_path}/file
#link string
db_srv="lxm/lxm@//192.168.150.37:1521/lltf"
Create_Sqlldr_Ctl(){
ctl_file_path=${imp_path}/ctl
case $1 in
't_sagw_callreleasepush_msg')
cat>${ctl_file_path}/$1.ctl <<EOF
load data
infile *
append
into table t_sagw_callreleasepush_msg
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
TIME TIMESTAMP(3) ,
MSGTYPE ,
PUSHTS DATE'yyyy-mm-dd hh24:mi:ss',
CALLTYPE ,
PHONENO ,
SECRETNO ,
PEERNO ,
CALLTIME TIMESTAMP(3),
RINGINGTIME TIMESTAMP(3),
STARTTIME TIMESTAMP(3),
FINISHTIME TIMESTAMP(3),
CALLID ,
FINISHTYPE ,
FINISHSTATE ,
RECORDMODE ,
RECORDURL ,
BIZID ,
SUBID ,
CODE ,
MSG ,
DELAY ,
CREATE_TIME date'yyyy-mm-dd hh24:mi:ss',
RECORDFLAG ,
REDIRECT_FLAG ,
CALLOUTTIME TIMESTAMP(3),
APMTIME TIMESTAMP(3),
MSRN_TIME TIMESTAMP(3),
APPKEY_CODE
)
EOF
;;
't_smb_sub_msg')
cat>${ctl_file_path}/$1.ctl<<EOF
load data
infile *
append
into table t_smb_sub_msg
FIELDS TERMINATED BY ','
TRAILING NULLCOLS(
TIME TIMESTAMP(3),
MSGTYPE ,
BIZID ,
SUBID ,
PRTMS ,
SMBMS ,
SUBTS TIMESTAMP(3),
PRODUCTTYPE ,
EXPIRATION ,
RESULT ,
DELAY ,
APPKEY ,
OTHERMS ,
PRODUCTCAT ,
CALLRESTRICT ,
CALLRECORDING ,
ANUCODE ,
CALLDISPLAY ,
AREACODE ,
CITYID ,
CREATE_TIME date'yyyy-mm-dd hh24:mi:ss',
APPKEY_CODE ,
REQUESTID
)
EOF
;;
't_smb_unsub_msg')
cat>${ctl_file_path}/$1.ctl<<EOF
load data
infile *
append
into table t_smb_unsub_msg
FIELDS TERMINATED BY ','
TRAILING NULLCOLS(
TIME TIMESTAMP(3),
MSGTYPE ,
SUBID ,
PRTMS ,
SMBMS ,
UNSUBTS TIMESTAMP(3),
RESULT ,
DELAY ,
APPKEY ,
OTHERMS ,
PRODUCTCAT ,
UNSUBTYPE ,
CREATE_TIME date'yyyy-mm-dd hh24:mi:ss',
APPKEY_CODE
)
EOF
;;
't_sagw_cdrpush_msg')
cat>${ctl_file_path}/$1.ctl<<EOF
load data
infile *
append
into table t_sagw_cdrpush_msg
FIELDS TERMINATED BY ','
TRAILING NULLCOLS(
TIME TIMESTAMP(3),
MSGTYPE ,
PUSHTS date'yyyy-mm-dd hh24:mi:ss',
CALLTYPE ,
PHONENO ,
SECRETNO ,
PEERNO ,
CALLTIME TIMESTAMP(3) ,
CALLID ,
ORG_CALLED ,
SMSSENDSTATE ,
BIZID ,
SUBID ,
CODE ,
MSG ,
DELAY ,
CREATE_TIME date'yyyy-mm-dd hh24:mi:ss',
RECORDFLAG ,
APPKEY_CODE
)
EOF
;;
*)
echo 'table_name is not acceptable!!!' >>$logfile
esac
echo 'create control file of '$1
}
# sqlldr params
lv_rows=1000
lv_bindsize=8192000
lv_readsize=8192000
# 4 tables
arr=('t_sagw_callreleasepush_msg' 't_smb_sub_msg' 't_smb_unsub_msg' 't_sagw_cdrpush_msg')
for var in ${arr[@]};
do
echo $var
# create control file
Create_Sqlldr_Ctl $var
#data file
data_file=${data_file_base_path}/${var}/${var}_${prev_date}.txt
# logfile
log_f=${imp_path}/${var}.log
# bad file
bad_f=${imp_path}/${var}.bad
#dos2unix ${data_file}
sqlldr userid="$db_srv" control="${imp_path}/ctl/${var}.ctl" data=${data_file} log="$log_f" bad="$bad_f" rows="$lv_rows" bindsize="$lv_bindsize" readsize="$lv_readsize" direct=n >/dev/null 2>&1
if [ $? -eq 0 ]; then
mv $data_file $backup_path
fi
done
echo ${prev_date} >> $flag_file
echo 'successfully import data to respective table !!!'
echo '=========end of importing data to respective table======='
<wiz_tmp_tag id="wiz-table-range-border" contenteditable="false" style="display: none;">
附件列表