AWSやJava、iPhone App、OpenFlowなど、様々なトピックを扱う技術系ブログです。

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

前提知識

CloudFrontアクセスログの有効化

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

• ログ作成設定の変更方法 – 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アクセスログのフォーマット

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

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

#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

参考

• アクセスログ – CloudFront開発者ガイド
• Analyzing S3 and CloudFront Access Logs with AWS RedShift