如何使用OPENROWSET读取Azure Blob存储中的JSON文件列表？

我尝试读取Azure Blob存储中存储的多个嵌套JSON文件（示例文件名：0000001.json、0000002.json等），已创建External Data Source（外部数据源）。目前可通过OPENROWSET结合BULK指定单个文件名成功逐个读取文件，SQL代码示例如下：

SELECT Obj1.*
FROM OPENROWSET(
    BULK '00000002.json', 
    DATA_SOURCE = 'MyDataSource', 
    SINGLE_CLOB
) as DataFile   
CROSS APPLY OPENJSON(BulkColumn) 
...

但尝试使用通配符*.json读取所有文件列表时，执行以下SQL代码：

SELECT Obj1.*
FROM OPENROWSET(
    BULK '*.json', 
    DATA_SOURCE = 'MyDataSource',
    SINGLE_CLOB
) as DIR    

却报错：

Cannot bulk load. The file "*.json" does not exist or you don't have file access rights.

已确认使用的SAS密钥具备完整的列表和读取权限，排除权限问题，请问如何读取该存储中的JSON文件列表？

要批量读取Azure Blob存储中的所有JSON文件，不能直接在OPENROWSET(BULK)中搭配SINGLE_CLOB使用*.json通配符，以下是几种可行的解决方法：

方法1：利用OPENROWSET的CSV格式批量读取（推荐，无需额外配置） #

通过指定FORMAT = 'CSV'并设置空的字段/行分隔符，让SQL Server批量加载所有JSON文件，再解析内容：

SELECT Obj1.*
FROM OPENROWSET(
    BULK '*.json', 
    DATA_SOURCE = 'MyDataSource',
    FORMAT = 'CSV',
    FIELDTERMINATOR = '',
    ROWTERMINATOR = ''
) as BulkFiles
CROSS APPLY OPENJSON(BulkFiles.BulkColumn) 
-- 此处补充你的JSON嵌套解析逻辑
...;

注意：该方法要求所有JSON文件的结构一致，若文件结构存在差异，需额外添加分支处理逻辑。

方法2：创建外部表（适合频繁批量读取场景） #

如果需要定期读取这些JSON文件，可以创建外部表映射Blob存储中的文件集合，后续直接查询外部表即可：

1. 先创建JSON格式的外部文件格式：

CREATE EXTERNAL FILE FORMAT JsonFileFormat
WITH (
    FORMAT_TYPE = JSON,
    DATA_COMPRESSION = 'NONE'
);

2. 创建外部表：

CREATE EXTERNAL TABLE dbo.BlobJsonFiles (
    -- 定义与JSON结构匹配的列
    Id INT,
    Name NVARCHAR(200),
    -- 其他字段...
)
WITH (
    LOCATION = '*.json',
    DATA_SOURCE = 'MyDataSource',
    FILE_FORMAT = JsonFileFormat
);

3. 查询所有文件数据：

SELECT * FROM dbo.BlobJsonFiles;

方法3：通过文件列表循环读取（适合需自定义处理逻辑的场景） #

先获取Blob存储中的所有JSON文件名，再循环逐个读取：

-- 启用xp_cmdshell（若未启用）
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- 存储文件名的临时表
CREATE TABLE #JsonFileList (FileName NVARCHAR(500));

-- 调用Azure CLI列出所有JSON文件（替换为你的存储账户、容器和SAS密钥）
DECLARE @listCmd NVARCHAR(1000) = 'az storage blob list --account-name <你的存储账户名> --container-name <你的容器名> --sas-token "<你的SAS密钥>" --query "[].name" --output tsv | findstr ".json"';
INSERT INTO #JsonFileList
EXEC xp_cmdshell @listCmd;

-- 循环读取每个文件
DECLARE @currentFile NVARCHAR(500);
DECLARE fileCursor CURSOR FOR 
    SELECT FileName FROM #JsonFileList WHERE FileName IS NOT NULL;

OPEN fileCursor;
FETCH NEXT FROM fileCursor INTO @currentFile;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT Obj1.*
    FROM OPENROWSET(
        BULK @currentFile, 
        DATA_SOURCE = 'MyDataSource', 
        SINGLE_CLOB
    ) as DataFile   
    CROSS APPLY OPENJSON(BulkColumn) 
    -- 补充你的JSON解析逻辑
    ...;

    FETCH NEXT FROM fileCursor INTO @currentFile;
END

CLOSE fileCursor;
DEALLOCATE fileCursor;
DROP TABLE #JsonFileList;

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