Snowflake存储过程遍历S3 CSV文件至同名表时遇语法错误求助

一、语法错误修复 #

原代码存在三个核心问题：

1. FOR循环语法错误：Snowflake SQL存储过程中，循环范围需用..而非to
2. 变量赋值方式错误：SQL存储过程中应使用:=赋值，或用LET声明变量
3. 静态SQL无法解析变量：COPY INTO中的表名、文件名是变量，必须用动态SQL执行

修复后的代码：

CREATE OR REPLACE PROCEDURE load_data_S3()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    file_name VARCHAR;
    table_name VARCHAR;
BEGIN
    FOR i IN 1..2 LOOP
        CASE i
            WHEN 1 THEN
                file_name := 'file1.csv';
                table_name := 'FILE1';
            WHEN 2 THEN
                file_name := 'file2.csv';
                table_name := 'FILE2';
            -- 后续可继续添加WHEN分支
            ELSE
                -- 无操作
        END CASE;
        
        -- 用动态SQL替换变量并执行
        EXECUTE IMMEDIATE 'COPY INTO ' || table_name || ' FROM @externalstg/' || file_name || ' FILE_FORMAT = (type=''csv'')';
    END LOOP;
    RETURN 'Data loaded successfully';
END;
$$;

二、批量加载优化方案（无需逐一列出125个文件） #

方案1：利用文件名与表名的规律自动生成 #

如果文件名和表名有固定规律（比如fileN.csv对应表FILEN），直接通过循环生成映射关系：

CREATE OR REPLACE PROCEDURE load_data_S3_batch()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    file_name VARCHAR;
    table_name VARCHAR;
    total_files INT := 125; -- 总文件数
BEGIN
    FOR i IN 1..total_files LOOP
        -- 生成文件名
        file_name := 'file' || i || '.csv';
        -- 生成对应表名
        table_name := 'FILE' || i;
        
        EXECUTE IMMEDIATE 'COPY INTO ' || table_name || ' FROM @externalstg/' || file_name || ' FILE_FORMAT = (type=''csv'')';
    END LOOP;
    RETURN 'All data loaded successfully';
END;
$$;

方案2：通过配置表管理文件与表的映射 #

如果文件名和表名无规律，先创建配置表存储映射关系，再遍历表完成加载：

1. 创建配置表并导入映射数据：

CREATE OR REPLACE TABLE FILE_TABLE_MAPPING (
    FILE_NAME VARCHAR,
    TABLE_NAME VARCHAR
);
-- 批量插入125条映射数据，可通过批量导入或脚本生成
INSERT INTO FILE_TABLE_MAPPING VALUES
('file_a.csv', 'TABLE_A'),
('file_b.csv', 'TABLE_B'),
-- 剩余123条映射
;

2. 编写存储过程遍历配置表：

CREATE OR REPLACE PROCEDURE load_data_S3_from_config()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT FILE_NAME, TABLE_NAME FROM FILE_TABLE_MAPPING LOOP
        EXECUTE IMMEDIATE 'COPY INTO ' || rec.TABLE_NAME || ' FROM @externalstg/' || rec.FILE_NAME || ' FILE_FORMAT = (type=''csv'')';
    END LOOP;
    RETURN 'All data loaded from config successfully';
END;
$$;

方案3：自动获取Stage中的文件并映射表名 #

如果所有目标CSV文件都在@externalstg下，且文件名去掉后缀就是表名，可自动获取文件列表完成加载：

CREATE OR REPLACE PROCEDURE load_data_S3_auto_detect()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    file_rec RECORD;
    file_name VARCHAR;
    table_name VARCHAR;
BEGIN
    -- 获取Stage中的所有CSV文件
    FOR file_rec IN 
        SELECT RELATIVE_PATH FROM TABLE( LIST(@externalstg) ) 
        WHERE RELATIVE_PATH LIKE '%.csv'
    LOOP
        file_name := file_rec.RELATIVE_PATH;
        -- 去掉.csv后缀并转为大写作为表名（可按需调整规则）
        table_name := UPPER( REPLACE(file_name, '.csv', '') );
        
        EXECUTE IMMEDIATE 'COPY INTO ' || table_name || ' FROM @externalstg/' || file_name || ' FILE_FORMAT = (type=''csv'')';
    END LOOP;
    RETURN 'All CSV files loaded successfully';
END;
$$;

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