Snowflake批量加载非规则文件：将文件名写入目标列的方案咨询

方案1：使用COPY INTO的FILES参数批量指定文件 #

COPY INTO的FILES参数支持直接指定多个文件名，无论命名规则是否统一，结合元数据字段METADATA$FILENAME即可批量加载同时记录文件名：

COPY INTO MYTABLE(FILENAME, FILE_ROW_NUMBER, COL1, COL2)
FROM (SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, T.$1, T.$2
      FROM @MYSTAGE(FILE_FORMAT => MYFORMAT, FILES => ('file_abc.csv', 'xzy_123.txt', 'random_name.dat')) T);

若文件数量较多，可通过LIST @MYSTAGE命令导出所有文件名，再用脚本（如Python、SnowSQL）自动生成包含所有文件名的FILES参数列表，避免手动输入，提升效率。

方案2：利用外部表实现自动批量加载 #

外部表可自动关联阶段中的所有文件，无需手动指定文件名，且能直接获取元数据字段，适合无规则命名的批量场景：

1. 创建外部表关联阶段：

CREATE OR REPLACE EXTERNAL TABLE MY_EXTERNAL_TABLE
(COL1 VARCHAR, COL2 INT)
WITH LOCATION = @MYSTAGE
FILE_FORMAT = MYFORMAT;

2. 插入数据到目标表并携带文件名信息：

INSERT INTO MYTABLE(FILENAME, FILE_ROW_NUMBER, COL1, COL2)
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, COL1, COL2
FROM MY_EXTERNAL_TABLE;

该方案无需筛选文件，外部表会自动遍历阶段内所有文件，加载效率更高。

方案3：动态生成COPY INTO语句（脚本化处理） #

若偏好使用COPY INTO，可通过脚本自动生成包含所有目标文件的SQL语句，示例SnowSQL脚本：

# 生成COPY INTO语句开头
!echo "COPY INTO MYTABLE(FILENAME, FILE_ROW_NUMBER, COL1, COL2) FROM (SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, T.$1, T.$2 FROM @MYSTAGE(FILE_FORMAT => MYFORMAT, FILES => ('" > batch_copy.sql

# 导出阶段文件列表并处理成逗号分隔的文件名
!LIST @MYSTAGE | awk '{print $1}' | tr '\n' ',' | sed 's/,$//' >> batch_copy.sql

# 补全语句结尾
!echo "')) T);" >> batch_copy.sql

# 执行生成的SQL
!snowsql -f batch_copy.sql

此方法自动化处理大量文件，避免手动筛选的低效问题。

方案4：阶段路径分区优化（可选） #

若文件虽命名无规则，但存储在阶段的分区路径下（如按日期划分的子路径），可通过指定路径前缀筛选文件：

COPY INTO MYTABLE(FILENAME, FILE_ROW_NUMBER, COL1, COL2)
FROM (SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, T.$1, T.$2
      FROM @MYSTAGE/2024_05_/ (FILE_FORMAT => MYFORMAT) T);

该方案仅适用于路径有规律的场景，能快速缩小加载范围。

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