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

Apache のアクセスログ（CLF形式）を、SQLServerの OPENROWSET を利用して、ファイルをデータベースにローディングすることなく、直接SQLで読み込んでみます。

ダミーログ生成

読み込むアクセスログは apache-loggen というダミーログを生成するツールで作りました。

apache-logen

これを c:\logs\access_log というファイル名で配置しておきます。

172.171.115.107 - - [02/Sep/2021:15:40:27 +0900] "GET /item/electronics/3055 HTTP/1.1" 200 89 "/category/electronics" "Mozilla/5.0 (Windows NT 6.0; rv:10.0.1) Gecko/20100101 Firefox/10.0.1"
172.117.78.196 - - [02/Sep/2021:15:40:27 +0900] "GET /category/software HTTP/1.1" 200 40 "/category/games?from=10" "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)"
20.93.218.134 - - [02/Sep/2021:15:40:27 +0900] "GET /category/books HTTP/1.1" 200 96 "/item/electronics/457" "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; YTB730; GTB7.2; EasyBits GO v1.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C)"
160.24.194.150 - - [02/Sep/2021:15:40:27 +0900] "GET /category/electronics HTTP/1.1" 200 123 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.7 (KHTML, like Gecko) Chrome/16.0.912.77 Safari/535.7"
72.153.81.37 - - [02/Sep/2021:15:40:27 +0900] "GET /category/books HTTP/1.1" 200 62 "-" "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; YTB730; GTB7.2; EasyBits GO v1.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C)"

フォーマットファイル作成

OPENROWSET でアクセスログを読み取るにあたり、そのフォーマットを定義しておく必要があります。XML形式と非XML形式があり、世の中のサンプルは非XML形式が多いので敢えてのXML形式でやってみたいと思います。レイアウトについての詳しい説明は以下を参照ください。
XML フォーマット ファイル (SQL Server)
以下のフォーマットファイルを c:\logs\access_log.fmt というファイル名で配置しておきます。

<?xml version="1.0"?>
<BCPFORMAT
  xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1"  xsi:type="CharTerm" TERMINATOR=" " />
  <FIELD ID="2"  xsi:type="CharTerm" TERMINATOR=" " />
  <FIELD ID="3"  xsi:type="CharTerm" TERMINATOR=" " />
  <FIELD ID="N1" xsi:type="CharTerm" TERMINATOR="[" />
  <FIELD ID="4"  xsi:type="CharTerm" TERMINATOR="]" />
  <FIELD ID="N2" xsi:type="CharTerm" TERMINATOR="&quat;" />
  <FIELD ID="5"  xsi:type="CharTerm" TERMINATOR="&quat;" />
  <FIELD ID="N3" xsi:type="CharTerm" TERMINATOR=" " />
  <FIELD ID="6"  xsi:type="CharTerm" TERMINATOR=" " />
  <FIELD ID="7"  xsi:type="CharTerm" TERMINATOR=" " />
  <FIELD ID="N4" xsi:type="CharTerm" TERMINATOR="&quat;" />
  <FIELD ID="8"  xsi:type="CharTerm" TERMINATOR="&quat;" />
  <FIELD ID="N5" xsi:type="CharTerm" TERMINATOR=" " />
  <FIELD ID="N6" xsi:type="CharTerm" TERMINATOR="&quat;" />
  <FIELD ID="9"  xsi:type="CharTerm" TERMINATOR="&quat;" />
  <FIELD ID="N7" xsi:type="CharTerm" TERMINATOR="\r\n" />
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="IP"        xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="2" NAME="IDNT"      xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="3" NAME="USER"      xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="4" NAME="DT"        xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="5" NAME="URL"       xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="6" NAME="STATUS"    xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="7" NAME="SIZE"      xsi:type="SQLNUMERIC"/>
  <COLUMN SOURCE="8" NAME="REFERER"   xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="9" NAME="USERAGENT" xsi:type="SQLCHAR"/>
 </ROW>
</BCPFORMAT>

いくつかコツがあるので説明します。
このフォーマットでは、CSV形式であるようなダブルクォーテーションで囲んだ文字列の定義（文字列の中に区切り記号が現れても無視する）に単純に対応できません。区切り文字は取り出すトークン単位で都度設定するので、こちらを利用してトークンを「切り捨てる」ことで必要な文字列のみを取り出します。例えば「AA,"BB,CC"」であれば、第一トークンはコンマ(,)まで、第2トークン、第3トークンはダブルクォーテーション(“)を終端として第2トークンは切り捨てる、とすることで「AA」と「BB,CC」を取り出すようにします。
尚、ダブルクォーテーションは、XML上は予約文字ですので、エスケープ文字（&quat;）で表現する必要がありますのでご注意ください。

OPENROWSET による読み込み

作成したフォーマットファイルを使ってアクセスログを読み込んでみます。

SELECT T.*
  FROM OPENROWSET( BULK N'C:\logs\access_log'
     , FORMATFILE=N'C:\logs\access_log.fmt' ) AS T;

実行結果は以下の通りです。きれいに分割して取得できました。

実行結果

実際に肥大化したアクセスログをローディングもなしに読み込みたい場面はあまりないと思いますが、一般ファイルを直接SQLで参照できると便利なことも多いでしょうから覚えておいて損はないと思います。

データベースSQL,SQLServer

Posted by takmaru