Snowflake UDTF仅接受常量传参 动态查询报错解决方案咨询

Snowflake内置的information_schema.task_dependents()是特殊的元数据扫描函数，编译阶段就强制要求task_name入参必须是常量值，不支持关联查询时逐行传入由表字段拼接生成的动态值。即使自定义UDTF包装该函数，也无法绕开底层的参数校验逻辑，因此执行动态关联查询时会抛出如下错误：

argument 1 to function TASK_DEPENDENTS_SCAN needs to be constant, found 'CORRELATION(SYS_VW.TASKNAME_1)'

方案1：存储过程批量遍历（可嵌入调度流程） #

通过存储过程游标逐行读取stg表的全限定任务名，每轮循环将任务名作为常量传入task_dependents查询依赖项，最后合并所有结果集返回，完全规避动态传参限制：

create or replace procedure get_all_stg_task_dependents()
returns table(dependent_name varchar, source_task_name varchar)
language sql
as
$$
declare
  final_result resultset default (select * from table(result_scan(-1)) where 1=0);
  task_cursor cursor for 
    select database_name || '.' || schema_name || '.' || name as full_task_name 
    from stg;
begin
  for task_row in task_cursor do
    let single_task_deps resultset := (
      select 
        :task_row.full_task_name as source_task_name,
        name as dependent_name
      from table(information_schema.task_dependents(
        task_name => :task_row.full_task_name, 
        recursive => true
      ))
    );
    final_result := (select * from final_result union all select * from single_task_deps);
  end for;
  return table(final_result);
end;
$$;

-- 直接调用即可拿到stg表所有任务对应的依赖项
call get_all_stg_task_dependents();

方案2：动态拼接SQL（适合临时查询、脚本场景） #

如果不想创建存储过程，可以先通过SQL生成所有任务的依赖查询语句，拼接为UNION ALL结构后执行，此时每个task_dependents的入参都是硬编码常量，可正常运行：

-- 执行该语句生成最终查询SQL
select listagg(
  'select '''||full_task_name||''' as source_task_name, name as dependent_name 
   from table(information_schema.task_dependents(task_name => '''||full_task_name||''', recursive => true))',
  ' union all '
) as executable_sql
from (
  select distinct database_name || '.' || schema_name || '.' || name as full_task_name 
  from stg
);

执行上述语句后，复制结果列executable_sql中的SQL直接运行，即可得到所有任务的依赖关联结果。

• 不要尝试通过自定义标量UDF、UDTF嵌套包装的方式绕过限制，元数据扫描函数的常量参数校验是在查询编译阶段执行的，嵌套包装无法改变入参为动态关联值的属性，依然会抛出相同错误。

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