CloudFrontのログクエリをやってみる(dateとtimeからJSTで検索する)

概要

• CloudFrontにおけるログをAthenaを使いクエリするといったことはよくあると思います。
• その際にdate, timeのカラムから日付を絞りクエリする際にUTC時間を気にしなければなりません。
• 今回は、そんな時に使うクエリをいくつか用意してみました。

AthenaにCloudFront テーブルを作成する

• 下記のDDLステートメントをコピーしてAthenaのコンソール(クエリエディタ)に貼り付け(Run Query)をします。
• ちなみに、下記のDDLは公式ドキュメントのものをそのまま引用しています。

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/CloudFront/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )

クエリしてみる

• 今回は２パターン用意しました。

①: 特定の日付(1日)のみに絞りクエリする

SELECT *
FROM cloudfront_logs
WHERE uri LIKE '%.mp4'
        AND ((date = CAST('2021-04-14' AS DATE)
        AND time >= '15:00:00')
        OR (date = CAST('2021-04-15' AS DATE)
        AND time < '15:00:00'));

②: 複数日付を指定し、その間にある条件でクエリする

SELECT *
FROM 
    (SELECT from_iso8601_timestamp(date_format(date,
         '%Y-%m-%dT') || time || 'Z') AT TIME ZONE 'Asia/Tokyo' jst_date, time, uri
    FROM cloudfront_logs
    WHERE "date"
        BETWEEN DATE '2021-02-01'
            AND DATE '2021-04-15'
    GROUP BY  date, time, uri)
WHERE "jst_date"
    BETWEEN DATE '2021-02-01'
        AND DATE '2021-04-15';