如何从Snowflake中仅查询AWS S3存储桶前缀？解决ls命令报错

需要从Snowflake关联的AWS S3存储桶中提取一级前缀列表（例如从S3://Bucket1/test1/file1.csv、S3://Bucket1/test2/file1.csv这类路径中提取test1、test2、test3），但使用ls命令时因文件量过大触发报错：

Total size (>=1,073,742,040 bytes) for the list of file descriptors returned from the stage exceeded limit (1,073,741,824 bytes); Number of file descriptors returned is >=4,329,605. Please use a prefix in the stage location or pattern option to reduce the number of files.

寻求更优雅的实现方法，当前考虑使用目录表。

一、使用目录表（推荐） #

目录表是Snowflake针对大规模存储场景优化的元数据管理工具，能高效存储和查询S3的目录结构，完全规避ls命令的文件数量限制：

1. 创建目录表
   确保已创建指向目标S3桶的外部阶段（示例阶段名为MY_S3_STAGE），执行SQL创建目录表：

   CREATE OR REPLACE DIRECTORY TABLE MY_S3_DIR_TABLE
   FOR STAGE MY_S3_STAGE;
   

2. 同步元数据
   将S3存储的最新结构同步到目录表：

   ALTER DIRECTORY TABLE MY_S3_DIR_TABLE REFRESH;
   

3. 提取前缀
   通过解析目录表的RELATIVE_PATH字段，去重获取所有一级前缀：

   SELECT DISTINCT SPLIT_PART(RELATIVE_PATH, '/', 1) AS prefix
   FROM MY_S3_DIR_TABLE
   WHERE RELATIVE_PATH LIKE '%/%' -- 过滤根目录下无前缀的文件
   AND SPLIT_PART(RELATIVE_PATH, '/', 1) != ''; -- 排除空值
   

二、临时查询技巧（限小体量场景） #

如果只是临时查询且不想创建目录表，可利用S3的前缀特性直接查询目录项：

ls @MY_S3_STAGE pattern='*/';

该命令会返回所有一级前缀的目录条目，无需遍历所有文件，能减少返回的描述符数量，但前缀数量过大时仍可能触发限制。

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