应用需求:
---------------------------------------------------------------
请将 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
www.xrcj.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 www.xrcj.wxxr.cn-access_log_20120911
stock.test.wxxr.cn-access_log_20120911 v1x-access_log_20120901 www.xrcj.wxxr.cn-access_log_20120912
stock.test.wxxr.cn-access_log_20120912 v1x-access_log_20120902 www.xrcj.wxxr.cn-access_log_20120913
stock.test.wxxr.cn-access_log_20120913 v1x-access_log_20120903 www.xrcj.wxxr.cn-access_log_20120914
stock.test.wxxr.cn-access_log_20120914 v1x-access_log_20120904 www.xrcj.wxxr.cn-access_log_20120915
stock.test.wxxr.cn-access_log_20120915 v1x-access_log_20120905 www.xrcj.wxxr.cn-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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-743841/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27042095/viewspace-743841/