使用Snowflake UDF查询变更跟踪表元数据的标识符报错问题

问题原因 #

Snowflake的返回表类型SQL UDF在编译阶段会预校验查询涉及的表的静态结构，而METADATA$ACTION、METADATA$ISUPDATE是CHANGES子句返回的动态元数据列，不属于源表T1的原生静态字段，因此编译阶段无法识别，就会抛出无效标识符错误。你在函数外单独执行该查询时，SQL是运行时动态解析CHANGES的返回结构，因此可以正常识别这两个元数据列。

可行解决方法 #

• 方案1：直接内联查询逻辑，不封装为UDF
  把这段增量查询的逻辑直接嵌入到你的增量同步作业流程中（比如定时任务脚本、存储过程的业务逻辑段），不需要封装为表UDF即可正常执行。
  示例执行逻辑：

  -- 直接查询增量数据，不需要封装UDF
  select ID, C1, METADATA$ACTION AS ACTION_NAME, METADATA$ISUPDATE AS IS_UPDATE
  from t1
  changes(information => default)
  at(timestamp => TABLE_LAST_UPDATE_DATETIME('T1'))
  end(timestamp => current_timestamp());
  

• 方案2：改用存储过程封装逻辑
  如果需要复用这段逻辑，可以用带动态SQL的存储过程实现，动态SQL是运行时解析，不会提前校验静态表结构，不会触发该错误。示例存储过程代码：

  CREATE OR REPLACE PROCEDURE GET_T1_DELTA(start_time TIMESTAMP_TZ)
  RETURNS TABLE(ID NUMBER, C1 VARCHAR, ACTION_NAME VARCHAR, IS_UPDATE BOOLEAN)
  LANGUAGE SQL
  AS
  $$
  DECLARE
    res RESULTSET;
    query STRING DEFAULT $$
      select ID, C1, METADATA$ACTION AS ACTION_NAME, METADATA$ISUPDATE AS IS_UPDATE
      from t1
      changes(information => default)
      at(timestamp => ?)
      end(timestamp => current_timestamp())
    $$;
  BEGIN
    res := (EXECUTE IMMEDIATE :query USING :start_time);
    RETURN TABLE(res);
  END;
  $$;
  
  -- 调用存储过程获取增量数据
  CALL GET_T1_DELTA(TABLE_LAST_UPDATE_DATETIME('T1'));
  

内容的提问来源于stack exchange，提问作者tp-ft