SQL Server PolyBase外部表插入数据如何写入单个指定文件

在SQL Server中创建指向Azure Blob存储的PolyBase外部表后，执行INSERT语句可以正常导出数据，但每次插入都会自动生成新文件，无法写入单个目标文件，也无法在插入时指定文件名。

现有配置与报错 #

当前使用的外部表创建语句：

CREATE EXTERNAL TABLE archive.filetransferauditlog (
    [id] [int]  NULL,
    [STATUS] [varchar](10)  NULL,
    [EVENT] [varchar](10)  NULL,
    [fileNameWithPath] [varchar](2048) NULL,
    [eventStartDate] [datetime] NOT NULL,
    [eventEndDate] [datetime] NOT NULL,
    [description] [varchar](4096) NULL,
    [loggedInUserId] [int] NULL,
    [transferType] [int] NULL
    )
    WITH (
    LOCATION = '/filetransferauditlog/',
    DATA_SOURCE = archivepurgedataExternalDataSource,
    FILE_FORMAT = ParquetFile
    )
GO

使用的插入语句：

INSERT INTO archive.filetransferauditlog
SELECT TOP(5) *
FROM dbo.filetransferauditlog

测试将LOCATION参数改为单个文件路径时，SELECT查询可正常读取，但插入操作报错如下：

java.sql.SQLException: Cannot execute the query "Remote Query" against OLE DB provider "SQLNCLI11" for linked server "SQLNCLI11". CREATE EXTERNAL TABLE AS SELECT statement failed as the path name 'wasbs://demoarchive@testarchivedemo.blob.core.windows.net/filetransferauditlogText/QID5060_20220607_54101_0.txt' could not be used for export. Please ensure that the specified path is a directory which exists or can be created, and that files can be created in that directory.

PolyBase导出存在两个无法通过参数调整绕过的原生限制：

• 导出目标必须是可写目录，不支持直接指定单个文件作为导出目标
• 每次导出都会在目标目录下生成引擎自动命名的新文件，不支持自定义导出文件名，也不支持向已有文件追加写入

方案1：导出后自动合并重命名（兼容性最好，推荐生产环境使用） #

操作流程：

• 为PolyBase导出配置专用的临时空目录作为外部表LOCATION，不要直接使用最终文件的存储目录
• 每次执行INSERT导出完成后，通过Blob侧的操作将临时目录下PolyBase生成的所有分片文件合并为单个文件，按需求重命名后移动到最终存储路径，最后清空临时导出目录
  可选的落地方式：
• 用SSIS的Azure Blob存储任务编排合并、重命名、清空的全流程，和现有SQL导出任务做依赖绑定
• 用Azure自动化的PowerShell脚本监听临时目录的文件生成事件，自动触发合并重命名逻辑
• SQL Server 2022及以上版本可直接使用sp_invoke_external_rest_endpoint存储过程调用Blob存储的块Blob提交接口，在T-SQL流程内完成文件合并，无需依赖外部调度工具

方案2：CETAS导出+视图封装（适合只读查询场景） #

操作流程：

• 不复用同一个外部表做多次插入，每次需要导出数据时，使用CREATE EXTERNAL TABLE AS SELECT（CETAS）语法，指定一个独立的空目录作为本次导出的LOCATION
• 导出完成后可按需对该目录下的文件做合并重命名
• 如果需要统一查询入口，可在SQL Server层创建视图，将所有导出目录对应的外部表做UNION ALL封装，对使用方屏蔽多目录、多文件的存储细节

注意：该方案无法实现存储层的单文件追加，仅能在查询层实现逻辑聚合

方案3：替换导出链路（适合必须原生写单文件、无后置处理的场景） #

如果不希望在导出后增加文件处理步骤，可以放弃PolyBase导出，改用支持指定单文件、支持追加写入的导出方式：

• 使用SSIS的Azure Blob目标组件，直接配置写入指定的单个Blob文件，开启追加写入选项
• 小数据量场景可使用sp_execute_external_script调用Python/R脚本，从本地表读取数据后直接上传到指定的Blob路径
• 在应用层实现逻辑：从SQL Server读取待导出数据后，直接通过Blob SDK写入指定的单个目标文件，支持追加操作

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