RedshiftでCloudFrontのアクセスログを解析してみた

はじめに

AWSチームのすずきです。

CloudFrontがS3に出力したアクセスログをAmazon Redshiftにインポートし、解析を試みる機会がありました。

CloudFrontのログインポート手順と、SQLと可視化ツールを利用した解析について、 先日のELBのアクセスログ解析に引き続き、 紹介させて頂きます。

概要

• CloudFrontで出力されたログ、Redshiftにインポートし解析を実施しました。

• CloudFront標準のログ出力先（S3）、ログの蓄積期間が長くなるとアクセス性が低下しがちですが、Lambdaを利用して移動を実施しています。（近日別途紹介予定）

環境情報

Amazon Redshift

• バージニア（us-east-1)リージョン、ds1.large×1台構成のクラスタを利用しました。

Redshift操作

仮テーブル設置

• CloudFrontのアクセスログ仕様に準じ、テンポラリテーブルを設置します

CREATE TEMP TABLE tmp_cf_accesslog
(
request_date VARCHAR (16)
,request_time VARCHAR (16)
,x_edge_location VARCHAR (16)
,sc_bytes INT
,c_ip VARCHAR (32)
,cs_method VARCHAR (32)
,cs_host VARCHAR (256)
,cs_uri_stem VARCHAR (256)
,sc_status VARCHAR (8)
,cs_referrer VARCHAR (256)
,cs_useragent VARCHAR (256)
,cs_uri_query VARCHAR (256)
,cs_cookie VARCHAR (256)
,x_edge_result_type VARCHAR (16)
,x_edge_request_id VARCHAR (32)
,x_host_header VARCHAR (128)
,cs_protocol VARCHAR (8)
,cs_bytes INT
,time_taken FLOAT4
,x_forwarded_for VARCHAR (256)
,ssl_protocol VARCHAR (8)
,ssl_cipher VARCHAR (32)
,x_edge_response_result_type VARCHAR (32)
)
;

• CloudFrontの最新のアクセスログ仕様、下記公式ページで確認可能です。
• AWS Documentation » Amazon CloudFront » 開発者ガイド » アクセスログ

インポート（COPY）

• CloudFrontログ保存先のS3と認証情報を指定し、インポートを実施します

COPY tmp_cf_accesslog
FROM 's3://<s3パス>/'
CREDENTIALS '<aws認証情報>'
DELIMITER '\t'
IGNOREHEADER 2 TRUNCATECOLUMNS TRIMBLANKS ACCEPTINVCHARS MAXERROR AS 1000 gzip
REGION 'ap-northeast-1'

• CREDENTIALSは事前に用意したロール、またはアクセスキーを利用します。
• CREDENTIALS 'aws_iam_role=arn:aws:iam::nnnnn:role/redshift-role-mmmmm'
• CREDENTIALS 'aws_access_key_id=aaaaa;aws_secret_access_key=bbbbb'

• S3とRedshiftのリージョンが異なる場合ため、「REGION」オプションでS3のリージョンを指定しています

• S3上の対象ログの総容量は150MB（gz圧縮を展開すると約1GB）、インポートの所要時間は2分強でした。

テーブル作成

• 解析、及び分散キーとして利用するため、時刻情報（request_timestamp）のカラムを追加します
• ENCODEの種類は「analyze compression」を実施して求めた推奨値としています

CREATE TABLE cf_accesslog
(
request_timestamp timestamp DISTKEY
,request_date VARCHAR (16) ENCODE lzo
,request_time VARCHAR (16) ENCODE lzo
,x_edge_location VARCHAR (16) ENCODE bytedict
,sc_bytes INT ENCODE lzo
,c_ip VARCHAR (32) ENCODE lzo
,cs_method VARCHAR (32) ENCODE lzo
,cs_host VARCHAR (256) ENCODE lzo
,cs_uri_stem VARCHAR (256) ENCODE lzo
,sc_status VARCHAR (8) ENCODE lzo
,cs_referrer VARCHAR (256) ENCODE lzo
,cs_useragent VARCHAR (256) ENCODE lzo
,cs_uri_query VARCHAR (256) ENCODE lzo
,cs_cookie VARCHAR (256) ENCODE lzo
,x_edge_result_type VARCHAR (16) ENCODE bytedict
,x_edge_request_id VARCHAR (32) ENCODE bytedict
,x_host_header VARCHAR (128) ENCODE lzo
,cs_protocol VARCHAR (8) ENCODE lzo
,cs_bytes INT ENCODE delta32k
,time_taken FLOAT4 ENCODE bytedict
,x_forwarded_for VARCHAR (256)
,ssl_protocol VARCHAR (8) ENCODE lzo
,ssl_cipher VARCHAR (32) ENCODE lzo
,x_edge_response_result_type VARCHAR (32) ENCODE bytedict
)
;

データ投入

• 日付カラムと時刻カラムを結合した日時カラムを追加し、Insertを行います。

INSERT into cf_accesslog
SELECT
(request_date || ' ' || request_time )::TIMESTAMP as request_timestamp
, *
FROM tmp_cf_accesslog
;

解析例

• Amazon Redshiftにインポートしたログの解析は、OSSの可視化ツール「re:dash」環境がありましたので、そちらを利用してみました

人気ページ分析

• リファー数のベスト5を日別抽出

SELECT *
FROM
(SELECT * ,
row_number() OVER (PARTITION BY request_date ORDER BY cnt DESC) AS RANK
FROM
(SELECT cs_referrer,
request_date,
count(1) AS cnt
FROM cf_accesslog
WHERE char_length(cs_referrer) > 30
GROUP BY cs_referrer,request_date
HAVING count(1) > 100)) a
WHERE a.RANK <= 5

キャッシュヒット状況分析

• CloudFrontのキャッシュステータス別の件数を抽出

select x_edge_result_type, count(1) as cnt
from cf_accesslog
group by x_edge_result_type

アクセス元IP分析

• アクセス元のソースIP、日別にベスト5を抽出

SELECT *
FROM
(SELECT * ,
row_number() OVER (PARTITION BY request_date
ORDER BY cnt DESC) AS RANK
FROM
(SELECT c_ip,
request_date,
count(1) AS cnt
FROM cf_accesslog
GROUP BY c_ip, request_date
HAVING count(1) > 100)
) a
WHERE a.RANK <= 5
ORDER BY request_date ,rank

まとめ

CloudFrontがS3に出力するアクセスログ、Amazon Redshiftへのインポートは簡単に実施する事が可能でした。

Amazon Redshiftを利用する事で、ログ解析、使い慣れたSQLで実施する事が可能になります。 特にRedshiftで利用できるウィンドウ関数などが、 効果的に利用できる局面は多いかと思われます。

また、カラムナ型データベースのRedshiftは、データを圧縮状態で効率良く保管することが可能です。 今回の検証では未圧縮状態で1GBほどのログを取り込んでいますが、Redshift上のストレージ消費は250MBほど、 最小クラスタ(dc1.large:160GBの)でも、数年〜10年程度のログが保管できる計算となり、 長期間のログを検索可能な状態で保管できる利点も大きいと思われます。

近々のアクセス傾向の確認や、全文検索エンジンの特性を活かした解析には、 ElastisearchとKibanaを用いた可視化、解析も有効と考えられますが、 対象期間、複雑な集計処理が必要、複数のログ要素を組み合わせた解析などでは、 RedshiftとTableauなどのBIツールが生きる場面も多いと思われます。 求められる要件や規模などに応じ、適切なサービスを使い分けてご利用ください。

参考リンク

Elastisearch 利用例

• CloudFrontのアクセスログをKibanaで可視化する

re:dash紹介

• re:dashを使ってRDSのクエリを可視化してみた