S3のアクセスログをRedshiftにロードする
AWSのログで遊ぼうシリーズ第2弾 – S3 x Redshift。
今回はS3のアクセスログを対象に、Redshiftにロードしてみます。
前提知識
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
mooapp 
トラックバックはまだありません。