Snowflake存储过程查询信息架构时语法错误求助

问题描述 #

需求：查询information schema元数据，根据数据类型生成统计列（日期类型生成最小/最大日期，数值类型生成计数、去重计数）；当前简化为传入DB_NAME、TBL_SCHEMA、TBL_NAME作为参数，期望输出SCHEMA_NM、TBL_NAME、_COUNT、_DISTINCT_COUNT、_MIN_DATE、_MAX_DATE列。

编写了如下Snowflake存储过程代码：

CREATE OR REPLACE PROCEDURE PROFILING( DB_NAME VARCHAR(16777216),TBL_SCHEMA VARCHAR(16777216), TBL_NAME VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
EXECUTE AS CALLER
AS 
$$
DECLARE
BEGIN
    create or replace temporary table tbl_name as (select case when data_type=NUMBER then (execute immediate 'select count(col_val) from ' || :1 || '.' ||  :2 || '.' ||  :3) else null end as col_value_num,case when data_type=DATE then (execute immediate 'select count(col_val) from ' || :1 || '.' ||  :2 || '.' ||  :3) else null end as col_min_date from   ':1.information_schema.columns where table_catalog=:1 and table_schema=:2 and table_name=:3 ' )) ;
     insert into some_base_table as select * from tbl_name;
     truncate tbl_name 
RETURN 'SUCCESS';
END;
$$;  

执行时出现错误：

error : SQL compilation error: Invalid expression value (?SqlExecuteImmediateDynamic?) for assignment.

错误原因与修复方案 #

核心错误是在SELECT语句的CASE表达式中直接使用EXECUTE IMMEDIATE——Snowflake的SQL存储过程不允许将动态执行语句直接作为查询的返回值，必须先将动态查询结果存入变量，再构建最终结果。此外代码还有其他语法问题，以下是分步修复：

1. 调整动态SQL执行逻辑

不能在SELECT的CASE分支里直接调用EXECUTE IMMEDIATE，需要先遍历目标表的所有列，针对每个列的类型执行对应的统计逻辑，再将结果聚合。

2. 修正information schema查询语法

原代码中':1.information_schema.columns...'的字符串拼接方式错误，需用参数拼接正确的Schema路径，同时推荐用参数名（而非位置参数:1）引用变量，提升可读性。

3. 修复基础语法问题

• 移除INSERT语句中多余的AS关键字；
• 给TRUNCATE语句添加分号；
• 替换固定的col_val为动态列名；
• 调整临时表结构，匹配预期输出字段。

修正后的完整代码 #

CREATE OR REPLACE PROCEDURE PROFILING(DB_NAME VARCHAR, TBL_SCHEMA VARCHAR, TBL_NAME VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    col_cursor CURSOR FOR
        SELECT column_name, data_type
        FROM IDENTIFIER(:DB_NAME || '.INFORMATION_SCHEMA.COLUMNS')
        WHERE TABLE_CATALOG = :DB_NAME
          AND TABLE_SCHEMA = :TBL_SCHEMA
          AND TABLE_NAME = :TBL_NAME;
    col_rec RECORD;
    v_count NUMBER;
    v_distinct_count NUMBER;
    v_min_date DATE;
    v_max_date DATE;
    v_temp_table VARCHAR := 'TBL_PROFILE_TEMP';
BEGIN
    -- 创建临时表存储统计结果
    CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:v_temp_table) (
        SCHEMA_NM VARCHAR,
        TBL_NAME VARCHAR,
        COLUMN_NAME VARCHAR,
        _COUNT NUMBER,
        _DISTINCT_COUNT NUMBER,
        _MIN_DATE DATE,
        _MAX_DATE DATE
    );

    -- 遍历每个列执行统计
    FOR col_rec IN col_cursor DO
        -- 数值类型：计算计数、去重计数
        IF col_rec.data_type IN ('NUMBER', 'INT', 'FLOAT', 'DOUBLE') THEN
            EXECUTE IMMEDIATE 'SELECT COUNT(' || col_rec.column_name || '), COUNT(DISTINCT ' || col_rec.column_name || ') FROM ' || :DB_NAME || '.' || :TBL_SCHEMA || '.' || :TBL_NAME
            INTO v_count, v_distinct_count;
            INSERT INTO IDENTIFIER(:v_temp_table)
            VALUES (:TBL_SCHEMA, :TBL_NAME, col_rec.column_name, v_count, v_distinct_count, NULL, NULL);
        -- 日期类型：计算最小、最大日期
        ELSIF col_rec.data_type = 'DATE' THEN
            EXECUTE IMMEDIATE 'SELECT MIN(' || col_rec.column_name || '), MAX(' || col_rec.column_name || ') FROM ' || :DB_NAME || '.' || :TBL_SCHEMA || '.' || :TBL_NAME
            INTO v_min_date, v_max_date;
            INSERT INTO IDENTIFIER(:v_temp_table)
            VALUES (:TBL_SCHEMA, :TBL_NAME, col_rec.column_name, NULL, NULL, v_min_date, v_max_date);
        -- 其他类型可按需扩展
        ELSE
            INSERT INTO IDENTIFIER(:v_temp_table)
            VALUES (:TBL_SCHEMA, :TBL_NAME, col_rec.column_name, NULL, NULL, NULL, NULL);
        END IF;
    END FOR;

    -- 将结果插入基础表
    INSERT INTO some_base_table
    SELECT SCHEMA_NM, TBL_NAME, _COUNT, _DISTINCT_COUNT, _MIN_DATE, _MAX_DATE
    FROM IDENTIFIER(:v_temp_table);

    -- 清理临时表
    TRUNCATE TABLE IDENTIFIER(:v_temp_table);
    DROP TABLE IDENTIFIER(:v_temp_table);

    RETURN 'SUCCESS';
END;
$$;

代码说明 #

• 使用游标遍历目标表的所有列，获取列名和数据类型；
• 针对不同数据类型执行对应的动态统计查询，将结果存入变量后插入临时表；
• 最后将临时表的数据写入some_base_table，并清理临时表；
• 用IDENTIFIER()函数处理动态表名/列名，避免SQL注入风险。

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