Azure Synapse SQL如何将文件分区路径作为查询列？

我在Blob存储容器中存储了按如下路径组织的Parquet文件：https://<my_blob_storage_accnt>.dfs.core.windows.net/current-snapshot/businesspartner=P1、https://<my_blob_storage_accnt>.dfs.core.windows.net/current-snapshot/businesspartner=P2等。

当我使用OPENROWSET在Synapse SQL中读取这些文件时（代码如下），结果表中没有businesspartner列。而在Spark/PySpark中，我可以通过设置basePath参数将其作为列读取（代码如下）。我查阅了相关文档，找到的方法（如使用filepath()或显式列出分区路径）对SQL使用者不够直观。请问是否有OPENROWSET参数或其他简洁方法，能将所有分区路径作为结果列纳入，以便后续用WHERE过滤？

Synapse SQL读取代码 #

SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://<my_blob_storage_accnt>.dfs.core.windows.net/current-snapshot/**',
        FORMAT = 'PARQUET'
    ) AS [result]

Spark/PySpark实现代码 #

val df = sparkSession.read
  .option("basePath", path)
  .parquet(path + "/businesspartner=*/*.parquet")

目前Synapse SQL的OPENROWSET并没有像Spark的basePath那样直接自动解析分区路径为列的参数，但可以通过以下两种相对简洁的方式实现需求：

1. 基于filepath()的字符串提取（临时查询场景） #

虽然需要字符串处理，但可以封装成可复用的逻辑，直接在查询中提取分区列：

SELECT
    TOP 100
    *,
    -- 从文件路径中提取businesspartner的值
    SUBSTRING(
        filepath(),
        CHARINDEX('businesspartner=', filepath()) + LEN('businesspartner='),
        CHARINDEX('/', filepath(), CHARINDEX('businesspartner=', filepath())) - (CHARINDEX('businesspartner=', filepath()) + LEN('businesspartner='))
    ) AS businesspartner
FROM
    OPENROWSET(
        BULK 'https://<my_blob_storage_accnt>.dfs.core.windows.net/current-snapshot/**',
        FORMAT = 'PARQUET'
    ) AS [result]

如果存在多级分区，只需按相同逻辑依次提取每个分区键的值即可。

2. 创建外部表（重复查询场景推荐） #

如果需要多次查询该数据集，创建外部表是更符合SQL使用习惯的方案，Synapse会自动识别并映射分区列：

步骤1：创建外部数据源 #

CREATE EXTERNAL DATA SOURCE BlobStorage
WITH (
    LOCATION = 'https://<my_blob_storage_accnt>.dfs.core.windows.net/current-snapshot/',
    TYPE = HADOOP
);

步骤2：创建外部文件格式 #

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

步骤3：创建外部表 #

CREATE EXTERNAL TABLE dbo.BusinessPartnerData (
    -- 列出Parquet文件内部的所有列
    col1 INT,
    col2 VARCHAR(100),
    -- 其他列...
)
WITH (
    LOCATION = '/',
    DATA_SOURCE = BlobStorage,
    FILE_FORMAT = ParquetFormat,
    -- 显式指定分区列，Synapse会自动匹配路径中的分区键
    PARTITION (businesspartner VARCHAR(100))
);

之后查询时可直接使用businesspartner列过滤，且分区过滤会自动下推到存储层，提升性能：

SELECT TOP 100 * FROM dbo.BusinessPartnerData WHERE businesspartner = 'P1';

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