Aug 17, 2026

Query AWS S3 access log

查詢 aws s3 的訪問日誌

[AWS chiehting]

347 Words CHANGE ME READ TIME1 Minute, 34 Seconds

2026-08-17 08:00 +0800

AWS S3 的 access log 配置到 s3://s3-access-logs 中，可以使用 Amazon Athena 做 SQL query。

如果是第一次使用 Amazon Athena，要先配置 Query result location 設定。進入到 Query settings > Query result encryption > Manage 進入到配置畫面，設定 Location of query result，例如 s3://s3-access-athena-results/。

建立資料庫

CREATE DATABASE s3_access_logs_db;

(option) 移除現有資料表

DROP TABLE istr_private;

建立資料表

CREATE EXTERNAL TABLE s3_access_logs_db.istr_private (
  BucketOwner String,
  Bucket String,
  RequestDateTime String,
  RemoteIP String,
  Requester String,
  RequestID String,
  Operation String,
  Key String,
  RequestURI String,
  HTTPstatus String,
  ErrorCode String,
  BytesSent String,
  ObjectSize String,
  TotalTime String,
  TurnAroundTime String,
  Referrer String,
  UserAgent String,
  VersionId String,
  HostId String,
  SigV String,
  CipherSuite String,
  AuthType String,
  Host String,
  TLSVersion String,
  AccessPointARN string,
  aclRequired string,
  SourceRegion string
)
PARTITIONED BY (
  year string,
  month string,
  day string
)
ROW FORMAT SERDE 
 'org.apache.hadoop.hive.serde2.RegexSerDe' 
WITH SERDEPROPERTIES ( 
 'input.regex'='([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$') 
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://s3-access-logs/157578228094/us-east-1/istr-private/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.year.type' = 'integer',
  'projection.year.range' = '2025,2030',
  'projection.month.type' = 'integer',
  'projection.month.range' = '1,12',
  'projection.month.digits' = '2',
  'projection.day.type' = 'integer',
  'projection.day.range' = '1,31',
  'projection.day.digits' = '2',
  'storage.location.template' = 's3://s3-access-logs/157578228094/us-east-1/istr-private/${year}/${month}/${day}/'
);

查詢資料

SELECT
  requestdatetime,
  remoteip,
  requester,
  operation,
  key,
  httpstatus,
  errorcode,
  UserAgent,
  Referrer
FROM s3_access_logs_db.istr_private
WHERE year = '2026' AND month = '08' AND day in ('12','13','14','15')
  AND key LIKE '%enNwediYXbRRaNhvgZ%'
  AND Requester != 'AmazonS3' 
ORDER BY requestdatetime DESC;

See Also