• ACL有効化が必要(S3バケットは本来、無効が推奨)

CloudFrontのログは、１ディレクトリに置かれますので、日付のフォルダーに移動するようにいします。

• LambdaでCloudFrontアクセスログを展開しログファイルの形式を変換してみた | DevelopersIO

CREATE EXTERNAL TABLE IF NOT EXISTS default.cloudfront_logs (
  `date` DATE,
  time STRING,
  location STRING,
  bytes BIGINT,
  requestip STRING,
  method STRING,
  host STRING,
  uri STRING,
  status INT,
  referrer STRING,
  useragent STRING,
  querystring STRING,
  cookie STRING,
  resulttype STRING,
  requestid STRING,
  hostheader STRING,
  requestprotocol STRING,
  requestbytes BIGINT,
  timetaken FLOAT,
  xforwardedfor STRING,
  sslprotocol STRING,
  sslcipher STRING,
  responseresulttype STRING,
  httpversion STRING,
  filestatus STRING,
  encryptedfields INT
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://test-logs/test1/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )

SELECT requestip, COUNT(*) AS num FROM cloudfront_logs WHERE uri LIKE '/contents/hoge/%' GROUP BY requestip ORDER BY requestip