AWS Athena跨prod/test环境查询特定月份日志结果不一致求助

1. 检查时区一致性 #

• 分别在test和prod环境执行以下语句，对比返回结果：

  SELECT session_timezone(), current_date, MONTH(current_date - interval '1' month);
  

  • 如果prod的session_timezone()与test不一致，会导致current_date和month()函数的计算基于不同时区，进而出现月份匹配偏差。比如prod用UTC时区、test用东八区时，当东八区是9月1日，UTC仍为8月31日，此时prod中current_date - interval '1' month得到7月31日，MONTH返回7，会匹配7月数据；而test环境得到8月1日，MONTH返回8，仅匹配8月数据。

2. 确认date列的数据类型 #

• 执行DESCRIBE your_table;查看date列的类型：
  • 若test的date是DATE类型，prod是TIMESTAMP或STRING类型，会导致month()函数计算逻辑差异：
    • 对TIMESTAMP类型，month()会基于会话时区提取月份，可能把UTC时区的上月末数据识别为本月数据（比如UTC的7月31日23点，在东八区是8月1日，month()返回8）。
    • 对STRING类型，若存在格式不规范的日期（隐性时间后缀、分隔符错误等），隐式转换为日期时可能出现月份识别错误。

3. 验证分区过滤逻辑 #

• Athena依赖分区 pruning 优化查询，若prod环境的分区键（如dt、year/month）未正确过滤，会扫描过去2个月的全量数据再二次过滤：
  • 检查prod查询是否遗漏分区过滤条件，比如未添加WHERE year(date) = YEAR(current_date - interval '1' month)，仅过滤月份可能导致同月份的跨年度数据被匹配（这里日志仅保留2个月，主要是同一年的7、8月）。
  • 执行SHOW PARTITIONS your_table;查看现有分区，确认是否同时存在7月和8月分区，再检查查询是否命中了不必要的分区。

4. 排查数据质量问题 #

• 查询prod环境中近2个月date列的分布情况：

  SELECT DISTINCT date_trunc('month', date) AS month, COUNT(*)
  FROM your_table
  WHERE date >= date_add('month', -2, current_date)
  GROUP BY date_trunc('month', date)
  ORDER BY month;
  

  • 查看7月分区中是否存在8月的date值，或8月分区中是否有7月的date值——日志生成时的日期赋值错误会导致这种情况，进而让month(date)条件同时匹配两个月的数据。

5. 替换日期过滤逻辑（临时验证） #

• 用精确的日期范围查询替代仅过滤月份的方式，验证是否能正确返回单月数据：

  WHERE date >= date_trunc('month', current_date - interval '1' month)
    AND date < date_trunc('month', current_date)
  

  • 如果该查询能正确返回8月数据，说明原month()函数的匹配逻辑存在环境差异；如果仍返回两个月数据，说明数据本身或分区存在问题。

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