Azure Synapse专用SQL池Polybase/COPY加载数据时如何获取源文件名

首先明确报错根因：你用到的带filename()方法的OPENROWSET语法仅适用于Synapse无服务器SQL池，专用SQL池（原Azure SQL Data Warehouse）的PolyBase、原生COPY INTO功能均未内置类似的自动提取全文件名的内置函数，可通过以下三种生产可用方案实现需求：

方案1：循环逐文件加载+显式注入文件名常量（推荐，适配所有场景） #

这是生产环境最通用的方案，无路径规则限制，能100%准确拿到每个文件的完整名称：

• 先通过Synapse管道/ADF的Get Metadata活动、存储账户SDK或PowerShell脚本，枚举待加载路径下的所有目标文件列表
• 遍历文件列表逐个执行加载命令，加载时将当前文件名作为常量列写入目标表，COPY INTO示例代码如下：

COPY INTO dbo.YourTargetTable
(
    -- 对应业务数据列
    Col1 VARCHAR(MAX),
    Col2 INT,
    Col3 DATETIME2,
    -- 最后一列为存储文件名的目标列，直接映射为常量值
    SourceFileName VARCHAR(1000) DEFAULT 'your_current_loading_file_name.tsv.gz'
)
FROM 'https://<your-storage-account>.blob.core.windows.net/<container>/2022/06/21/your_current_loading_file_name.tsv.gz'
WITH (
    FILE_FORMAT = [CompressedTSV],
    CREDENTIAL = (IDENTITY = 'Managed Identity')
);

如果用PolyBase的话，也可以在INSERT INTO ... SELECT的时候直接把文件名作为常量加在SELECT子句里，逻辑完全一致。

方案2：PolyBase外部表+filepath函数提取路径段（适合目录规则固定的场景） #

如果你的文件存储路径有固定的分层规则，可以利用PolyBase支持的filepath函数提取路径分段，再截取得到文件名：

1. 创建外部表时LOCATION指向存储的根目录，而非具体子文件夹
2. 查询时通过filepath(n)获取第n层路径的内容，再通过字符串截取拿到文件名
   示例代码：

-- 先创建指向容器根路径的外部表
CREATE EXTERNAL TABLE ext.RawData
(
    Col1 VARCHAR(MAX),
    Col2 INT,
    Col3 DATETIME2
)
WITH (
    LOCATION = '/',
    DATA_SOURCE = [Analytics_AzureStorage],
    FILE_FORMAT = [CompressedTSV]
);

-- 查询时截取路径段得到文件名，以下示例路径层级为 /年/月/日/文件名
SELECT
    *,
    -- 截取第4层路径（索引从1开始）中最后一个/后的内容即为文件名
    RIGHT(filepath(4), CHARINDEX('/', REVERSE(filepath(4))) - 1) AS SourceFileName
FROM ext.RawData
-- 按路径过滤目标日期分区
WHERE filepath(1) = '2022'
  AND filepath(2) = '06'
  AND filepath(3) = '21'

该方案限制：必须严格统一文件存储的目录层级，否则会出现文件名截取错误，无法适配路径层级不固定的文件存储结构。

方案3：加载前预处理注入文件名字段 #

如果不想在SQL层做逻辑处理，可以在数据上传到Blob的环节，或者通过Synapse数据流、Databricks等计算引擎提前给每一行数据追加SourceFileName字段，后续直接通过PolyBase/COPY INTO加载即可，不需要在写入SQL池时做额外处理，兼容性最好。

避坑提示：所有公开文档中提到的OPENROWSET搭配filename()函数的用法，全部是针对Synapse无服务器SQL池的，在专用SQL池执行必然报语法错误，没有兼容版本。

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