如何通过Synapse无服务器SQL高效获取Azure数据湖唯一文件夹名

需要通过Synapse无服务器SQL从Azure数据湖中获取唯一文件夹名列表，数据湖结构为device/message/yyyy/mm/dd/xyz.parquet。

已写出的可行查询如下：

SELECT DISTINCT
    r.filepath(1) AS deviceid
FROM
    OPENROWSET(
        BULK 'https://xyz.dfs.core.windows.net/xyzdatalakestoragegen2filesystem/*/*/*/*/*/*',
        FORMAT = 'PARQUET'
    ) as r

当前问题：该查询会扫描整个数据湖以返回唯一列表，效率极低；且无法预先知晓数据湖的具体路径/文件信息，查询需适配多个同结构的数据湖。需要可使用TSQL实现的高效优化技巧。

1. 限定扫描层级，仅读取目录元数据 #

调整OPENROWSET的BULK路径，只扫描到device层级的目录，避免深入到后续的文件层级，无需加载任何Parquet文件内容，仅读取存储的目录元数据：

SELECT DISTINCT
    r.filepath(1) AS deviceid
FROM
    OPENROWSET(
        BULK 'https://<storage-account>.dfs.core.windows.net/<container>/device/*/',
        FORMAT = 'PARQUET'
    ) AS r

路径中的device/*/表示仅扫描device下的所有直接子目录，也就是各个deviceid对应的文件夹，大幅减少扫描范围。

2. 借助系统视图查询外部数据源元数据 #

如果可以预先注册外部数据源，直接查询系统视图获取路径元数据，完全跳过文件扫描，性能最优。适配不同数据湖时，只需修改外部数据源的存储地址：

-- 注册外部数据源（不同数据湖只需修改LOCATION）
CREATE EXTERNAL DATA SOURCE TargetDataLake
WITH (
    LOCATION = 'https://<storage-account>.dfs.core.windows.net/<container>/',
    TYPE = HADOOP
);

-- 提取唯一deviceid
SELECT DISTINCT
    SUBSTRING(file_location, LEN(eds.LOCATION) + 8, CHARINDEX('/message/', file_location) - LEN(eds.LOCATION) - 8) AS deviceid
FROM
    sys.external_file_locations efl
JOIN
    sys.external_data_sources eds ON efl.data_source_id = eds.data_source_id
WHERE
    eds.name = 'TargetDataLake'
    AND file_location LIKE '%device/%/message/%'

此方式直接读取存储的元数据记录，无需触发任何文件内容扫描，效率最高。

3. 用GROUP BY替代DISTINCT降低内存开销 #

当待处理的目录数量较多时，GROUP BY比DISTINCT的内存利用率更高，可在扫描过程中逐步完成聚合：

SELECT
    r.filepath(1) AS deviceid
FROM
    OPENROWSET(
        BULK 'https://<storage-account>.dfs.core.windows.net/<container>/device/*/',
        FORMAT = 'PARQUET'
    ) AS r
GROUP BY
    r.filepath(1)

4. 过滤路径结构精准定位目标目录 #

通过FILEPATH()函数的过滤条件，确保只扫描符合结构的路径，避免无关目录干扰：

SELECT DISTINCT
    r.filepath(1) AS deviceid
FROM
    OPENROWSET(
        BULK 'https://<storage-account>.dfs.core.windows.net/<container>/*/',
        FORMAT = 'PARQUET'
    ) AS r
WHERE
    r.filepath() LIKE 'device/%/message/%'

此方法适用于无法提前确定device层级路径的场景，通过过滤条件精准匹配目标结构，减少无效扫描。

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