Snowflake存储过程传递JSON对象遍历键值及报错排查

错误原因分析 #

1. SPLIT_PART参数类型错误：原代码FOR循环遍历的是整个JSON对象，而非tables数组内的字符串元素，key_value为OBJECT类型，但SPLIT_PART要求第一个参数是STRING类型，导致类型不匹配。
2. Executing NULL statement：循环逻辑错误导致生成的sql_command无效；同时创建的临时表名是JSON_OBJECTS，插入时却引用JSON_KEY_VALUES，表名不一致引发异常。
3. 其他问题：插入语句未给字符串值加单引号，会触发语法错误；临时表缺少需求中的SCHEMA_NAME和OBJECT_NAME字段。

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE proc_db.proc_schema.json_test(
    tables VARIANT
)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    table_name STRING;
    sql_command VARCHAR;
BEGIN
    -- 创建包含目标字段的临时表
    sql_command := 'CREATE OR REPLACE TEMP TABLE proc_db.proc_schema.JSON_TABLES (
        DATABASE_NAME VARCHAR,
        SCHEMA_NAME VARCHAR,
        OBJECT_NAME VARCHAR
    )';
    EXECUTE IMMEDIATE :sql_command;

    -- 遍历JSON中的tables数组元素
    FOR table_name IN (
        SELECT value::STRING 
        FROM TABLE(FLATTEN(input => :tables:tables))
    ) DO
        -- 拆分全限定表名并插入临时表
        INSERT INTO proc_db.proc_schema.JSON_TABLES 
        VALUES (
            SPLIT_PART(:table_name, '.', 1),
            SPLIT_PART(:table_name, '.', 2),
            SPLIT_PART(:table_name, '.', 3)
        );
    END FOR;
      
    RETURN 'Success!';
END;
$$;

调用语句（保持原逻辑） #

call proc_db.proc_schema.json_test(
    PARSE_JSON('{
        "tables": ["DB_PROD_1.DB_PROD_SCHEMA.DB_PROD_TABLE","DB_PROD.MY_SCHEMA.MY_TABLE", "DB_DEV.MY_SCHEMA.MY_TABLE_2"]
    }')
);

关键修复点说明 #

• 数组遍历：用FLATTEN函数展开JSON中的tables数组，将每个元素转为STRING类型，确保SPLIT_PART参数类型合法。
• 临时表结构：补充SCHEMA_NAME和OBJECT_NAME字段，匹配需求中的表结构。
• 插入逻辑：直接通过绑定变量插入拆分结果，避免字符串拼接的语法错误，同时保证数据类型正确。
• 表名一致性：创建和插入操作统一使用临时表名JSON_TABLES，消除不存在表的异常。

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