Snowflake创建表值UDF后提示未知函数的问题求助

问题描述 #

使用以下代码创建Snowflake表值UDF：

create or replace function l2."xx_proc_getDqtReport"(schemaname text,tablename text)
returns table(tablename text,colname text,fillrate float,distvalues variant,dqtupdatetime timestamp)
language javascript
as
$$
    var sql1 = `create or replace table app.dqt_report_${tablename} (tablename text,colname text,fillrate float,distvalues variant,dqtupdatetime timestamp)`;
    var st1 = snowflake.createStatement({sqlText:sql1});
    var rs1 = st1.execute();

    var colquery = `select distinct column_name
                    from information_schema.columns
                    where table_schema ilike '${schemaname}' -- put your schema name here
                        and table_name ilike '${tablename}'  -- put your table name here
                    order by column_name`;

    var colst = snowflake.createStatement({sqlText:colquery});
    var colrs = colst.execute();

    var useschemasql = `use schema ${schemaname}`;
    var useschemast =  snowflake.createStatement({sqlText:useschemasql});
    var useschemars = useschemast.execute();

    while(colrs.next()){
        var colname = colrs.getColumnValue(1);
        var sql2 = `insert into app.dqt_report_${tablename}
                with t1 as (
                    select count(*) from ${tablename}
                ),
                t2 as (
                    select count(*) as fillcount from ${tablename} where nullif(${colname},'') is not null
                )
                select ${tablename}, ${colname}, (fillcount/rowcount)*100 as fillrate,listagg(distinct ${colname},',') as distvalues,current_timestamp() as dqtupdatetime
                from ${tablename},t1,t2`;

        var st2 = snowflake.createStatement({sqlText:sql2});
        var rs2 = st2.execute();
    }

    var fetchreportsql = `select * from app.dqt_report_${tablename}`;
    var fetchreportst =  snowflake.createStatement({sqlText:useschemasql});
    var fetchreportrs = useschemast.execute();

    return table(fetchreportrs);
$$;

执行后提示Function xx_proc_getDqtReport successfully created.，但调用语句select l2."xx_proc_getDqtReport"('l1','dtidr_elg_raw');报错Unknown user-defined function L2."xx_proc_getDqtReport"，且show user functions;无法查询到该函数。

排查与修复步骤 #

1. 确认UDF创建的上下文 #

Snowflake对象默认创建在当前激活的数据库和schema下，即使指定了l2.前缀，若当前未切换到对应数据库，可能导致对象创建位置不符：

• 执行select current_database(), current_schema();查看当前上下文，确认是否在l2 schema所属的数据库中；
• 若不在，先切换数据库：use database <l2所属数据库名>;，再重新执行UDF创建语句。

2. 跨上下文查询UDF实际位置 #

避免当前上下文限制，直接查询目标数据库的系统视图确认UDF是否存在：

select * from <目标数据库名>.information_schema.functions 
where function_name = 'xx_proc_getDqtReport' 
and function_schema = 'L2';

注：Snowflake默认将未用双引号包裹的对象名转为大写，若创建时用双引号保留了大小写，需严格匹配函数名的大小写。

3. 修正表值UDF的调用语法 #

表值UDF的正确调用方式必须用table()包裹，原调用语句缺少该关键字，这是报错的直接原因之一：

select * from table(l2."xx_proc_getDqtReport"('l1','dtidr_elg_raw'));

4. 修复UDF代码中的逻辑错误 #

代码中存在核心逻辑错误，会导致UDF创建后处于异常状态，无法被正常识别：
最后获取结果集时错误复用了切换schema的语句，正确代码应改为：

// 替换原错误代码
var fetchreportst = snowflake.createStatement({sqlText:fetchreportsql});
var fetchreportrs = fetchreportst.execute();

5. 验证权限与重新创建 #

• 确保当前角色拥有l2 schema下创建UDF的权限：grant create function on schema l2 to role <你的当前角色>;；
• 修正代码后重新执行UDF创建语句，再用show user functions in schema l2;确认UDF存在。

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