AWS Athena日期查询触发INVALID_FUNCTION_ARGUMENT错误的排查与解决

问题场景 #

我在AWS Athena中创建了如下视图：

SELECT
    REPLACE(CAST(rt_id AS VARCHAR), '.0', '') AS rt_id,
    survey_id,
    DATE(date_parse(event_date, '%Y-%m-%d')) as survey_date,
    species_name,
    all_runs as count
from
    mt_efishing_data

执行带日期范围的查询时触发错误：INVALID_FUNCTION_ARGUMENT: Invalid format: """，查询语句如下：

select *
from counts_dates
where rt_id in ('56','275','276')
and species_name = 'Atlantic salmon'
and survey_date between cast('2023-09-01' as date) and cast('2023-09-30' as date);

移除日期过滤条件后查询正常，尝试调整date_parse语法、改用>/<运算符均无效。排查发现源表event_date列存在空值，移除这些空值行后查询可正常执行，但必须将日期字面量转换为date类型，直接使用字符串字面量无法生效。

附源表数据样本：

site_id,event_date,event_date_year,easting,northing,survey_length,survey_area,fished_width,fished_area,survey_method,survey_strategy,n_runs,species_name,run1,run2,run3,all_runs,rt_id,survey_id
1,2023-09-04,2023.0,379176.0,481427.0,37.8,117.18,3.1,117.18,DC ELECTRIC FISHING,SQ,1,Brown / sea trout,4,,,4,275.0,1
1,2023-09-04,2023.0,379176.0,481427.0,37.8,117.18,3.1,117.18,DC ELECTRIC FISHING,SQ,1,Atlantic salmon,0,,,0,275.0,1
1,2023-09-04,2023.0,379176.0,481427.0,37.8,117.18,3.1,117.18,DC ELECTRIC FISHING,SQ,1,Bullhead,2,,,2,275.0,1
2,2023-09-13,2023.0,378596.0,480258.0,55.0,407.0,7.4,407.0,DC ELECTRIC FISHING,SQ,1,Brown / sea trout,1,,,1,255.0,2

报错原因 #

1. 空值导致解析失败：date_parse函数无法解析空字符串或NULL值，当源表event_date存在空值时，视图执行过程中会直接抛出格式错误。
2. 日期类型不匹配：Athena中字符串字面量与date类型列直接比较时，会因类型隐式转换问题导致过滤逻辑失效，必须显式将字符串转为date类型。

解决方法 #

1. 修改视图，兼容空值 #

使用Athena提供的try_date_parse函数，该函数在解析失败时返回NULL而非报错，同时可选择性过滤空值行：

SELECT
    REPLACE(CAST(rt_id AS VARCHAR), '.0', '') AS rt_id,
    survey_id,
    -- 解析失败返回NULL，避免报错
    DATE(try_date_parse(event_date, '%Y-%m-%d')) as survey_date,
    species_name,
    all_runs as count
from
    mt_efishing_data
-- 可选：提前过滤空值行，减少视图中的无效数据
where event_date is not null and event_date != ''

2. 调整查询语句，规避空值并确保类型匹配 #

若不修改视图，查询时先过滤掉survey_date为NULL的行，同时显式转换日期字面量：

select *
from counts_dates
where rt_id in ('56','275','276')
and species_name = 'Atlantic salmon'
and survey_date is not null
-- 使用date字面量语法简化类型转换
and survey_date between date '2023-09-01' and date '2023-09-30';

3. 优化日期字面量写法 #

Athena支持直接用date 'YYYY-MM-DD'的语法替代cast('YYYY-MM-DD' as date)，代码更简洁易读。

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