如何在Athena中创建按日期分区的CloudFront日志表？查询无结果排障

CloudFront日志存储在S3的路径格式为：
s3://aws-cloudfront-log-[AWS账号ID]/[自定义前缀]/E[CloudFront分发ID].[年]-[月]-[日]-[小时].[哈希值].gz
示例路径：
s3://aws-cloudfront-log-1290287349012/my-app/E12KDDSA1S7.2024-06-26-23.9e4a2b9e.gz

我参考官方标准日志DDL修改后，创建了按日期分区的表，DDL如下：

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs.editorial (
  `date` DATE,
  time STRING,
  x_edge_location STRING,
  sc_bytes BIGINT,
  c_ip STRING,
  cs_method STRING,
  cs_host STRING,
  cs_uri_stem STRING,
  sc_status INT,
  cs_referrer STRING,
  cs_user_agent STRING,
  cs_uri_query STRING,
  cs_cookie STRING,
  x_edge_result_type STRING,
  x_edge_request_id STRING,
  x_host_header STRING,
  cs_protocol STRING,
  cs_bytes BIGINT,
  time_taken FLOAT,
  x_forwarded_for STRING,
  ssl_protocol STRING,
  ssl_cipher STRING,
  x_edge_response_result_type STRING,
  cs_protocol_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_filter string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION 's3://aws-cloudfront-log-1290287349012/my-app/'
TBLPROPERTIES (
  'skip.header.line.count'='2',
  'projection.date_filter.format'='yyyy-MM-dd-HH',
  'projection.date_filter.interval'='1',
  'projection.date.interval.unit'='HOURS',
  'projection.date_filter.range'='2021-01-01-00,NOW',
  'projection.date_filter.type'='date',
  'projection.enabled'='true',
  'storage.location.template'='s3://aws-cloudfront-log-1290287349012/my-app/E12KDDSA1S7.${date}'
);

执行查询select * FROM cloudfront_logs.editorial WHERE date_filter = '2024-06-26-23' LIMIT 1;时无结果返回，想知道哪里配置错误？另外不想用需要额外组件调整日志结构的方案。

核心错误分析 #

• 模板变量与分区字段不匹配：storage.location.template里用了${date}，但分区字段名是date_filter，变量名必须和分区字段完全一致，应改为${date_filter}。
• 路径模板未匹配完整文件名：日志文件名是E12KDDSA1S7.2024-06-26-23.9e4a2b9e.gz，原模板只写到日期部分，需要用通配符匹配后续的哈希值和后缀，应改为E12KDDSA1S7.${date_filter}.*.gz。
• 投影配置拼写错误：原DDL中projection.date.interval.unit是错误的，正确的键名应为projection.date_filter.interval.unit，否则时间间隔配置不生效。

修正后的DDL #

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs.editorial (
  `date` DATE,
  time STRING,
  x_edge_location STRING,
  sc_bytes BIGINT,
  c_ip STRING,
  cs_method STRING,
  cs_host STRING,
  cs_uri_stem STRING,
  sc_status INT,
  cs_referrer STRING,
  cs_user_agent STRING,
  cs_uri_query STRING,
  cs_cookie STRING,
  x_edge_result_type STRING,
  x_edge_request_id STRING,
  x_host_header STRING,
  cs_protocol STRING,
  cs_bytes BIGINT,
  time_taken FLOAT,
  x_forwarded_for STRING,
  ssl_protocol STRING,
  ssl_cipher STRING,
  x_edge_response_result_type STRING,
  cs_protocol_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_filter string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION 's3://aws-cloudfront-log-1290287349012/my-app/'
TBLPROPERTIES (
  'skip.header.line.count'='2',
  'projection.date_filter.format'='yyyy-MM-dd-HH',
  'projection.date_filter.interval'='1',
  'projection.date_filter.interval.unit'='HOURS',
  'projection.date_filter.range'='2021-01-01-00,NOW',
  'projection.date_filter.type'='date',
  'projection.enabled'='true',
  'storage.location.template'='s3://aws-cloudfront-log-1290287349012/my-app/E12KDDSA1S7.${date_filter}.*.gz'
);

额外注意事项 #

1. 修正后的DDL使用Athena的投影分区功能，不需要移动或修改S3上的原始日志文件，直接通过模板匹配文件名中的日期字段即可实现分区查询。
2. 执行修正后的DDL后，重新运行查询语句即可获取对应时间的日志数据。

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