优化Snowflake的COPY INTO命令：减少外部文件列表耗时

我正在优化Snowflake的COPY INTO命令，用于从ADLS Gen2外部阶段加载数据到Snowflake。ADLS的文件夹结构如下：

YYYY-MM-DDTHH24.MI.SSZ/METADATA/<TABLE>/<TABLE>.json
YYYY-MM-DDTHH24.MI.SSZ/<TABLE>/<TABLE>.csv

因为文件格式不同，我用两个独立的COPY INTO命令分别加载到两张独立的表中。

已尝试的优化措施 #

• 指定阶段路径缩小扫描范围到特定日期：

  COPY INTO TABLE
  FROM ( SQL )
  @EXT_STAGE/2024-08-01
  PATTERN = 'T[0-9]{2}\.[0-9]{2}\.[0-9]{2}Z/.*\.json$'
  

  但耗时依然很长，ADLS几乎每10分钟就会生成一个新文件夹。
• 测试不同的PATTERN规则：

  PATTERN = 'T[0-9]{2}\.[0-9]{2}\.[0-9]{2}Z/.*\.json$' -- 效果最佳
  PATTERN = '.*T[0-9]{2}\.[0-9]{2}\.[0-9]{2}Z/.*\.json$' -- 效果次之
  PATTERN = '.*\.json$' -- 效果第三
  

  不过各PATTERN之间的耗时差异很小。

执行统计数据 #

处理100k个文件耗时约12分钟
外部阶段列表耗时：5分钟
数据处理耗时：7分钟

插入行数：94479
扫描进度：100.00%
扫描外部字节数：2.35GB
写入字节数：17.10MB

仅扫描约2GB数据、写入17MB数据，但整体执行耗时偏高，目前无法对文件进行压缩（不符合业务要求），求问是否可以通过优化PATTERN或其他方式提升执行效率。

优化建议 #

1. 更精准的路径过滤替代PATTERN #

既然已经指定了日期路径@EXT_STAGE/2024-08-01，可以进一步把路径细化到METADATA下的具体表目录，直接定位目标文件所在层级，减少Snowflake需要扫描的文件夹数量：

COPY INTO TABLE
FROM ( SQL )
@EXT_STAGE/2024-08-01/METADATA/<TABLE>
PATTERN = '.*\.json$'

这种方式无需再匹配时间文件夹，直接进入目标目录，能大幅降低列表阶段的耗时。

2. 利用分区元数据加速扫描 #

基于ADLS的时间分区结构，在外部阶段定义时添加PARTITION_BY参数，让Snowflake利用分区元数据快速定位目标文件，避免全量扫描：

CREATE OR REPLACE STAGE EXT_STAGE
URL = 'abfss://container@account.dfs.core.windows.net/path'
STORAGE_INTEGRATION = ADLS_INT
FILE_FORMAT = (TYPE = JSON)
PARTITION_BY = (DATE_TRUNC('HOUR', TO_TIMESTAMP_NTZ(REPLACE(REPLACE(SPLIT_PART(RELATIVE_PATH, '/', 1), 'T', ' '), '.', ':'))));

之后在COPY INTO中直接通过分区过滤数据，比如只加载某小时的数据：

COPY INTO TABLE
FROM ( SQL )
@EXT_STAGE
WHERE $1::DATE = '2024-08-01' AND $2::HOUR = 10;

3. 调整COPY INTO的并行度参数 #

尝试增大MAX_FILES_PER_LOAD或MAX_CONCURRENCY_LEVEL参数，提升文件处理的并行度（需根据仓库大小调整，避免资源过载）：

COPY INTO TABLE
FROM ( SQL )
@EXT_STAGE/2024-08-01
PATTERN = 'T[0-9]{2}\.[0-9]{2}\.[0-9]{2}Z/METADATA/<TABLE>/.*\.json$'
MAX_FILES_PER_LOAD = 5000
MAX_CONCURRENCY_LEVEL = 100;

4. 预生成文件清单分离列表与加载 #

先通过LIST命令获取符合条件的文件路径并保存到临时表，再让COPY INTO直接加载这些文件，跳过阶段列表的耗时：

-- 1. 获取目标文件路径
CREATE OR REPLACE TEMPORARY TABLE FILE_PATHS AS
SELECT RELATIVE_PATH
FROM TABLE(LIST(@EXT_STAGE/2024-08-01, PATTERN => 'T[0-9]{2}\.[0-9]{2}\.[0-9]{2}Z/METADATA/<TABLE>/.*\.json$'));

-- 2. 从指定文件加载
COPY INTO TABLE
FROM ( SELECT $1 FROM @EXT_STAGE/RELATIVE_PATH )
(SELECT RELATIVE_PATH FROM FILE_PATHS);

该方式适合文件数量较多的场景，利用Snowflake批量处理能力分离列表与加载操作。

5. 临时升级仓库大小 #

如果当前使用小仓库，可临时升级到更大规格（如从XS升级到L），提升扫描和处理的并行能力，完成加载后再切回原仓库，平衡成本与效率。

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