Oracle 使用外部表分析网站访问日志

原创 eygle 2019-12-06

1027

使用外部表可以很容易地实现小网站的访问日志分析。虽然使用其他工具也可以实现相关功能，但是使用Oracle来分析对于数据库技术爱好者可以更加得心应手。

先来看一下具体分析的过程：

首先创建路径指向日志存放目录：

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create or replace directory exdata 
  2  as ' d:\oracle\exdata\';

Directory created.

然后将这个路径的访问权限授予eygle用户来进行具体操作：

SQL> grant read,write on directory exdata to eygle;
Grant succeeded.

选择合适的分隔符创建外部表，对于网站的访问文件，可以选择双引号作为分隔符，创建如下外部表：

create table eygle_access_log
(  ip_address_date         varchar2(100),
   acc_file                varchar2(4000),
   acc_cdsz                varchar2(20),
   acc_url                 varchar2(4000),
   left_blank              varchar2(10),
   acc_agent               varchar2(4000))
   organization external (
  type oracle_loader
  default directory EXDATA
  access parameters (
    RECORDS DELIMITED BY X'0A'  
    nobadfile
    nodiscardfile
    nologfile
    fields terminated by '"'
    missing field values are null
  )
  location('access_log.2010-09-29')
)  reject limit unlimited
/

注意这里的一个重要选项：

RECORDS DELIMITED BY X'0A'

如果是在Linux/Unix平台分析Linux/Unix平台下的日志文件，可以使用：

records delimited by newline

但是将Linux/Unix平台平台的文件转移到Windows上分析，就必须使用X’0A’的换行设置，这是因为Linux/Unix和Windows的换行符号不同。如果不做特殊设置，在查询外部表时可能会遇到如下错误：

SQL> select * from eygle_access_log;
select * from eygle_access_log
*
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEFETCH 调出时出错
ORA-29400: 数据插件错误KUP-04020: found record longer than buffer size
supported, 524288, in d:\oracle\exdata\access_log.2010-09-29

创建完成外部表之后，就可以对eygle.com的2010年09月29日的访问日志进行分析了。

可以先看一下各个字段的分界结果，示例如下：

SQL> select ip_address_date from eygle_access_log where rownum <11;

IP_ADDRESS_DATE
---------------------------------------------------------------------
116.53.250.171 - - [29/Sep/2010:00:34:34 -0700]
116.53.250.171 - - [29/Sep/2010:00:34:34 -0700]
116.53.250.171 - - [29/Sep/2010:00:34:34 -0700]
203.209.252.14 - - [29/Sep/2010:00:34:34 -0700]
123.160.166.70 - - [29/Sep/2010:00:34:34 -0700]
124.234.189.103 - - [29/Sep/2010:00:34:34 -0700]
115.238.38.114 - - [29/Sep/2010:00:34:34 -0700]
123.160.166.70 - - [29/Sep/2010:00:34:35 -0700]
123.160.166.70 - - [29/Sep/2010:00:34:35 -0700]
123.160.166.70 - - [29/Sep/2010:00:34:35 -0700]

通过SQL析取出访问的IP地址：

SQL> select substr(ip_address_date, 1, instr(ip_address_date, ' ')) ip_address
  2    from eygle_access_log
  3   where rownum < 11;

IP_ADDRESS
------------------------------------------------------------------------------
116.53.250.171
116.53.250.171
116.53.250.171
203.209.252.14
123.160.166.70
124.234.189.103
115.238.38.114
123.160.166.70
123.160.166.70
123.160.166.70

接下来就可以很容易地获得当日访问站点的独立IP数量了：

SQL> select count(distinct(substr(ip_address_date,
  2                               1,
  3                               instr(ip_address_date, ' ')))) uip
  4    from eygle_access_log;

       UIP
----------
      9745
已用时间:  00: 00: 00.68

因为外部表的处理性能上要差一些，我们记录了一下时间，以上查询大约用了68号秒的时间。

可以对比一下数据库表的性能，首先将日志加载到数据库表中：

SQL> create table ealog as
  2  select * from eygle_access_log;

表已创建。

已用时间:  00: 00: 10.62
SQL> select count(*) from ealog;

  COUNT(*)
----------
    129836

然后强制刷新Buffer Cache，消除Cache的影响，再次执行查询：

SQL> alter session set events = 'immediate trace name flush_cache';
Session altered.
SQL> select count(distinct(substr(ip_address_date,
  2                               1,
  3                               instr(ip_address_date, ' ')))) uip
  4    from ealog;

       UIP
----------
      9745

已用时间:  00: 00: 01.04
SQL> /

       UIP
----------
      9745

已用时间:  00: 00: 00.10

注意第一次查询用了大约1秒多的时间，第二次查询用了10毫秒。反复查询，Cache的作用体现出来，可以大幅度提升了查询性能，而外部表的性能也非常不错，只是反复查询都需要访问外部对象，无法利用Cache来达到性能提升。

接下来可以查询当日网站中，哪些网页是被最频繁访问的：

SQL> select replace((replace(acc_file,'GET ','http://www.eygle.com')),'HTTP/1.1') accfile,ct from (
  2  select ACC_FILE,count(*) ct from eygle_access_log_20061016
  3  where acc_file like '%htm%'
  4  group by acc_file order by ct desc)
  5  where rownum <12;

ACCFILE                                                                        CT
----------------------------------------------------------------------       -----
http://www.eygle.com/link.html                                                559
http://www.eygle.com/archives/2010/09/inside_adsl_ads.html                    388
http://www.eygle.com/archives/2010/09/farewell_msn_space.html                 359
http://www.eygle.com/archives/2010/09/backup_recovery_internal.html           202
http://www.eygle.com/archives/2007/11/whats_wmiprvse_exe.html                  98
http://www.eygle.com/archives/2008/12/windows_installer.html                   77
http://www.eygle.com/archives/2007/03/ntoskrnl_exe_corruption.html             76
http://www.eygle.com/index-sql.htm                                             64
http://www.eygle.com/index-tech.htm                                            61
http://www.eygle.com/digest/2008/04/beijing_cmbchina.html                      60
http://www.eygle.com/archives/2010/09/tailand_julia.html                       57

已用时间:  00: 00: 00.89

通过外部表及SQL查询，只要日志文件中存在的信息，都可以很容易的被获取和分析。

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 使用外部表分析网站访问日志

评论