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

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

cloudfront2redshift

前提知識

CloudFrontアクセスログの有効化

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

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

CloudFrontのアクセスログは指定したS3上の下記のパスに格納されます。

{bucket-name}.s3.amazonaws.com/{optional-prefix/}{distribution-ID}.{YYYY}-{MM}-{DD}-{HH}.{unique-ID}.gz

ELB/S3のアクセスログとは異なり、圧縮済み(.gz)で出力されますのでご注意ください。

詳細は下記のドキュメント参照。

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

CloudFrontのアクセスログは、タブ区切りで1行に1リクエストが記録されます。
ウェブディストリビューションの場合、各行には下記のフィールドが含まれます。（公式ドキュメントの抜粋です。）

フィールド名 説明
request_date イベントが発生した日付。yyyy-mm-dd 形式です（例: 2014-05-23）。日付と時刻は協定世界時（UTC）です。
request_time サーバーがリクエストの処理を完了した時刻（UTC）（例: 01:42:39）。
x_edge_location リクエストを処理したエッジロケーション。各エッジロケーションは、3 文字コードと、割り当てられた任意の数字で識別されます（例: DFW3）。
sc_bytes サーバーからクライアントへのバイト数（ヘッダーを含む。例: 1045619）。
remote_ip クライアントの IP（例: 192.0.2.183）。
cs_method HTTP アクセス方式。
cs_host DNS 名（リクエストに指定された CloudFront ディストリビューション名）。CNAME に対するリクエストを実行した場合、DNS 名フィールドには、CNAME ではなく、基礎となるディストリビューション DNS 名が含まれます。
cs_uri_stem URI ステム（例: /images/daily-ad.jpg）。
sc_status HTTP ステータスコード（例: 200） or 000 (CloudFront がリクエストに応答する前に、ビューアが接続をクローズしたことを示す)
cs_referer リファラー。
cs_user_agent ユーザーエージェント
cs_uri_query 接続文字列に含まれる URI のクエリ文字列部分。URI にクエリ文字列が含まれない場合、ログファイルにあるその要求の cs-uri-query フィールドには 1 つのハイフン（-）が含まれます。
cs_cookie 名前値のペアおよび関連属性を含む、リクエスト内の Cookie ヘッダー。
x_edge_result_type リクエストの結果タイプ。
x_edge_request_id 要求を一意に識別する暗号化された文字列。
x_host_header ビューアによってこのリクエストの Host ヘッダーに組み込まれた値。
cs_protocol ビューアによってリクエストに指定されたプロトコル。http または https。
cs_bytes ビューアによってリクエストに組み込まれたデータのバイト数（クライアントからサーバーへの送信時のバイト数。ヘッダーを含む）。
time_taken CloudFront エッジサーバーがビューアのリクエストを受け取った時間と、CloudFront がレスポンスの最終バイトをサーバーの出力キューに書き込んだ時間との差（秒数）をサーバー側で測定。

サンプルはこんな感じです。

#Version: 1.0
#Fields: date time x-edge-location sc-bytes c-ip cs-method cs(Host) cs-uri-stem sc-status cs(Referer) cs(User-Agent) cs-uri-query cs(Cookie) x-edge-result-type x-edge-request-id x-host-header cs-protocol cs-bytes time-taken 
2014-05-23 01:13:11 FRA2 182 192.0.2.10 GET d111111abcdef8.cloudfront.net /view/my/file.html 200 www.displaymyfiles.com Mozilla/4.0%20(compatible;%20MSIE%205.0b1;%20Mac_PowerPC) - zip=98101 RefreshHit MRVMF7KydIvxMWfJIglgwHQwZsbG2IhRJ07sn9AkKUFSHS9EXAMPLE== d111111abcdef8.cloudfront.net http - 0.001
2014-05-23 01:13:12 LAX1 2390282 192.0.2.202 GET d111111abcdef8.cloudfront.net /soundtrack/happy.mp3 304 www.unknownsingers.com Mozilla/4.0%20(compatible;%20MSIE%207.0;%20Windows%20NT%205.1) a=b&c=d zip=50158 Hit xGN7KWpVEmB9Dp7ctcVFQC4E-nrcOcEKS3QyAez--06dV7TEXAMPLE== d111111abcdef8.cloudfront.net http - 0.002

データのロード

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

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

CREATE TABLE cloudfront_access_logs (
    request_date VARCHAR(MAX) SORTKEY,
    request_time VARCHAR(MAX) ENCODE LZO,
    x_edge_location VARCHAR(40) ENCODE LZO,
    sc_bytes INT ENCODE LZO,
    remote_ip VARCHAR(50) ENCODE LZO,
    cs_method VARCHAR(50) ENCODE LZO,
    cs_host VARCHAR(MAX) ENCODE LZO,
    cs_uri_stem VARCHAR(MAX) DISTKEY ENCODE LZO,
    sc_status VARCHAR(20) ENCODE LZO,
    cs_referrer VARCHAR(MAX) ENCODE LZO,
    cs_useragent VARCHAR(MAX) ENCODE LZO,
    cs_uri_query VARCHAR(MAX) ENCODE LZO,
    cs_cookie VARCHAR(MAX) ENCODE LZO,
    x_edge_result_type VARCHAR(MAX) ENCODE LZO,
    x_edge_request_id VARCHAR(MAX) ENCODE LZO,
    x_host_header VARCHAR(MAX) ENCODE LZO,
    cs_protocol VARCHAR(10) ENCODE LZO,
    cs_bytes INT ENCODE LZO,
    time_taken VARCHAR(MAX) ENCODE LZO
);

Redshift COPYコマンドの実行

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

COPY
          cloudfront_access_logs
FROM
          's3://hogehoge'
COMPUPDATE OFF
CREDENTIALS
         'aws_access_key_id=yourkey;aws_secret_access_key=yoursecretkey'
DELIMITER '\t'
IGNOREHEADER 2
TRUNCATECOLUMNS
TRIMBLANKS
ACCEPTINVCHARS
MAXERROR as 100000
gzip
  • COMPUPDATE OFF : 自動圧縮をOFFにします。
  • DELIMITER ‘¥t’ : CloudFrontアクセスログの場合、区切り文字が’\t’ですので、そのように設定します。(必須)
  • IGNOREHEADER 2 : CloudFrontアクセスログの場合、最初の2行はヘッダですので無視するように設定します。(必須)
  • TRUNCATECOLUMNS : 既定のサイズを超えるデータを切り捨てます。
  • TRIMBLANKS : VARCHAR文字列から末尾の空白を削除します。
  • ACCEPTINVCHARS : VARCHAR型のカラムが正しいUTF-8ではない文字をロードした場合でもロードを有効化します。
  • MAXERROR as 100000 : エラーを100000回検出した場合にロード失敗とします。
  • gzip : gzip形式を解凍します。

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

SELECT * FROM cloudfront_access_logs LIMIT 10;

動作確認

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

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

SELECT *
FROM cloudfront_access_logs
WHERE to_date(request_date || ' ' || request_time,'YYYY-MM-DD HH:MI:SS')  >= '2014-11-17 12:00:00' AND to_date(request_date || ' ' || request_time,'YYYY-MM-DD HH:MI:SS') < '2014-11-17 13:00:00'

日ごとのログ件数を集計する

SELECT request_date, count(*) as lines
FROM cloudfront_access_logs
GROUP BY request_date
ORDER BY request_date

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

SELECT top 10 *
FROM cloudfront_access_logs
ORDER BY time_taken

sc_statusが200以外のリクエストの合計数をディストリビューション毎に集計する

SELECT cs_host, count(*) as lines
FROM cloudfront_access_logs
WHERE sc_status <> 200
GROUP BY cs_host

s

参考

2014
12/01
CATEGORY
AWS
CloudFront
Redshift
コメントを書く

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

コメントを残す