如何在Athena中按天分区非文件夹结构的S3访问日志？

当然有简便方法！针对S3访问日志这种没有文件夹结构、文件名里包含日期的场景，Athena的**分区投影（Partition Projection）**绝对是最优解——不用手动编写脚本同步分区，也不用每天手动添加，配置好之后Athena会自动识别并按天分区。

核心思路 #

S3访问日志的文件名开头是yyyy-MM-dd-格式的日期（比如你示例里的2018-03-15-03-05-46-...），我们可以基于这个日期片段创建分区键，通过分区投影让Athena自动匹配对应日期的日志文件，实现按天过滤查询。

1. 创建带分区投影的外部表 #

直接用下面的DDL创建表，记得替换成你的实际桶名和日志存储前缀：

CREATE EXTERNAL TABLE IF NOT EXISTS s3_access_logs (
  bucket_owner STRING,
  bucket STRING,
  request_time STRING,
  remote_ip STRING,
  requester STRING,
  request_id STRING,
  operation STRING,
  key STRING,
  request_uri STRING,
  http_status STRING,
  error_code STRING,
  bytes_sent BIGINT,
  object_size BIGINT,
  total_time BIGINT,
  turn_around_time BIGINT,
  referrer STRING,
  user_agent STRING,
  version_id STRING,
  host_id STRING,
  signature_version STRING,
  cipher_suite STRING,
  authentication_type STRING,
  host_header STRING,
  tls_version STRING
)
PARTITIONED BY (date STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1',
  'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \"([^\"]*)\" ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \"([^\"]*)\" ([^ ]*)'
)
LOCATION 's3://BUCKET_NAME/ACCESS_LOGS_DEST/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.date.type' = 'date',
  'projection.date.range' = '2018-01-01,NOW', -- 替换成你的日志起始日期
  'projection.date.format' = 'yyyy-MM-dd',
  'projection.date.interval' = '1',
  'projection.date.interval.unit' = 'DAYS',
  'storage.location.template' = 's3://BUCKET_NAME/ACCESS_LOGS_DEST/${date}*'
);

2. 关键配置解释 #

• PARTITIONED BY (date STRING)：定义date为分区键，用字符串类型适配文件名里的日期格式。
• 分区投影配置（TBLPROPERTIES）：
  • projection.enabled = 'true'：开启分区投影功能。
  • projection.date.type = 'date'：指定分区类型为日期型。
  • projection.date.range：设置日志的起始日期和结束日期，NOW表示自动适配当前日期，新产生的日志会被自动识别。
  • projection.date.format = 'yyyy-MM-dd'：对应S3日志文件名开头的日期格式。
  • projection.date.interval和unit：设置分区间隔为1天，刚好满足按天分区的需求。
  • storage.location.template：告诉Athena每个分区对应的S3文件模式，${date}*会匹配所有以该日期开头的日志文件，完美适配S3访问日志的命名规则。

3. 查询分区日志 #

配置完成后，直接用分区键过滤即可高效查询某天的日志：

SELECT * FROM s3_access_logs WHERE date = '2018-03-15';

Athena会自动只扫描2018-03-15开头的日志文件，避免全表扫描，大幅提升查询速度。

4. 给现有表添加分区投影 #

如果你已经有了S3访问日志的表，可以通过ALTER TABLE命令添加分区投影配置：

ALTER TABLE s3_access_logs SET TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.date.type' = 'date',
  'projection.date.range' = '2018-01-01,NOW',
  'projection.date.format' = 'yyyy-MM-dd',
  'projection.date.interval' = '1',
  'projection.date.interval.unit' = 'DAYS',
  'storage.location.template' = 's3://BUCKET_NAME/ACCESS_LOGS_DEST/${date}*'
);

内容的提问来源于stack exchange，提问作者ekeren