Athena查询dt时间范围时如何自动添加分区过滤条件至WHERE子句

首先修正你现有配置的小问题：你建表时声明的分区字段是hour，但S3存储路径里的分区键为hh=，二者需要对齐，要么调整建表语句的分区字段为hh string，要么修改S3路径前缀为hour=，否则分区无法正常加载匹配。

方案1：用CTE自动生成所需分区（无额外表配置，适配性最高） #

你不需要手动枚举覆盖范围的分区列表，只需通过序列生成函数自动构造需要的分区范围，查询时自动关联过滤，性能和手动写IN语句完全一致，仅扫描对应分区的文件：

-- 仅需要修改下方起止时间即可，无需手动计算分区
WITH query_params AS (
  SELECT
    TIMESTAMP '2021-10-09 23:05:00' AS start_dt,
    TIMESTAMP '2021-10-11 01:00:00' AS end_dt
),
target_partitions AS (
  -- 自动生成时间范围内所有需要的小时分区
  SELECT
    date_format(hour_slot, '%Y-%m-%d') AS day,
    lpad(CAST(hour(hour_slot) AS VARCHAR), 2, '0') AS hh
  FROM query_params,
  UNNEST(SEQUENCE(
    date_trunc('hour', start_dt),
    date_trunc('hour', end_dt),
    INTERVAL '1' HOUR
  )) AS t(hour_slot)
)
SELECT foo.* FROM foo
-- 关联自动生成的分区列表实现分区修剪
INNER JOIN target_partitions
  ON foo.day = target_partitions.day
  AND foo.hh = target_partitions.hh
-- 保留原始dt的精确过滤
WHERE dt > (SELECT start_dt FROM query_params)
  AND dt < (SELECT end_dt FROM query_params)

方案2：封装视图实现透明查询 #

如果希望用户完全不需要感知分区字段，只需要写dt条件就可以自动走分区过滤，可以把上面的逻辑封装成视图：

CREATE VIEW v_foo AS
SELECT *
FROM foo

如果使用Athena 3及以上版本，还可以给视图加时间参数，调用时直接传入起止时间即可，完全不需要处理分区逻辑。

方案3：配置分区投影减少分区加载开销 #

如果你还想进一步省去Athena扫描分区元数据的开销，可以开启Athena的分区投影功能，直接在表属性里配置分区的生成规则，不需要手动加载分区：

ALTER TABLE foo SET TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.day.type' = 'date',
  -- 根据你的实际数据时间范围调整，支持写NOW代表当前时间
  'projection.day.range' = '2021-01-01,NOW',
  'projection.day.format' = 'yyyy-MM-dd',
  'projection.hh.type' = 'integer',
  'projection.hh.min' = '0',
  'projection.hh.max' = '23',
  'projection.hh.digits' = '2',
  'storage.location.template' = 's3://foo/foo/day=${day}/hh=${hh}'
)

配置完成后，配合方案1的查询写法，查询效率会更高。

目前Athena暂不支持完全零改造的自动dt条件转分区过滤，因为引擎无法自动感知dt字段和分区字段的派生关系，上述方案已经可以完全避免手动编写分区IN列表的繁琐操作。

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