OPENROWSET(BULK)を使ってIISログをSQLServerで読み取る

はじめに

SQLServerにCSVファイルを取り込む機会がありました。
CSVファイルを基に、UPDATEする必要があったので、OPENROWSET(BULK)を使ってみました。

使い勝手が良く、ログの調査にも使えそうな気がしたので、試しにIISログを読み込んでみました。

IISログをSQLServerで読み取る

u_ex240502.log

2024-05-02 13:29:20 ::1 GET /react-deploy - 80 - ::1 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/124.0.0.0+Safari/537.36+Edg/124.0.0.0 - 500 19 5 302
2024-05-02 13:29:20 ::1 GET /favicon.ico - 80 - ::1 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/124.0.0.0+Safari/537.36+Edg/124.0.0.0 http://localhost/react-deploy 404 0 2 77
2024-05-02 13:36:02 ::1 GET /react-deploy/index.html - 80 - ::1 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/124.0.0.0+Safari/537.36+Edg/124.0.0.0 - 401 3 5 3
2024-05-02 13:37:05 ::1 GET /react-deploy - 80 - ::1 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/124.0.0.0+Safari/537.36+Edg/124.0.0.0 - 301 0 0 3
…

IISLogFormat.xml

<?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="CharFixed" LENGTH="20"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="6" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="7" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="8" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="9" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="10" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="11" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="12" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="13" xsi:type="CharTerm" TERMINATOR=" "/>
    <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\r\n"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="date-time" xsi:type="SQLDATETIME" />
    <COLUMN SOURCE="2" NAME="s-ip" xsi:type="SQLNVARCHAR" />
    <COLUMN SOURCE="3" NAME="cs-method" xsi:type="SQLNVARCHAR" />
    <COLUMN SOURCE="4" NAME="cs-uri-stem" xsi:type="SQLNVARCHAR" />
    <COLUMN SOURCE="5" NAME="cs-uri-query" xsi:type="SQLNVARCHAR" />
    <COLUMN SOURCE="6" NAME="s-port" xsi:type="SQLINT" />
    <COLUMN SOURCE="7" NAME="cs-username" xsi:type="SQLNVARCHAR" />
    <COLUMN SOURCE="8" NAME="c-ip" xsi:type="SQLNVARCHAR" />
    <COLUMN SOURCE="9" NAME="cs(User-Agent)" xsi:type="SQLNVARCHAR" />
    <COLUMN SOURCE="10" NAME="cs(Referer)" xsi:type="SQLNVARCHAR" />
    <COLUMN SOURCE="11" NAME="sc-status" xsi:type="SQLINT" />
    <COLUMN SOURCE="12" NAME="sc-substatus" xsi:type="SQLINT" />
    <COLUMN SOURCE="13" NAME="sc-win32-status" xsi:type="SQLINT" />
    <COLUMN SOURCE="14" NAME="time-taken" xsi:type="SQLINT" />
  </ROW>
</BCPFORMAT>

実行クエリ

SELECT *
  FROM OPENROWSET
       (BULK 'C:\work\file\u_ex240502.log',
        FORMATFILE = 'C:\work\file\IISLogFormat.xml',
        FIRSTROW = 1
       ) AS t

実行結果

課題事項

実際にはIISのログファイルには「#」から始まるヘッダ行が含まれています。

#Software: Microsoft Internet Information Services 10.0
#Version: 1.0
#Date: 2024-05-02 13:29:20
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken

ヘッダの部分を読み飛ばす処理が上手くいかず、ログファイルから削除してから読み取りました。
「#から始まる行を読み飛ばす」みたいな設定が出来ないか調査中です。