ELBのアクセスログをRedshiftにロードする

AWSのログで遊ぼうシリーズ第1弾 – ELB x Redshift。
今回はELBのアクセスログを対象に、Redshiftにロードしてみます。

elb2redshift


前提知識

ELBアクセスログの有効化

方法は下記のドキュメント参照。

ELBアクセスログの格納場所

ELBのアクセスログは、指定したS3の下記のパスに5分毎あるいは60分毎に(設定依存)出力されます。

{Bucket}/{Prefix}/AWSLogs/{AWS AccountID}/elasticloadbalancing/{Region}/{Year}/{Month}/{Day}/{AWS Account ID}_elasticloadbalancing_{Region}_{Load Balancer Name}_{End Time}_{Load Balancer IP}_{Random String}.log

出力されるログファイルは非圧縮です

ELBアクセスログのフォーマット

ELBのアクセスログは、スペース区切りで1行に1リクエストが記録されます。
各行には下記のフィールドが含まれます。(公式ドキュメントの抜粋です。)

フィールド名 説明
request_timestamp クライアントに応答が返された時間(UTC)。ISO 8601 形式が使用されます。
elb ロードバランサーの名前
client_port リクエストを送信したクライアントの IP アドレスとポート番号。
backend_port このリクエストを処理した登録済みインスタンスの IP アドレスとポート番号。
request_processing_time ロードバランサーがリクエストを受け取り、そのリクエストを登録済みインスタンスに送信した時点からの合計経過時間(秒単位)
backend_processing_time ロードバランサーが登録済みインスタンスにリクエストを送信し、そのインスタンスが応答ヘッダーの送信を開始した時点から合計経過時間(秒単位)。
response_processing_time ロードバランサーが登録済みインスタンスから応答ヘッダーを受け取り、クライアントへの応答の送信を開始した時点からの合計経過時間(秒単位)。この処理時間には、ロードバランサーでの待機時間と、ロードバランサーからバックエンドへの接続の取得時間の両方が含まれます。
elb_status_code ロードバランサー(HTTP のみ)からの応答のステータスコード
backend_status_code 登録済みインスタンス(HTTP のみ)からの応答のステータスコード
received_bytes クライアント(リクエスタ)から受け取ったリクエストのサイズ(バイト単位)。HTTP リクエストの場合、受信したバイト数はリクエストの本文を表し、ヘッダーを含みません。TCP の場合、バイト数はヘッダーを含みます。
sent_bytes クライアント(リクエスタ)に返される応答のサイズ(バイト単位)。HTTP レスポンスの場合、送信バイト数は応答の本文を表し、ヘッダーを含みません。TCP の場合、バイト数はヘッダーを含みます。
request クライアントからのリクエスト行は二重引用符で囲まれており、次の形式でログに記録されます。HTTP メソッド + プロトコル://ホストヘッダー:ポート + パス + HTTP バージョン。TCP リクエストの場合、URL が入力されない代わりに、スペースで区切られた 3 個のダッシュが引用符で囲まれて表示されます(例: “- – -“)。

HTTPのアクセスログはこんな感じです。

2014-03-04T02:20:21.212932Z my-elb 192.22.123.169:21029 172.16.93.0:80 0.000066 0.000651 0.000044 404 404 0 315 "GET http://example.com:80/index2.html HTTP/1.1"
2014-03-04T02:20:21.273241Z my-elb 192.22.127.225:28376 172.16.93.1:80 0.000047 0.000701 0.000032 200 200 0 1085 "GET http://example.com:80/index.html HTTP/1.1"
2014-03-04T02:20:21.444392Z my-elb 192.22.165.43:13503 172.16.93.1:80 0.000067 0.00067 0.000053 200 200 0 1085 "GET http://example.com:80/index.html HTTP/1.1"
2014-03-04T02:20:21.977025Z my-elb 192.22.121.162:30815 172.16.93.1:80 0.00007 0.000867 0.000069 200 200 0 1097 "GET http://example.com:80/sample.html HTTP/1.1"

TCPのアクセスログはこんな感じです。

2014-03-04T02:15:43.959433Z my-elb 192.22.81.240:24599 172.16.93.1:80 0.000497 0.000015 0.000017 - - 200 1362 "- - - "
2014-03-04T02:15:44.001637Z my-elb 192.22.152.221:27889 172.16.93.0:80 0.000574 0.000015 0.000017 - - 200 1362 "- - - "
2014-03-04T02:15:44.196433Z my-elb 192.22.165.251:49539 172.16.93.1:80 0.00053 0.000012 0.000017 - - 200 1362 "- - - "
2014-03-04T02:15:44.634838Z my-elb 192.22.32.224:57443 172.16.93.0:80 0.000587 0.000015 0.000017 - - 200 1362 "- - - "
2014-03-04T02:15:44.667070Z my-elb 192.22.68.165:33606 172.16.93.0:80 0.000506 0.000015 0.000017 - - 200 1350 "- - - "
2014-03-04T02:15:44.764904Z my-elb 192.22.250.2:32954 172.16.93.0:80 0.000483 0.000011 0.000018 - - 200 1350 "- - - "

データのロード

ELBアクセスログ用のテーブルの作成

あらかじめ、ELBのアクセスログを格納するためのテーブルをRedshift上に作成しておきます。
ELBのアクセスログのフォーマットを考えて、elb_access_logsテーブルを下記のように定義し、CREATE TABLE文を実行します。

CREATE TABLE elb_access_logs (
     request_timestamp DateTime encode lzo,
     elb varchar(30) encode lzo,
     client_port varchar(22) encode lzo,
     backend_port varchar(22) encode lzo,
     request_processing_time FLOAT encode bytedict,
     backend_processing_time FLOAT encode bytedict,
     response_prosessing_time FLOAT encode bytedict,
     elb_status_code varchar(3) encode lzo,
     backend_status_code varchar(3) encode lzo,
     received_bytes BIGINT encode lzo,
     sent_bytes BIGINT encode lzo,
     request varchar(MAX) encode lzo
)
sortkey(request_timestamp) ;

Redshift COPYコマンドの実行

COPYコマンドを実行して、S3上に蓄積されたELBのアクセスログをRedshiftにコピーします。

COPY
          elb_access_logs
FROM
          's3://hogehoge'
COMPUPDATE OFF
CREDENTIALS
         'aws_access_key_id=yourkey;aws_secret_access_key=yoursecretkey'
DELIMITER ' '
TRUNCATECOLUMNS
TRIMBLANKS
REMOVEQUOTES
TIMEFORMAT as 'auto'
ACCEPTINVCHARS
MAXERROR as 100000
  • COMPUPDATE OFF : 自動圧縮をOFFにします。
  • DELIMITER ‘ ‘ : ELBアクセスログの場合、区切り文字が’ ‘ですので、そのように設定します。(必須)
  • TRUNCATECOLUMNS : 既定のサイズを超えるデータを切り捨てます。
  • TRIMBLANKS : VARCHAR文字列から末尾の空白を削除します。
  • REMOVEQUOTES : 入力データの文字列を囲む引用符を削除します。区切り記号を含む引用符内のすべての文字は保持されます。つまり、DELIMITERで’ ‘を指定していても、引用符内の’ ‘は区切り文字と認識しません。(必須)
  • TIMEFORMAT as ‘auto’ : ELBアクセスログの場合、時刻が’2014-02-15T23:39:43.945958Z’のように出力され、Redshiftデフォルトのフォーマットである’YYYY-MM-DD HH:MI:SS’に合致しないため、時刻のフォーマットを自動認識させます。(必須)
  • ACCEPTINVCHARS : VARCHAR型のカラムが正しいUTF-8ではない文字をロードした場合でもロードを有効化します。
  • MAXERROR as 100000 : エラーを100000回検出した場合にロード失敗とします。

インポートが完了したら下記のクエリを実行して、意図した通りにログの各フィールドがテーブルの各列にマッピングされていることを確認しましょう。

SELECT * FROM elb_access_logs LIMIT 10;

動作確認

4つのパターンでクエリ実行してみた結果、下記のようになりました。

指定した期間のアクセスログをすべて取得する

SELECT *
FROM elb_access_logs
WHERE request_timestamp >= '2014-11-17 12:00:00' AND request_timestamp < '2014-11-17 13:00:00'

もっとも遅かった10リクエストを取得する

SELECT top 10 *
FROM elb_access_logs
ORDER BY backend_processing_time

elb_status_codeが200以外のリクエストの数を月次で集計する

SELECT to_char(request_timestamp, 'YYYY-MM') as month, count(*) as lines
FROM elb_access_logs
WHERE elb_status_code <> 200
GROUP BY to_char(request_timestamp, 'YYYY-MM')
ORDER BY to_char(request_timestamp, 'YYYY-MM');

結果例:

month lines
2014-09 414
2014-10 256
2014-11 383

request_processing_timeが1秒を超えるリクエストの合計数をELB毎に集計する

SELECT elb, count(*) as lines
FROM elb_access_logs
WHERE request_processing_time > 1
GROUP BY elb;

結果例:

elb lines
testA 1835
testB 932
testC 24

参考

  1. トラックバックはまだありません。

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中

%d人のブロガーが「いいね」をつけました。