Amazon Athena とは

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

CloudFront ログのテーブルの作成

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

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

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

   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' )
   

2. cloudfront_logs テーブルが作成される。

3. [Edit Settings] (設定を編集) をクリックし、Amazon S3 内のクエリ結果の保存場所をセットアップ。私はs3://CloudFront_bucket_name/athena にした。

CloudFront ログのクエリ例

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

SELECT
    DISTINCT * 
FROM
    cloudfront_logs 
WHERE
    "date" BETWEEN DATE '2018-06-09' AND DATE '2018-06-11'
LIMIT 10;

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

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;