Snowflake查询Azure ADLS Gen2 Stage时metadata$filename重复返回问题

根因分析 #

你遇到的重复返回文件名问题并非Snowflake Bug，是查询Stage时的默认行为导致：
直接对Stage执行SELECT查询时，Snowflake会默认使用当前会话的默认文件格式（通常为CSV格式）解析匹配到的文件内容，文件内每识别到一行数据就返回一条结果，每条结果都会携带对应文件的metadata$filename属性。
你匹配的是PNG格式的二进制文件，默认CSV解析逻辑会将二进制流中的特殊字符错误识别为行分隔符，最终将1个PNG文件拆分为562行记录，因此返回了562次相同的文件名。
LIST命令仅枚举Stage内的文件元数据，不会解析文件内容，所以可以正常返回1条匹配结果。

特殊现象解释 #

你使用UNION语句返回预期结果属于巧合：UNION操作会自动对合并后的结果集执行去重，562条相同的文件名记录会被合并为1条，因此最终返回正则表达式值+1条文件名的预期结果，换成UNION ALL就会复现563条记录的问题。

修复方案 #

方案1：查询时增加去重逻辑 #

仅需要获取匹配文件名的场景，直接对metadata$filename加DISTINCT去重即可，适配所有文件类型：

SELECT DISTINCT
    metadata$filename
FROM
    @dev_silver_db.common.stage_bronze/DEV/BRONZE/<CENSORED>/S21/2715147 (
        PATTERN => $pattern_png         
    )
    AS t

方案2：指定二进制文件格式解析 #

针对二进制文件查询场景，提前创建二进制类型的文件格式，查询时指定该格式，避免文件内容被错误拆分：

1. 先创建二进制文件格式（仅需执行一次）

CREATE FILE FORMAT IF NOT EXISTS common.binary_format 
TYPE = BINARY;

2. 查询时指定文件格式

SELECT
    metadata$filename
FROM
    @dev_silver_db.common.stage_bronze/DEV/BRONZE/<CENSORED>/S21/2715147 (
        PATTERN => $pattern_png,
        FILE_FORMAT => 'common.binary_format'
    )
    AS t

方案3：使用元数据表枚举文件 #

不需要读取文件内容的场景，直接查询Snowflake内置的Stage目录元数据表，性能更高且不会触发文件内容解析：

SELECT RELATIVE_PATH AS filename
FROM TABLE(INFORMATION_SCHEMA.STAGE_DIRECTORY_FILES(
    STAGE_NAME => 'dev_silver_db.common.stage_bronze',
    RELATIVE_PATH => 'DEV/BRONZE/<CENSORED>/S21/2715147',
    PATTERN => $pattern_png
))

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