AWS Athena混合日期粒度S3路径分区投影配置咨询

你的判断完全正确：Athena Partition Projection的storage.location.template是固定深度的路径模板，你当前配置的模板对应年/月/日/时4层分区，扫描时只会匹配该深度路径下的文件，直接存放在月级、日级浅路径下的文件不在扫描范围内，因此查询2022年06月数据时返回0条。
单张Partition Projection表原生不支持直接映射不同深度的混合粒度分区路径，可通过以下两种可行方案实现预期查询逻辑。

调整Kinesis Firehose的动态分区输出规则，将所有粒度的数据写入统一深度的分区路径，通过占位值填充缺失的分区维度，无需改动业务查询逻辑即可完全适配Partition Projection。

配置步骤 #

• 调整Firehose输出路径规则：
  • 月级数据：缺失的day字段填固定占位值00，缺失的hour字段填固定占位值00，路径格式为s3://bucket/prefix/${year}/${month}/00/00/
  • 日级数据：缺失的hour字段填固定占位值00，路径格式为s3://bucket/prefix/${year}/${month}/${day}/00/
  • 小时级数据：保留原有4层路径格式，路径为s3://bucket/prefix/${year}/${month}/${day}/${hour}/
• 调整Partition Projection配置，将day和hour的投影范围扩展包含占位值00，对应DDL如下：

create external table `my_table`(
  `period` string COMMENT '时间粒度标识',
  `item_id` string COMMENT '业务ID')
PARTITIONED BY ( 
  `year` string, 
  `month` string, 
  `day` string, 
  `hour` string)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION
  's3://bucket/prefix/'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.day.type'='integer',
  'projection.day.digits' = '2',
  'projection.day.range'='00,31',
  'projection.hour.type'='integer',
  'projection.hour.digits' = '2',
  'projection.hour.range'='00,23',
  'projection.month.type'='integer', 
  'projection.month.digits'='2', 
  'projection.month.range'='01,12',
  'projection.year.format'='yyyy', 
  'projection.year.range'='2022,NOW',  
  'projection.year.type'='date', 
  'storage.location.template'='s3://bucket/prefix/${year}/${month}/${day}/${hour}');

查询逻辑匹配 #

• 查询整月数据（如2022年06月）：添加过滤条件year='2022' and month='06'，Athena会自动扫描该月路径下所有day（00-31）、hour（00-23）的文件，自动覆盖月级（day=00,hour=00）、日级（hour=00）、小时级三类数据
• 查询单日数据（如2022年05月04日）：添加过滤条件year='2022' and month='05' and day='04'，会自动扫描该日路径下所有hour（00-23）的文件，覆盖日级（hour=00）、小时级两类数据，不会拉取无关的月级数据
• 查询单小时数据：添加完整的年、月、日、时过滤条件，仅扫描对应小时的文件

如果无法调整Kinesis Firehose的现有写入规则，可以为每个粒度的数据单独创建启用Partition Projection的表，再通过UNION ALL视图对外提供统一查询入口，分区裁剪会自动生效，不会扫描多余数据。

配置步骤 #

• 创建月级分区表，仅保留年、月两个分区列：

create external table `my_table_monthly`(
  `period` string,
  `item_id` string)
PARTITIONED BY (`year` string, `month` string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
STORED AS TEXTFILE
LOCATION 's3://bucket/prefix/'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.month.type'='integer', 
  'projection.month.digits'='2', 
  'projection.month.range'='01,12',
  'projection.year.format'='yyyy', 
  'projection.year.range'='2022,NOW',  
  'projection.year.type'='date', 
  'storage.location.template'='s3://bucket/prefix/${year}/${month}/'
);

• 创建日级分区表，保留年、月、日三个分区列：

create external table `my_table_daily`(
  `period` string,
  `item_id` string)
PARTITIONED BY (`year` string, `month` string, `day` string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
STORED AS TEXTFILE
LOCATION 's3://bucket/prefix/'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.day.type'='integer',
  'projection.day.digits' = '2',
  'projection.day.range'='01,31',
  'projection.month.type'='integer', 
  'projection.month.digits'='2', 
  'projection.month.range'='01,12',
  'projection.year.format'='yyyy', 
  'projection.year.range'='2022,NOW',  
  'projection.year.type'='date', 
  'storage.location.template'='s3://bucket/prefix/${year}/${month}/${day}/'
);

• 小时级表直接复用你之前创建的my_table即可。
• 创建统一查询视图：

create view my_table_unified as
select period, item_id, year, month, day, hour, 'monthly' as granularity
from my_table_monthly
union all
select period, item_id, year, month, day, hour, 'daily' as granularity
from my_table_daily
union all
select period, item_id, year, month, day, hour, 'hourly' as granularity
from my_table;

查询逻辑匹配 #

• 查询整月数据：添加过滤条件year='2022' and month='06'，三个表都会应用分区裁剪，分别扫描月级、日级、小时级路径下的对应数据
• 查询单日数据：添加过滤条件year='2022' and month='05' and day='04'，可额外添加granularity in ('daily','hourly')过滤掉不需要的月级数据
• 查询单小时数据：添加完整分区过滤条件+granularity='hourly'，仅扫描小时级表的对应分区

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