Athena整数分区转yyyy/mm/dd/hh：查询优化与配置问询

方案一：查询时动态拼接日期（无需修改表结构） #

直接在WHERE子句中将year、month、day字段拼接为标准日期格式，即可使用BETWEEN语法。需用LPAD补全月份/日期的前导零，确保日期解析正确：

SELECT *
FROM vehicles
WHERE 
  date(concat(year, '-', lpad(month, 2, '0'), '-', lpad(day, 2, '0'))) 
  BETWEEN date('2022-08-31') AND date('2022-09-01')
  AND agency = 'your_agency_value'

如果需要精确到小时，可拼接小时字段：

SELECT *
FROM vehicles
WHERE 
  timestamp(concat(year, '-', lpad(month, 2, '0'), '-', lpad(day, 2, '0'), ' ', lpad(hour, 2, '0'), ':00:00'))
  BETWEEN timestamp('2022-08-31 23:00:00') AND timestamp('2022-09-01 01:00:00')
  AND agency = 'your_agency_value'

方案二：重建表使用单日期分区字段（推荐，更高效） #

通过分区投影将单个日期/时间分区字段映射到S3上的year/month/day/hour路径，从根本上简化查询并优化分区裁剪。

步骤1：创建新的外部表 #

替换原有的多分区字段为单个dt（日期时间）字段，配置分区投影参数匹配现有S3路径：

CREATE EXTERNAL TABLE `vehicles_date_partitioned`(
  `objectid` bigint, 
  `trip_id` string, 
  `vehicle_id` string, 
  `route_id` string, 
  `direction_id` bigint, 
  `timestamp` bigint, 
  `delay` bigint, 
  `delay_type` string, 
  `headsign` string, 
  `route_short_name` string, 
  `route_long_name` string, 
  `longitude` double, 
  `latitude` double)
PARTITIONED BY ( 
  `agency` string,
  `dt` timestamp) -- 单个日期时间分区字段
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://transitnode/vehicles/'
TBLPROPERTIES (
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'areColumnsQuoted'='false', 
  'averageRecordSize'='115', 
  'classification'='csv', 
  'columnsOrdered'='true', 
  'compressionType'='none', 
  'delimiter'=',', 
  'exclusions'='["s3://transitnode/athena/*"]', 
  'skip.header.line.count'='1',
  -- 分区投影配置
  'projection.enabled' = 'true',
  'projection.agency.type' = 'injected',
  'projection.dt.type' = 'datetime',
  'projection.dt.format' = 'yyyy-MM-dd HH:mm:ss', -- 匹配dt字段的格式
  'projection.dt.interval' = '1',
  'projection.dt.interval.unit' = 'HOURS', -- 按小时分区，匹配S3路径的hour层级
  'projection.dt.range' = '2020-01-01 00:00:00,NOW', -- 根据实际数据时间范围调整
  -- 映射到S3的year/month/day/hour路径
  'storage.location.template' = 's3://transitnode/vehicles/${agency}/${dt.year}/${dt.month}/${dt.day}/${dt.hour}/'
);

步骤2：使用简化查询 #

创建完成后，即可直接用BETWEEN语法查询日期范围：

SELECT *
FROM vehicles_date_partitioned
WHERE 
  dt BETWEEN timestamp('2022-08-31 00:00:00') AND timestamp('2022-09-01 23:59:59')
  AND agency = 'your_agency_value'

注意事项 #

• 无需执行MSCK REPAIR TABLE，分区投影会自动识别S3上的分区路径。
• 若仅需按天查询，可将dt设为date类型，调整projection.dt.type为date，format为yyyy-MM-dd，interval.unit为DAYS。
• 确保projection.dt.range覆盖实际数据时间范围，避免查询遗漏分区。

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