如何将JSON文件及文件名、路径批量加载到Synapse SQL专用池

要解决批量加载JSON内容、文件名和路径的问题，你需要调整OPENROWSET的参数以正确读取整个文件，并正确使用元数据函数。以下是修正后的方案：

1. 确认目标表结构 #

首先确保目标表包含存储JSON内容、文件名和路径的字段：

CREATE TABLE dbo.PolicyStagingJsonOnly (
    jsonContent VARCHAR(MAX),
    fn NVARCHAR(255), -- 存储文件名
    fp NVARCHAR(1024) -- 存储文件路径
);

2. 修正后的加载代码 #

使用SINGLE_CLOB选项读取整个JSON文件为单个字符串，同时通过filename()和filepath()获取元数据：

方式一：使用COPY INTO #

COPY INTO dbo.PolicyStagingJsonOnly (jsonContent, fn, fp)
FROM (
    SELECT 
        CONVERT(VARCHAR(MAX), BulkColumn) AS jsonContent,
        filename() AS fn,
        filepath() AS fp
    FROM OPENROWSET(
        BULK 'https://datalakexxxx.blob.core.windows.net/staging/policy/*.json',
        FORMAT = 'CSV',
        FIELDTERMINATOR = '0x00',
        ROWTERMINATOR = '0x00',
        SINGLE_CLOB
    ) AS [result]
) AS source;

方式二：使用INSERT INTO #

如果更倾向于直接插入，也可以用以下写法：

INSERT INTO dbo.PolicyStagingJsonOnly (jsonContent, fn, fp)
SELECT 
    CONVERT(VARCHAR(MAX), BulkColumn) AS jsonContent,
    filename() AS fn,
    filepath() AS fp
FROM OPENROWSET(
    BULK 'https://datalakexxxx.blob.core.windows.net/staging/policy/*.json',
    FORMAT = 'CSV',
    FIELDTERMINATOR = '0x00',
    ROWTERMINATOR = '0x00',
    SINGLE_CLOB
) AS [result];

关键修正点说明 #

• SINGLE_CLOB：强制将整个JSON文件读取为单个字符大对象，确保每个文件对应一行记录，避免文件内容被拆分。
• FIELDTERMINATOR/ROWTERMINATOR设为0x00：空字符作为分隔符，确保不会将文件内容误拆分为多行。
• filename()和filepath()：这两个函数在Synapse的OPENROWSET BULK操作中原生支持，分别返回当前加载文件的名称和完整路径。

原代码问题分析 #

你之前的代码使用FORMAT='CSV'但未指定SINGLE_CLOB，且分隔符设置为0x0b（垂直制表符），可能导致JSON内容被错误拆分，同时元数据函数无法正确关联到每个文件。

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