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

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

s32redshift


前提知識

S3アクセスログの有効化

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

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

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

フィールド名 説明
bucket_owner 配信元バケット所有者の正規ユーザー ID。
bucket リクエストの処理対象のバケットの名前。システムで受け取ったリクエストの形式に誤りがあり、バケットを特定できない場合、そのリクエストはサーバーアクセスログに表示されません。
request_timestamp リクエストを受け取った時刻。形式は strftime() の用語を使用し、[%d/%b/%Y:%H:%M:%S %z] になります。
remote_ip リクエスタの表面上のインターネットアドレス。中間プロキシやファイアウォールにより、リクエストを作成したマシンの実際のアドレスが不明確になる場合があります。
requester リクエスタの正規ユーザー ID。未認証リクエストの場合は「Anonymous」という文字列。この識別子は、アクセスコントロールに使用されるものと同じです。
request_id リクエスト ID は、各リクエストを一意に識別するために Amazon S3 によって生成される文字列です。
operation SOAP.operation、REST.HTTP_method.resource_type、または WEBSITE.HTTP_method.resource_type
key リクエストの URL エンコードされた「key」部分、オペレーションがキーパラメータを取らない場合は「-」。
request_uri HTTP リクエストメッセージの Request-URI の部分。
http_status_code レスポンスの HTTP ステータスの数値。
error_code Amazon S3 エラーコード。エラーがない場合は「-」。
sent_bytes 送信されたレスポンスのバイト数(HTTP プロトコルオーバーヘッドを除きます)。ゼロの場合は「-」。
object_size 該当するオブジェクトの合計サイズ。
total_time サーバーから見た、リクエストの転送中の時間数(ミリ秒単位)。これは、リクエストが受信されてから、レスポンスの最終バイトが送信されるまでの時間を計測した値です。クライアント側での計測値は、ネットワークレイテンシーにより長くなる場合があります。
turn_around_time Amazon S3 でリクエストの処理に要した時間数(ミリ秒単位)。これは、リクエストの最終バイトが受信されてから、レスポンスの先頭バイトが送信されるまでの時間を計測した値です。
referer HTTP Referrer ヘッダーの値(存在する場合)。一般に、HTTP ユーザーエージェント(例: ブラウザ)はこのヘッダーをリクエスト作成時のリンクまたは埋め込みページの URL に設定します。
user_agent HTTP User-Agent ヘッダーの値。
version_id リクエストのバージョン ID。オペレーションが versionId パラメータを取らない場合は「-」。

サンプルは下記。

79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:00:38 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be 3E57427F3EXAMPLE REST.GET.VERSIONING - "GET /mybucket?versioning HTTP/1.1" 200 - 113 - 7 - "-" "S3Console/0.4" -
79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:00:38 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be 891CE47D2EXAMPLE REST.GET.LOGGING_STATUS - "GET /mybucket?logging HTTP/1.1" 200 - 242 - 11 - "-" "S3Console/0.4" -
79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:00:38 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be A1206F460EXAMPLE REST.GET.BUCKETPOLICY - "GET /mybucket?policy HTTP/1.1" 404 NoSuchBucketPolicy 297 - 38 - "-" "S3Console/0.4" -
79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:01:00 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be 7B4A0FABBEXAMPLE REST.GET.VERSIONING - "GET /mybucket?versioning HTTP/1.1" 200 - 113 - 33 - "-" "S3Console/0.4" -
79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:01:57 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be DD6CC733AEXAMPLE REST.PUT.OBJECT s3-dg.pdf "PUT /mybucket/s3-dg.pdf HTTP/1.1" 200 - - 4406583 41754 28 "-" "S3Console/0.4" -
79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:03:21 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be BC3C074D0EXAMPLE REST.GET.VERSIONING - "GET /mybucket?versioning HTTP/1.1" 200 - 113 - 28 - "-" "S3Console/0.4" -

データのロード

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

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

CREATE TABLE s3_access_logs (
    bucket_owner VARCHAR(MAX) ENCODE RUNLENGTH,
    bucket VARCHAR(255) ENCODE LZO,
    request_timestamp VARCHAR(MAX) SORTKEY ENCODE LZO,
    request_timestamp_delta VARCHAR(MAX) ENCODE LZO,
    remote_ip VARCHAR(50) ENCODE LZO,
    requestor VARCHAR(MAX) ENCODE LZO,
    request_id VARCHAR(MAX) ENCODE LZO,
    operation VARCHAR(MAX) ENCODE LZO,
    key VARCHAR(MAX) ENCODE LZO,
    request_uri VARCHAR(MAX) DISTKEY ENCODE LZO,
    http_status_code VARCHAR(MAX) ENCODE LZO,
    error_code VARCHAR(MAX) ENCODE LZO,
    sent_bytes VARCHAR(MAX) ENCODE LZO,
    object_size VARCHAR(MAX) ENCODE LZO,
    total_time VARCHAR(MAX) ENCODE LZO,
    turn_around_time VARCHAR(MAX) ENCODE LZO,
    referer VARCHAR(MAX) ENCODE LZO,
    user_agent VARCHAR(MAX) ENCODE LZO,
    version_id VARCHAR(10) ENCODE LZO
);

Redshift COPYコマンドの実行

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

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

REMOVEQUOTESを指定すると、S3のアクセスログにおけるrequest_uriのように、ダブルクォートで囲まれた文字列に含まれるスペースを区切り文字とみなさず、ひとかたまりで扱うことができます。
例えば、

"GET /mybucket?versioning HTTP/1.1"

というrequest_uriの場合、REMOVEQUOTESを指定しないと「”GET」「/mybucket?versioning」「HTTP/1.1″」に分割されて認識されます。
一方、REMOVEQUOTESを指定すると、「GET /mybucket?versioning HTTP/1.1」として認識されます。
便利ですね。

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

SELECT * FROM s3_access_logs LIMIT 10;

動作確認

4つのパターンでクエリ実行してみました。

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

SELECT *
FROM s3_access_logs
WHERE to_date(btrim(request_timestamp,'['),'DD/MON/YYYY:HH:MI:SS')  >= '2014-11-17 12:00:00' AND to_date(btrim(request_timestamp,'['),'DD/MON/YYYY:HH:MI:SS') < '2014-11-17 13:00:00'

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

SELECT top 10 *
FROM s3_access_logs
ORDER BY turn_around_time

http_status_codeが200以外のリクエストの合計数をバケット毎に集計する

SELECT bucket, count(*) as lines
FROM s3_access_logs
WHERE http_status_code <> 200
GROUP BY bucket

エラーが記録された件数をUser Agent毎に集計する

SELECT user_agent, count(*) as lines
FROM s3_access_logs
WHERE error_code <> '-'
GROUP BY user_agent
ORDER BY lines
DESC

参考

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

コメントを残す

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

WordPress.com ロゴ

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

Twitter 画像

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

Facebook の写真

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

Google+ フォト

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

%s と連携中

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