試してもたのでメモ。

身近なビックデータ、IISのログ。数GBならRDBでも何とかなりますが、数百GB~TBとなるとRDBだとしんどいです。個人的にはBigQeryに突っ込めば？と思うのですが、Hadoopで処理してみたいと思います。

Hadoopであればなんでもいいのですが、折角なのでHDInsight(Microsoft AzureのHadoop)で処理してみたいと思います。ただ、ここで紹介する手順の殆どは素のHadoopやAmazon EMRで利用できます。

なお、ツールとしてはHiveを使います。

IISのログ形式

いくつかの形式が存在しますが、ここではデフォルトのW3Cかつ、カスタム無しを処理してみたいと思います。
デフォルト状態でIISのログは、

W3C形式
u_exYYMMDD.logというファイル名で日別出力（例 u_ex141112.log）

となっています。

取得項目は、IISの管理画面で確認するか、各ログファイルヘッダ部を見ればわかります。
今回のログのヘッダ？は、

# 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

という感じです。
項目名と順序がありますので、それを参考にします。各項目の意味は、IISのサイトなどで確認できます。

今回はデフォルトのままで利用します。縦に書くと、

# 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

という感じです。

ログの保存場所とディレクトリ/ファイル構成

保存場所（BLOB）

まず、HDInsightではAzure StorageのBLOBにファイルを置くことが一般的です。データノード内のHDFSを利用することもできますが、クラスタを削除するとデータも一緒に消えるので、BLOBに保存しておくのが無難です。

BLOBへのアップロードツールを利用すればいいです。GUIのツールもCUIのツールもあります。

私はMac環境なのでCyberduckを利用しています。WindowsであればAzCopyやAzure Storage Explorerなどがあります。

どうにかしてログをBLOBにアップロードして下さい。

保存構成（ディレクトリ構成）

Hadoop(Hive)は、原則、1テーブルに1ディレクトリをマップし、ディレクトリ以下に同じ構造のファイルがあることを前提にファイルを処理を行います。そのため、BLOB内にログ保存用の専用コンテナ/ディレクトリを作成し、ログを保存するようにします。多くの場合、iilogなどのコンテナを作成し、その中に保存しておけばいいでしょう。

パーティショニングもできますが、必要に応じて別途説明します。

なお、BLOBには、原則として階層構造は無く（ディレクトリは無く）、コンテナ以下にフラットにファイルが保存されます。仮想的に、logs/hoge.logなどとファイル名を付けることで、仮想的な階層構造を表現することは可能です。

今回は、iisというコンテナを作成し、その中に、logをいうフォルダを作り、それ以下にIISのログファイルを格納することにします（実際にはlog/u_exYYMMDD.logというファイル名を並べる）。

なお、ファイルのリネームが伴うのでコンテナ直下でも問題ありません。どうやらSQL Data Warehouseで読み込みに医療するPolybaseがコンテナ直下を認識できないようなので、仕方なくこうしています。なので、SDWを利用しない人はコンテナ直下でいいかと思います。

配置が完了したらHDInsightで利用してみます。

HDInsightの利用

利用環境

HDInsightにはWindowsベースのものと、Linuxベースのものがありますが、Linuxベースの方がSSHログインでコンソール制御できなるなど断然使いやすいのでLinuxベースのクラスタを利用します。

Linux版ではSparkが利用できないなどの制限があるようです。

また、冒頭にも書きましたが処理機能としては、Hiveを利用します。もちろん、MapReduceやStreamingを直接書くこともできますが、PowerBIやODBC経由での利用も想定し、Hiveにします。

HDInsightのHiveはエンジンがデフォルトでTezに設定されているようです。Tezは次世代のバッチ系エンジンで、素のMapReduceより20%~以上高速といわれているのでうれしいです。MRに戻すこともできます。

クラスタの構成

クラスタの構成はここでは割愛します。詳しくはこちらを参考にしてみてください。誰でも簡単にできます。

なお、ネットの速さ重視で東日本リージョンにしてみましたがリージョンに割り当てられているコア（ノード数）が少なく、あまりHadoopの意味ないじゃん！な感じでしたので、こなれた海外がいいかもしれません。

クラスタへのログイン

Linuxベースのクラスたの場合、

ssh <username>@<account>-ssh.azurehdinsight.net

でログインできます。usernameやaccoutはクラスタ構築時に設定したものです。また、管理画面からSSHのログイン先サーバ名を確認することができます（通常のサーバ名とは違うので注意：-sshがつく）。

HDInsightでの作業

BLOBにファイルがアップロードされ、HDInsightへのログインが完了したら、後はさらっと分析するだけです。が、本格的にログの分析を開始する前に、データの整形が必要です。

ここでは、ゴミ取り等の前作業をする暫定テーブルに一度データを取り込み、ゴミ除去やdatetimeの変換等処理を加えて最終的に分析用テーブルを作成することにします。

では、進めます。

データクレンジング（ゴミ掃除）

IISのログを開いて見ていただけると解りますが、アクセスログとは別に#から始まるヘッダー行が含まれています。この行を削除しないと、正しく分析ができません。

ですので、ゴミ掃除も含め、作業は以下のように進めます。

ログをひとまず暫定テーブル(iislogs_org)にロード
先頭カラム(date_utc)に#から始まる文字列を含む行を除き、分析テーブル(iislogs)テーブルに出力

JST変換問題

W3CフォーマットのdateとtimeはUTCです。また、dateとtimeは別々に保存されているため、1つのdatetimeにマージし、かつ、JSTに変換します。分析テーブル(iislogs)にインポートする際、ゴミ掃除に加え、その変換も行います。

dateとtimeをdatetime(timestamp)にマージ
JSTに変換

いろいろ大変です。

暫定テーブル(iislogs_org)作成

では、まず、素（ゴミ有り）のログ取り込み用の暫定テーブルを用意します。

ゴミデータは正しくカラムにロードされるわけではないのでスキーマの設定は適当でもいいのですが、一応ログの構造を意識したものにします。

ではhiveコンソールを起動し、下記HiveQLを実行します。

hiveの起動はhadoopユーザーでログイン後、hiveと打つだけです。また、データベースはdefaultを利用します(useしません）。

# ヘッダー情報入りログをとりあえず格納
CREATE EXTERNAL TABLE iislogs_org(
    date_utc string,
    time_utc string,
    s_ip string,
    cs_method string,
    cs_uri_stem string,
    cs_uri_query string,
    s_port string,
    cs_username string,
    c_ip string,
    cs_user_agent string,
    cs_referer string,
    sc_status string,
    sc_substatus string,
    sc_win32_status string,
    time_taken string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n'
LOCATION 'wasb://iis@<storage_account>.blob.core.windows.net/log/';

iisの部分はコンテナ名、の部分はAzure Storageサービスのアカウントです。適宜読み替えて下さい。また、Amazon EMRとかではs3//~とすればよいだけです。

正しく取り込まれたか、あるいはゴミ業、有用行がどのように取り込まれているか、

SELECT * FROM iislogs_org LMIT 10;

などとして確かめて見て下さい。

#ではじまるゴミ行はデリミタが無いため全て1つめのdate_utcカラムに挿入されます。

分析用のテーブル(iislogs)を作成

では、分析に使うテーブルを作ります。
ここでは、ひとまず空テーブルとして作成し、必要な変換処理などをINSERT処理に記述することで、最終的に目的とするデータを得ることにします。

分析テーブルはほとんど暫定テーブルと同じですが、2つの変更点があります。

datetime_jstカラムの追加
デリミタを' 'スペースから','に変更

datetime形式のJSTを格納するためのdatetime_jstカラムを追加します。datetime_jstの型はdatetime型にしたいところですが、HDInsighのHiveは少し古く(0.14)、datetimeが使えないので、timestamp型で我慢します。

HDI3.3を選択することで、Hive1.2.1が利用できるようです。詳細はここを。

また、IISのログは項目がスペースで区切られていますが、それでは、Timestampのdateとtimeの間(2015-12-11 23:44:55の11と23の間)が区切りとして認識されてしまうので、デリミタを','に変更します。

デリミタが','で都合が悪ければ他のデリミタもありますし、ESCAPED BY '"'等を設定することで""で囲ったりもできます。

以下が、CREATE TABLE文です。

# ゴミ掃除後のログを入れるテーブルを作成
CREATE EXTERNAL TABLE iislogs(
    date_utc string,
    time_utc string,
    datetime_jst timestamp, #追加
    s_ip string,
    cs_method string,
    cs_uri_stem string,
    cs_uri_query string,
    s_port string,
    cs_username string,
    c_ip string,
    cs_user_agent string,
    cs_referer string,
    sc_status string,
    sc_substatus string,
    sc_win32_status string,
    time_taken string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
LOCATION 'wasb://iis@<storage_account>.blob.core.windows.net/clean/';

データの取り込み

テーブルが作成が完了したら暫定テーブル(iislogs_org)から、データを取り込みます。
取り込みは、INSERT OVERWRITE TABLE SELECT ~文を利用します。

INSERT OVERWRITE TABLE iislogs
SELECT
    date_utc string,
    time_utc string,
    from_utc_timestamp(cast(CONCAT(date_utc,' ',time_utc) as timestamp),'JST'),
    s_ip string,
    cs_method string,
    cs_uri_stem string,
    cs_uri_query string,
    s_port string,
    cs_username string,
    c_ip string,
    cs_user_agent string,
    cs_referer string,
    sc_status string,
    sc_substatus string,
    sc_win32_status string,
    time_taken string
FROM iislogs_org WHERE NOT date_utc LIKE '#%';

datetime_jstとしたところは、CONCATでdateとtimeをマージし、かつ、timestampにcastした後、from_utc_timestampでJSTに変換しています。

from_utc_timestamp(cast(CONCAT(date_utc,' ',time_utc) as timestamp),'JST'),

あとは、WHERE句で、

WHERE NOT date_utc LIKE '#%';

としてあるので、date_utcカラムの先頭に#が入っている行は無視されます。
他に除きたい条件があれば、追記してももちろん問題ありません。

データの確認

無事読み込みが完了したら、以下のコマンド等でデータを確認してみましょう。

select date_utc,time_utc,datetime_jst from iislogs_jst limit 10;

うまく変換されているようです。

2014-11-12	08:00:25	2014-11-12 17:00:25
2014-11-12	08:00:31	2014-11-12 17:00:31
2014-11-12	08:01:16	2014-11-12 17:01:16
2014-11-12	08:04:39	2014-11-12 17:04:39
2014-11-12	08:04:43	2014-11-12 17:04:43
2014-11-12	08:09:21	2014-11-12 17:09:21
2014-11-12	08:19:08	2014-11-12 17:19:08
2014-11-12	08:19:10	2014-11-12 17:19:10
2014-11-12	08:27:37	2014-11-12 17:27:37
2014-11-12	08:27:41	2014-11-12 17:27:41

処理結果を利用する

ファイル出力

処理結果はBLOBに出力することができます。

# 処理結果をblobに出力
INSERT OVERWRITE DIRECTORY 'wasb://output@<storage_account>.blob.core.windows.net/result1/'
    SELECT c_ip,count(*) FROM iislogs GROUP BY c_ip;

ただ、区切り文字が^Aという特殊な文字で出力されるため、お手軽ですが変換等が面倒です。

ExcelからODBCで利用する

どうせ、最後にExcelに取り込み利用するなら、ODBC経由で最初からExcelに取り込む事もできます。
ODBCでHDInsightを利用する方法はこちらを参考にしてください。

fluentd等でBLOBやS3にログを転送し、そのまま分析というのもありですね。

SQL Data WarehouseとData Lakeでも試してみたいです。