CloudFrontのログをAthenaで検索

Amazon Athena とは

S3内のデータをSQLを使用して分析できるサービス。

CloudFront ログのテーブルの作成

以下のユーザーガイドに従って進める。

Amazon CloudFront ログのクエリ
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/cloudfront-logs.html

以下の DDL ステートメントを Athena コンソールのクエリエディタに貼り付け、38行目のLOCATION をログを保存する S3バケットに変更し、実行します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE EXTERNAL TABLE IF NOT EXISTS default.cloudfront_logs (
`date` DATE,
time STRING,
location STRING,
bytes BIGINT,
request_ip STRING,
method STRING,
host STRING,
uri STRING,
status INT,
referrer STRING,
user_agent STRING,
query_string STRING,
cookie STRING,
result_type STRING,
request_id STRING,
host_header STRING,
request_protocol STRING,
request_bytes BIGINT,
time_taken FLOAT,
xforwarded_for STRING,
ssl_protocol STRING,
ssl_cipher STRING,
response_result_type STRING,
http_version STRING,
fle_status STRING,
fle_encrypted_fields INT,
c_port INT,
time_to_first_byte FLOAT,
x_edge_detailed_result_type STRING,
sc_content_type STRING,
sc_content_len BIGINT,
sc_range_start BIGINT,
sc_range_end BIGINT
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://CloudFront_bucket_name/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )

cloudfront_logs テーブルが作成されます。
[Edit Settings] (設定を編集) をクリックし、Amazon S3 内のクエリ結果の保存場所をセットアップ。私はs3://CloudFront_bucket_name/athena にしました。

CloudFront ログのクエリ例

date 列名は予約語であるため、二重引用符で囲みます。

1
2
3
4
5
6
7
SELECT
    DISTINCT * 
FROM
    cloudfront_logs 
WHERE
    "date" BETWEEN DATE '2018-06-09' AND DATE '2018-06-11'
LIMIT 10;

カラムが日付と時刻で分かれており、かつUTCのため日本時間に修正して出力します。
長い。もっといいやり方あるかもしれない。

1
2
3
4
5
6
SELECT
    date_format(at_timezone(Cast(concat(date_format("date", '%Y-%m-%d'), ' ', "time") as timestamp), 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') as "datetime",
    "request_ip", "method", "uri", "result_Type", "query_string", "user_agent"
FROM
    cloudfront_logs
LIMIT 10;