BigQuery自定义UDF调用FORMAT_DATE原生函数报错及相关问题求助

问题排查与修复方案 #

1. 核心报错原因梳理 #

• 类型不匹配错误：FORMAT_DATE内置函数要求输入参数为DATE类型，你定义UDF时指定的入参是DATETIME类型，类型不匹配会直接导致运行时报错。
• 函数找不到错误：你创建的永久UDF归属在mytable数据集下，调用时如果不指定数据集前缀，GBQ默认只会在系统内置函数、当前会话临时函数范围内查找，自然会提示*function to_char not found*错误。
• 临时函数加载慢：这是GBQ临时函数的固有特性，每次会话都需要重新编译加载函数逻辑，数据量较大时耗时会明显拉长，使用永久函数可以完全规避这个问题。

2. 修复步骤 #

第一步：修正UDF定义

如果你的入参确实是DATETIME类型，直接替换为适配DATETIME的格式化函数即可：

CREATE OR REPLACE FUNCTION mytable.TO_CHAR(dstamp DATETIME, fmt STRING)
RETURNS STRING AS
(
    FORMAT_DATETIME(fmt, dstamp)
);

如果你的入参实际为DATE类型，直接调整UDF的入参定义即可：

CREATE OR REPLACE FUNCTION mytable.TO_CHAR(dstamp DATE, fmt STRING)
RETURNS STRING AS
(
    FORMAT_DATE(fmt, dstamp)
);

第二步：调用时添加数据集前缀

永久UDF必须带上归属的数据集名才能正常调用，修改查询语句如下：

SELECT
    mytable.TO_CHAR(End_Time, 'yyyy-mm') AS Month_and_Year,
    -- 剩余查询逻辑
FROM 你的业务表名

可选优化：省略数据集前缀调用

如果不想每次调用都写数据集名，可以在查询开头指定默认数据集：

SET @@dataset_id = 'mytable';

-- 后续调用UDF无需再加前缀
SELECT
    TO_CHAR(End_Time, 'yyyy-mm') AS Month_and_Year,
FROM 你的业务表名

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