Redshift中Python UDF返回空值及调用报错问题求助

看起来你的UDF在处理不同格式的datetime字符串时遇到了兼容性问题——要么解析失败返回空值，要么直接抛出错误。这大概率是因为你当前的解析逻辑只适配了单一格式，没有覆盖你提到的带/不带毫秒、带/不带前导零的场景，而且可能缺少异常处理机制。

我来给你梳理下问题并提供修复方案：

1. 核心问题排查 #

你的原始UDF可能只尝试了一种datetime格式解析，当遇到不匹配的字符串时，要么抛出异常（直接传字符串时报错），要么静默返回空（调用表时）。比如如果你的代码是datetime.strptime(tzDate, "%Y-%m-%d %H:%M:%S")，那带毫秒的2024-05-20 14:30:00.123就会直接解析失败。

2. 改进后的Python UDF #

我们需要让UDF尝试多种可能的datetime格式，并且添加异常捕获，确保即使解析失败也能返回明确的提示（或者你需要的默认值）。这里是修改后的函数：

create or replace function everyonesdb.validateDate(tzDate varchar) 
returns varchar stable 
as $$
from datetime import datetime

def parse_datetime(date_str):
    # 定义所有可能的datetime格式，覆盖你提到的场景
    formats = [
        "%Y-%m-%d %H:%M:%S.%f",  # 带毫秒（带前导零）
        "%Y-%m-%d %H:%M:%S",      # 不带毫秒（带前导零）
        "%Y-%m-%d %-H:%-M:%-S",   # 不带前导零的时分秒
        "%Y-%m-%d %-H:%-M:%-S.%f",# 不带前导零+毫秒
        "%m/%d/%Y %H:%M:%S.%f",   # 额外考虑月/日/年格式（如果有需要）
        "%m/%d/%Y %H:%M:%S"
    ]
    for fmt in formats:
        try:
            # 解析成功后返回标准化的datetime字符串（保留三位毫秒）
            return datetime.strptime(date_str, fmt).strftime("%Y-%m-%d %H:%M:%S.%f")[:-3]
        except ValueError:
            continue
    # 如果所有格式都匹配失败，返回明确的错误标记或者NULL
    return "INVALID_DATE"  # 若需要返回空值，可改为return None

return parse_datetime(tzDate)
$$ language plpythonu;

3. 关键优化点解释 #

• 多格式覆盖：列出所有你实际遇到的datetime格式，确保覆盖带/不带毫秒、带/不带前导零的场景。注意%-H是Python中用于去掉前导零的格式符（部分Windows环境可能需要用%#H，大数据环境一般用%-H）。
• 异常捕获：用try-except包裹解析逻辑，避免单个无效字符串导致整个UDF报错。
• 标准化输出：解析成功后统一返回带三位毫秒的格式，方便后续计算时间间隔。

4. 测试与验证 #

先测试单个字符串确认UDF正常工作：

SELECT everyonesdb.validateDate('2024-05-20 14:30:00'); -- 不带毫秒
SELECT everyonesdb.validateDate('2024-05-20 4:5:3.123'); -- 不带前导零+毫秒
SELECT everyonesdb.validateDate('invalid-date'); -- 测试无效值

确认单个测试正常后，再关联表数据查询：

SELECT your_datetime_column, everyonesdb.validateDate(your_datetime_column)
FROM your_table;

5. 额外注意事项 #

• 如果你的datetime字符串包含时区信息，需要在格式中添加%Z或者%z，并调整解析逻辑适配时区。
• 如果返回NULL更适合你的后续计算，可以把return "INVALID_DATE"改成return None。

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