将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;">

 

 

 

 

附件列表