如何获取Snowflake Azure外部阶段指定路径子文件夹名并分表存储？

你有Azure外部阶段@mystage，目录结构包含@mystage/nz/atm/INC/和@mystage/nz/drv/INC/下的日期子文件夹，需要分别提取这两个路径下的子文件夹名称并存储到不同的临时表/表中，以下是几种替代你现有方案的实现方式：

方法一：正则匹配替代固定索引拆分 #

避免依赖SPLIT_PART的固定位置索引（路径层级变化时会失效），改用正则表达式精准提取目标文件夹名：

提取atm/INC下的子文件夹 #

CREATE TEMPORARY TABLE atm_inc_folders (folder_name STRING);

INSERT INTO atm_inc_folders
SELECT DISTINCT REGEXP_SUBSTR(name, '@mystage/nz/atm/INC/([^/]+)/?', 1, 1, 'e', 1) AS folder_name
FROM TABLE(RESULT_SCAN(LIST @mystage/nz/atm/INC/));

提取drv/INC下的子文件夹 #

CREATE TEMPORARY TABLE drv_inc_folders (folder_name STRING);

INSERT INTO drv_inc_folders
SELECT DISTINCT REGEXP_SUBSTR(name, '@mystage/nz/drv/INC/([^/]+)/?', 1, 1, 'e', 1) AS folder_name
FROM TABLE(RESULT_SCAN(LIST @mystage/nz/drv/INC/));

说明：正则表达式中的([^/]+)匹配路径中/分隔的目标文件夹，'e'参数指定提取第一个捕获组的内容，路径结构调整时只需修改正则部分即可，灵活性更强。

方法二：使用SCAN_EXTERNAL_STAGE函数（推荐） #

Snowflake的SCAN_EXTERNAL_STAGE函数可直接扫描外部阶段并返回元数据，无需先执行LIST再扫描结果，代码更简洁：

提取atm/INC下的子文件夹 #

CREATE OR REPLACE TEMPORARY TABLE atm_inc_folders AS
SELECT DISTINCT SPLIT_PART(RELATIVE_PATH, '/', 3) AS folder_name
FROM TABLE(SCAN_EXTERNAL_STAGE(
    @mystage,
    PATTERN => 'nz/atm/INC/[^/]+/',
    RECURSIVE => FALSE
));

提取drv/INC下的子文件夹 #

CREATE OR REPLACE TEMPORARY TABLE drv_inc_folders AS
SELECT DISTINCT SPLIT_PART(RELATIVE_PATH, '/', 3) AS folder_name
FROM TABLE(SCAN_EXTERNAL_STAGE(
    @mystage,
    PATTERN => 'nz/drv/INC/[^/]+/',
    RECURSIVE => FALSE
));

说明：PATTERN参数通过正则匹配目标路径下的子文件夹，RECURSIVE => FALSE限制只扫描第一级子目录；RELATIVE_PATH返回相对于阶段根的路径，这里拆分第3部分即可得到目标日期文件夹名称。

方法三：存储过程批量处理（多路径场景适用） #

如果需要处理多个类似路径，可通过存储过程复用逻辑，减少重复代码：

CREATE OR REPLACE PROCEDURE extract_folder_names(stage_subpath STRING, target_table STRING)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    -- 创建目标临时表
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TEMPORARY TABLE ' || target_table || ' (folder_name STRING)';
    
    -- 插入提取的文件夹名
    EXECUTE IMMEDIATE 'INSERT INTO ' || target_table || '
        SELECT DISTINCT REGEXP_SUBSTR(name, ''@mystage/' || stage_subpath || '([^/]+)/?'', 1, 1, ''e'', 1) AS folder_name
        FROM TABLE(RESULT_SCAN(LIST @mystage/' || stage_subpath || '))';
    
    RETURN '提取完成，结果存入表：' || target_table;
END;
$$;

-- 调用存储过程处理atm/INC路径
CALL extract_folder_names('nz/atm/INC/', 'atm_inc_folders');

-- 调用存储过程处理drv/INC路径
CALL extract_folder_names('nz/drv/INC/', 'drv_inc_folders');

说明：存储过程接收阶段子路径和目标表名作为参数，自动完成表创建与数据插入，适合批量处理多个目录的场景。

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