如何获取Snowflake stage文件列表并按日期筛选复制到表

不用写JS存储过程，Snowflake本身有原生能力可以直接实现，核心是用Stage自带的目录表（Directory Table）功能，完全绕开LIST命令不能在子查询里调用的限制。

实现步骤 #

• 第一步：给目标Stage开启目录表
  目录表是Snowflake原生维护的Stage元数据表，会自动同步Stage下所有文件的路径、最后修改时间、文件大小、ETag等元数据，支持直接在SQL中查询。
  对已有的Stage执行下面的命令开启即可：

  -- 内部/外部Stage通用，开启目录表+自动刷新元数据
  ALTER STAGE 你的Stage名 
  SET DIRECTORY = (ENABLE = TRUE, AUTO_REFRESH = TRUE);
  
  -- 首次开启后手动刷一次，同步历史文件元数据，后续开了自动刷新不用再手动执行
  ALTER STAGE 你的Stage名 REFRESH;
  

• 第二步：直接用SQL筛选符合要求的文件
  用DIRECTORY()表函数查询Stage目录表，支持按文件名模式、修改时间任意筛选，和普通表查询逻辑完全一致：

  -- 示例：筛选文件名匹配sales/前缀csv压缩文件、最近12小时修改的最新20个文件
  SELECT RELATIVE_PATH AS file_path, LAST_MODIFIED
  FROM DIRECTORY( @你的Stage名 )
  WHERE
    RELATIVE_PATH LIKE 'sales/%.csv.gz' -- 文件名/路径匹配规则和LIST命令完全一致
    AND LAST_MODIFIED >= DATEADD('hour', -12, CURRENT_TIMESTAMP()) -- 按修改时间过滤
  ORDER BY LAST_MODIFIED DESC
  LIMIT 20; -- 取最新的N个文件
  

• 第三步：直接将筛选出的文件复制到目标表
  COPY INTO命令原生支持在WHERE子句中通过METADATA$FILENAME匹配子查询返回的文件列表，不需要拼动态SQL：

  COPY INTO 你的目标表
  FROM (
    SELECT $1, $2, $3, METADATA$FILENAME, METADATA$FILE_ROW_NUMBER
    FROM @你的Stage名
    WHERE METADATA$FILENAME IN (
      -- 这里直接嵌套文件筛选逻辑
      SELECT RELATIVE_PATH
      FROM DIRECTORY( @你的Stage名 )
      WHERE
        RELATIVE_PATH LIKE 'sales/%.csv.gz'
        AND LAST_MODIFIED >= DATEADD('hour', -12, CURRENT_TIMESTAMP())
      ORDER BY LAST_MODIFIED DESC
      LIMIT 20
    )
  )
  FILE_FORMAT = (TYPE = CSV, SKIP_HEADER = 1, COMPRESSION = GZIP);
  

备选方案（不推荐生产用） #

如果暂时无法给Stage开启目录表，可以借助RESULT_SCAN拿到LIST命令的返回结果做筛选：

先执行LIST @你的Stage名 PATTERN = '*.csv.gz';，再通过SELECT "name", "last_modified" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))拿到文件列表做后续筛选。这个方案依赖会话上下文，必须保证LIST和后续查询/复制逻辑在同一个会话内顺序执行，稳定性差，只适合临时手动操作场景。

方案说明 #

• 目录表方案是Snowflake官方原生支持的生产级能力，没有自定义代码维护成本，元数据查询性能远高于反复执行LIST命令
• 支持任意复杂的筛选逻辑，除了修改时间、文件名匹配，还可以按文件大小、ETag等维度过滤
• 可以直接嵌入存储过程、任务、视图等任意Snowflake逻辑组件，没有调用场景限制

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