應用需求：

---------------------------------------------------------------

請將 1,2日誌內容插入 1.75 資料庫warehouse 賬號下

1,2日誌都在1.75伺服器上

1.

/logbackup/

|-- apache

目錄下

如下訪問日誌

m.xrcj.wxxr.cn

service.stock.test.wxxr.cn

stock.test.wxxr.cn

v1x

需要的欄位，site,ip,時間（date型別）,url ，請求結果程式碼，返回位元組數

作業系統上的檔案資訊：

-----------------------------------------

[oracle@searchdb2 201209]$pwd

/logbackup/apache/201209

[oracle@searchdb2 201209]$ls

m.xrcj.wxxr.cn-access_log_20120911 stock.test.wxxr.cn-access_log_20120916 v1x-access_log_20120906

m.xrcj.wxxr.cn-access_log_20120912 v1x-access_log v1x-access_log_20120907

m.xrcj.wxxr.cn-access_log_20120913 v1x-access_log_20120822 v1x-access_log_20120908

m.xrcj.wxxr.cn-access_log_20120914 v1x-access_log_20120823 v1x-access_log_20120909

m.xrcj.wxxr.cn-access_log_20120915 v1x-access_log_20120824 v1x-access_log_20120910

m.xrcj.wxxr.cn-access_log_20120916 v1x-access_log_20120825 v1x-access_log_20120911

service.stock.test.wxxr.cn-access_log_20120911 v1x-access_log_20120826 v1x-access_log_20120912

service.stock.test.wxxr.cn-access_log_20120912 v1x-access_log_20120827 v1x-access_log_20120913

service.stock.test.wxxr.cn-access_log_20120913 v1x-access_log_20120828 v1x-access_log_20120914

service.stock.test.wxxr.cn-access_log_20120914 v1x-access_log_20120829 v1x-access_log_20120915

service.stock.test.wxxr.cn-access_log_20120915 v1x-access_log_20120830 v1x-access_log_20120916

service.stock.test.wxxr.cn-access_log_20120916 v1x-access_log_20120831 -access_log_20120911

stock.test.wxxr.cn-access_log_20120911 v1x-access_log_20120901 -access_log_20120912

stock.test.wxxr.cn-access_log_20120912 v1x-access_log_20120902 -access_log_20120913

stock.test.wxxr.cn-access_log_20120913 v1x-access_log_20120903 -access_log_20120914

stock.test.wxxr.cn-access_log_20120914 v1x-access_log_20120904 -access_log_20120915

stock.test.wxxr.cn-access_log_20120915 v1x-access_log_20120905 -access_log_20120916

[oracle@searchdb2 201209]$more m.xrcj.wxxr.cn-access_log_20120911

124.193.121.146 - - [11/Sep/2012:16:03:18 +0800] "GET / HTTP/1.1" 200 2041

124.193.121.146 - - [11/Sep/2012:16:03:18 +0800] "GET /favicon.ico HTTP/1.1" 404 952

124.193.121.146 - - [11/Sep/2012:16:03:19 +0800] "GET / HTTP/1.1" 200 885

124.193.121.146 - - [11/Sep/2012:16:03:19 +0800] "GET /magnoliaPublic/dms/templating-kit/themes/finance/img/a1.png HTTP/1.1"

200 46335

124.193.121.146 - - [11/Sep/2012:16:03:19 +0800] "GET /magnoliaPublic/dms/templating-kit/themes/finance/img/a2.png HTTP/1.1"

200 3359

124.193.121.146 - - [11/Sep/2012:16:03:19 +0800] "GET /magnoliaPublic/dms/templating-kit/themes/finance/img/a3.png HTTP/1.1"

200 3748

124.193.121.146 - - [11/Sep/2012:16:03:19 +0800] "GET /magnoliaPublic/dms/templating-kit/themes/finance/img/a4.png HTTP/1.1"

200 58813

124.193.121.146 - - [11/Sep/2012:16:03:20 +0800] "GET /magnoliaPublic/dms/templating-kit/themes/finance/img/android.png HTTP/

1.1" 200 2505

124.193.121.146 - - [11/Sep/2012:16:03:20 +0800] "GET /magnoliaPublic/dms/templating-kit/themes/finance/img/ios.png HTTP/1.1"

200 4551

資料庫表結構：

SQL> desc log_file

Name Null? Type

----------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------

SITE VARCHAR2(2000)

IP VARCHAR2(15)

ACCESS_TIME DATE

URL VARCHAR2(2000)

CODE NUMBER(38)

LENGTH VARCHAR2(2000)

--------------------------------- insert_log.ctl --------------------------------

load data

append into table log_file

fields terminated by WHITESPACE TRAILING NULLCOLS

( SITE, IP, ACCESS_TIME DATE "dd/mm/yyyy:hh24:mi:ss", URL, CODE,LENGTH)

【IP:$1 access_time:$4 url:$7 code:$9 length:$10】

-----------------------------------insert_log.sh--------------------------------

#!/bin/bash

#FIlE_NAME:insert_log.sh

parent_dir=/logbackup/apache

month_dir=`date -d '1 day ago' +%Y%m`

log_dir=${parent_dir}/${month_dir}

yesterday=`date -d '1 day ago' +%Y%m%d`

cur_path=`pwd`

#echo $log_dir

cd $log_dir

list=`ls | grep $yesterday`

k=1

for i in $list ; do

echo --------------------------

echo $k:$i

echo --------------------------

site=`echo $i | awk -F"-" '{print $1}'`

cat $i | awk -v v_out=$site '{print v_out "\t" $1 "\t" $4 "\t" $7 "\t" $9 "\t" $10}' | sed 's/\[//g' > /tmp/log_dir

/${i}.dat

#sqlldr

echo "sqlldr userid=warehouse/oracle control=$cur_path/insert_log.ctl data="/tmp/log_dir/$i.dat" log=/tmp/log_dir/${i

}.log bad=/tmp/log_dir/${i}.bad"

sqlldr userid=warehouse/oracle control=$cur_path/insert_log.ctl data="/tmp/log_dir/$i\.dat" log=/tmp/log_dir/${i}.log

bad=/tmp/log_dir/${i}.bad > /dev/null

let k++

rm -rf /tmp/log_dir/*

done

apache日誌內容匯入資料庫

相關文章