Amazon Athena 将varchar类型pricing_date转为date格式时报错如何解决

报错信息中的INVALID_FUNCTION_ARGUMENT: Invalid format: ""说明pricing_date列存在空字符串的脏数据，date_parse函数无法对空字符串执行指定格式的日期解析，因此抛出参数非法错误。

1. 先排查脏数据分布 #

执行以下查询确认pricing_date列的异常值情况：

SELECT DISTINCT pricing_date 
FROM "mydatabase"."sales" 
WHERE 
  pricing_date = '' 
  OR pricing_date IS NULL 
  OR length(trim(pricing_date)) != 19; -- 标准格式%m/%d/%Y %H:%i:%s的长度为19位

2. 加入脏数据兼容逻辑处理 #

Athena支持try()函数包裹可能报错的逻辑，解析失败时会自动返回NULL而非中断整个查询，修改后的查询语句如下：

SELECT 
  try(date_parse(trim(s.pricing_date),'%m/%d/%Y %H:%i:%s')) AS dataconverted
FROM "mydatabase"."sales" s

如果需要给解析失败的行设置默认日期，可以结合coalesce()函数实现：

SELECT 
  coalesce(
    try(date_parse(trim(s.pricing_date),'%m/%d/%Y %H:%i:%s')),
    DATE '1970-01-01' -- 可替换为你需要的默认日期
  ) AS dataconverted
FROM "mydatabase"."sales" s

3. 可选：过滤无效数据行 #

如果不需要解析失败的行，可以直接在WHERE条件中过滤掉异常值：

SELECT 
  date_parse(trim(s.pricing_date),'%m/%d/%Y %H:%i:%s') AS dataconverted
FROM "mydatabase"."sales" s
WHERE 
  s.pricing_date != '' 
  AND s.pricing_date IS NOT NULL
  AND try(date_parse(trim(s.pricing_date),'%m/%d/%Y %H:%i:%s')) IS NOT NULL

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