Azure Synapse Analytics外部表数据库每日刷新后文件路径变更致查询报错的非流水线解决方案咨询

我来帮你分析这个问题并给出不用流水线的解决方案：

你的核心问题在于创建外部表时，OPENROWSET的BULK参数硬编码了单个特定文件名，而源目录SomeFileName/下每天会生成带动态GUID和数字后缀的新CSV文件，导致第二天这个路径失效。下面是几个可行的非流水线解决思路：

方法1：用通配符匹配目录下所有目标CSV文件 #

Synapse的OPENROWSET支持在BULK路径中使用通配符（比如*.csv），直接扫描目标目录下的所有CSV文件，无需关注具体文件名。这样不管每天生成的文件名是什么，只要在指定目录下就能被读取，再通过你的WHERE Reference IN (...)过滤出需要的记录。

修改后的代码示例：

CREATE OR ALTER EXTERNAL TABLE reports.FileIamCreating
WITH
(
    LOCATION ='/reports/CreatedFleName/',
    DATA_SOURCE = AzureDataLakeStorageGen2,
    FILE_FORMAT = csv
)
AS
SELECT
    Reference,
    CAST('250.00' AS FLOAT) AS [Invoiced costs]
FROM
OPENROWSET(
    BULK 'https://XName.dfs.core.windows.net/abc/tables/SomeFileName/*.csv', -- 用*.csv匹配目录下所有CSV
    FORMAT = 'CSV',
    HEADER_ROW = TRUE,
    PARSER_VERSION = '2.0'
) AS tbl1
WHERE Reference IN (
    '100725',
    '107575',
    '107707',
    '108771'
)

注意事项： #

如果目标目录下会留存旧的CSV文件，要确保这些旧文件中不会包含你需要过滤的Reference值（否则会重复读取数据）；如果旧文件有重复数据，可以考虑在生成新文件后自动归档旧文件，或者在查询时结合FILEPATH()函数过滤最新文件（比如WHERE FILEPATH() LIKE '%_当天日期相关的后缀%'，如果后缀里包含日期特征的话）。

方法2：动态SQL+存储过程，只读取最新文件 #

如果你希望精准读取每天生成的最新单个文件，可以用动态SQL结合系统视图获取目录下的最新文件路径，再自动生成外部表。这种方法无需流水线，只需手动执行存储过程（或用Synapse的SQL Agent作业自动执行）。

步骤1：创建指向目标目录的外部数据源（如果还没有） #

CREATE EXTERNAL DATA SOURCE SomeFileName_DS
WITH (
    LOCATION = 'https://XName.dfs.core.windows.net/abc/tables/SomeFileName/',
    TYPE = HADOOP,
    CREDENTIAL = -- 这里如果需要权限的话，填写你的ADLS访问凭证，比如SAS或MSI
);

步骤2：创建存储过程自动生成外部表 #

CREATE PROCEDURE Refresh_Reports_FileIamCreating
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 获取SomeFileName目录下的最新CSV文件路径
    DECLARE @LatestFilePath NVARCHAR(1000);
    SELECT TOP 1 @LatestFilePath = file_path
    FROM sys.external_files
    WHERE data_source_id = (SELECT data_source_id FROM sys.external_data_sources WHERE name = 'SomeFileName_DS')
      AND file_path LIKE '%.csv' -- 只筛选CSV文件
    ORDER BY create_time DESC; -- 按创建时间倒序，取最新的

    -- 2. 动态拼接CREATE EXTERNAL TABLE语句
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'
    DROP EXTERNAL TABLE IF EXISTS reports.FileIamCreating;
    CREATE EXTERNAL TABLE reports.FileIamCreating
    WITH
    (
        LOCATION =''/reports/CreatedFleName/'',
        DATA_SOURCE = AzureDataLakeStorageGen2,
        FILE_FORMAT = csv
    )
    AS
    SELECT
        Reference,
        CAST(''250.00'' AS FLOAT) AS [Invoiced costs]
    FROM
    OPENROWSET(
        BULK ''' + @LatestFilePath + ''',
        FORMAT = ''CSV'',
        HEADER_ROW = TRUE,
        PARSER_VERSION = ''2.0''
    ) AS tbl1
    WHERE Reference IN (
        ''100725'',
        ''107575'',
        ''107707'',
        ''108771''
    )';

    -- 3. 执行动态SQL
    EXEC sp_executesql @SQL;
END

使用方式： #

每天只需执行EXEC Refresh_Reports_FileIamCreating;，就能自动基于最新文件重建外部表，避免路径失效的问题。如果需要自动执行，可以在Synapse中创建SQL Agent作业，设置每天定时运行这个存储过程（这不属于Data Factory流水线，是Synapse自身的SQL自动化能力）。

方法3：直接从目录级外部表查询 #

你也可以先创建一个指向SomeFileName目录的外部表，然后从这个外部表中查询数据来生成你的目标外部表。这样就彻底摆脱了单个文件的路径限制：

-- 先创建指向源目录的外部表
CREATE EXTERNAL TABLE staging.SomeFileName_Source
WITH
(
    LOCATION = '/abc/tables/SomeFileName/', -- 对应ADLS的目录路径
    DATA_SOURCE = AzureDataLakeStorageGen2,
    FILE_FORMAT = csv,
    PARSER_VERSION = '2.0'
);

-- 再创建你的目标外部表
CREATE OR ALTER EXTERNAL TABLE reports.FileIamCreating
WITH
(
    LOCATION ='/reports/CreatedFleName/',
    DATA_SOURCE = AzureDataLakeStorageGen2,
    FILE_FORMAT = csv
)
AS
SELECT
    Reference,
    CAST('250.00' AS FLOAT) AS [Invoiced costs]
FROM staging.SomeFileName_Source
WHERE Reference IN (
    '100725',
    '107575',
    '107707',
    '108771'
);

这种方式下，staging.SomeFileName_Source会自动扫描目录下的所有CSV文件，每天生成新文件后，查询这个外部表就能自动读取最新数据，无需修改任何路径。

以上几种方法都不需要用到Azure Data Factory流水线，你可以根据自己的需求选择最适合的方案~

备注：内容来源于stack exchange，提问作者MariaT