Apache アクセスログをSQLで読み込む【Oracle編】

「Apache アクセスログをSQLで読み込む【SQL Server編】」では、SQL Server で、Apache のアクセスログ（CLF形式）を直接読み込んでみましたが、同じことを Oracle でやってみます。利用するバージョンは Oracle 19c で、読み込むアクセスログは関連記事と同じものを利用します。

インライン外部表について

Oracleでファイルを直接読み込む機能として「外部表」があります。詳細は「ORACLE_LOADERアクセス・ドライバ」に記載がありますが、ファイルをデータベースにローディングする標準ユーティリティである「SQL*Loader」が内部的に利用している機能です。この機能は実際に CREATE TABLE しないと使えませんでしたが、 Oracle 18c より「インライン外部表」という機能が追加され、SQL の一部として定義できるようになっています。

ディレクトリオブジェクト作成

Oracle から外部にあるファイルにアクセスするためには「ディレクトリ・オブジェクト」が必要です。要はデータベースがアクセスできるディレクトリを明示的に定義してあげるわけです。関連記事と同じアクセスログを利用するので、ログを配置しているフォルダ"c:\logs"をディレクトリ・オブジェクトとして定義します。

CREATE DIRECTORY LOGDIR AS 'C:\logs';

次にこのフォルダを利用するユーザに対してアクセス権を与えます。

GRANT READ ON DIRECTORY LOGDIR TO scott;

この例では読み込み権限（READ）のみを与えていますが、読み込み時のログファイル（LOGFILE）や、取り込めなかったレコードを出力するファイル（BADFILE）を定義する場合には、書き込み権限（WRITE）も必要です。ちなみに権限がないとディレクトリオブジェクトそのものが参照できないので以下のようなエラーになります。

ORA-06564: オブジェクトLOGDIRは存在しません。

ログファイル等を出力している場合に書き込み権限がないと以下のようなエラーになります。デフォルトではログファイルは出力されるので注意してください。

ORA-29913: ODCIEXTTABLEOPENコールアウトの実行中にエラーが発生しました。
ORA-29400: データ・カートリッジ・エラーが発生しました
KUP-04074: ディレクトリ・オブジェクトLOGDIRへの書込みアクセスがありません

インライン外部表による読み込み

では、インライン外部表の機能を使って読み込んでみます。

SELECT IP
     , IDNT
     , RUSER
     , REPLACE(DT1,'[','')||' '||REPLACE(DT2,']','') AS DT
     , URL
     , STATUS
     , CSIZE
     , REFERER
     , USERAGENT 
  FROM EXTERNAL (   
    (
      IP        VARCHAR2(4000),
      IDNT      VARCHAR2(4000),
      RUSER     VARCHAR2(4000),
      DT1       VARCHAR2(4000),
      DT2       VARCHAR2(4000),
      URL       VARCHAR2(4000),
      STATUS    VARCHAR2(4000),
      CSIZE     NUMBER,
      REFERER   VARCHAR2(4000),
      USERAGENT VARCHAR2(4000)
    )     
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY LOGDIR
    ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      NOLOGFILE
      NOBADFILE
      FIELDS TERMINATED BY WHITESPACE
      OPTIONALLY ENCLOSED BY '"'
    )
   LOCATION ('access_log') REJECT LIMIT UNLIMITED) T;

ORACLE_LOADER による読み込みの場合、SQL Server における OPENROWSET とは異なり、区切り記号（TERMINATED BY）とは別に囲み記号（ENCLOSED BY）が定義できます。したがって普通の CSV ファイルであれば非常に簡単に取り込めると思います。

ただ、今回取り込もうとしたApache のアクセスログ（CLF形式）の場合は少し工夫が必要です。区切り記号は空白（WHITESPACE）で囲み文字はダブルクォーテーション(“)であるにもかかわらず、日付時刻のところだけが"[“～"]"で囲まれている上に時刻の後ろに空白があるためです。
対応方法としては、ちょっとせこいですが日付時刻のところだけ取込後に結合しています。

実行結果

SQL Server のときと同様にファイルを分割して取り込めました。

データベースOracle,SQL

Posted by takmaru