Snowflake存储过程：视图转表功能无法运行，求技术指导

问题点分析 #

• 架构过滤逻辑错误：原代码查询INFORMATION_SCHEMA.VIEWS时，用TABLE_SCHEMA = SOURCE_SCHEMA匹配，但SOURCE_SCHEMA是SOURCE.STAGE（数据库.架构）格式，而TABLE_SCHEMA仅存储架构名，TABLE_CATALOG才对应数据库名，导致无法正确筛选目标视图。
• 目标架构格式错误：TARGET_SCHEMA := 'TARGET.STAGE.TABLES'是三级结构，Snowflake对象命名规则为数据库.架构.对象，此处应将目标架构设为TARGET.STAGE，表直接挂载在该架构下。
• 循环变量赋值错误：FOR VIEW_NAME IN (...)返回的是行对象，直接赋值给CURRENT_VIEW会触发类型不匹配错误，需提取VIEW_NAME.TABLE_NAME字段。
• 字符串转义错误：代码中使用"作为双引号转义符，这是HTML转义规则，Snowflake SQL存储过程应直接用单引号包裹双引号，或用$$分隔符简化动态SQL编写。
• 未处理目标表存在场景：原代码未判断目标表是否已存在，重复执行会抛出对象已存在的错误，建议添加IF NOT EXISTS或CREATE OR REPLACE TABLE逻辑。

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE COPY_VIEWS_TO_TABLES()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    SOURCE_DB STRING;       
    SOURCE_SCHEMA STRING;   
    TARGET_DB STRING;       
    TARGET_SCHEMA STRING;   
    CURRENT_VIEW STRING;        
    CREATE_TABLE_SQL STRING;    

BEGIN
    -- 拆分源和目标的数据库与架构
    SOURCE_DB := 'SOURCE';
    SOURCE_SCHEMA := 'STAGE';
    TARGET_DB := 'TARGET';
    TARGET_SCHEMA := 'STAGE';

    -- 遍历源数据库架构下的所有视图
    FOR VIEW_REC IN (
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.VIEWS
        WHERE TABLE_CATALOG = SOURCE_DB
          AND TABLE_SCHEMA = SOURCE_SCHEMA
    )
    DO
        CURRENT_VIEW := VIEW_REC.TABLE_NAME;

        -- 构造动态SQL，用$$避免引号转义，添加IF NOT EXISTS避免重复创建报错
        CREATE_TABLE_SQL := $$
            CREATE TABLE IF NOT EXISTS "$$ || TARGET_DB || $$"."$$ || TARGET_SCHEMA || $$"."$$ || CURRENT_VIEW || $$" AS 
            SELECT * FROM "$$ || SOURCE_DB || $$"."$$ || SOURCE_SCHEMA || $$"."$$ || CURRENT_VIEW || $$"
        $$;

        EXECUTE IMMEDIATE CREATE_TABLE_SQL;
    END FOR;

    RETURN '所有视图已成功复制为表！';
END;
$$;

额外建议 #

• 权限验证：确保存储过程所有者拥有源视图的SELECT权限，以及目标架构的CREATE TABLE权限。
• 约束与注释同步：CREATE TABLE ... AS SELECT仅复制数据和基础数据类型，主键、外键、字段注释等约束不会同步，若需保留需额外编写逻辑处理。
• 性能优化：若视图数量较多，可添加事务控制或分批执行逻辑，避免一次性占用过多资源。

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