Athena

テーブルの作成

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
)
PARTITIONED BY(
         date string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION 's3://xxxxxx/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )

日付と日時を指定して検索

日本時間の2020-02-01の10時から12時まで

select * from "default"."cloudfront_logs" where date=cast('2020-02-01' as timestamp)
AND time BETWEEN '01:00:00' AND '03:00:00'

日付をcast()するのがポイント🐱

Athenaの課金

• 検索範囲のデータ量が1 TB あたり 5 USD
• 1TB以下は10MBとして計算
• 10MBは0.000005USD