寻求使用SSMS连接Azure Blob Storage JSON数据并存储至SQL Server的分步指南

嘿，我给你整理了一份用SSMS把Azure Blob Storage里的JSON数据导入SQL Server的完整分步指南，都是实际操作过的步骤，你跟着来就行：

先确认这些基础条件都满足，避免后续踩坑：

• 你的SQL Server版本是2017及以上（因为要用到OPENROWSET(BULK...)访问Azure Blob的特性）
• 你有Azure Blob Storage的访问权限：要么拿到存储账户的密钥，要么有有效的SAS令牌（至少要有读取Blob的权限）
• 用的SSMS版本建议18.0以上，兼容性更好
• 目标SQL Server数据库已经创建，并且你拥有该库的CREATE TABLE、INSERT等操作权限

步骤1：配置SQL Server访问Azure Blob的外部数据源 #

首先要让SQL Server能连接到你的Azure Blob存储，需要创建几个关键对象，在SSMS的查询窗口里执行以下T-SQL：

1.1 创建数据库主密钥（如果还没创建过） #

主密钥用来加密后续的凭据信息：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword123!'; -- 替换成你自己的强密码
GO

1.2 创建数据库范围凭据 #

这里分两种方式，选一种就行：

方式A：用SAS令牌

CREATE DATABASE SCOPED CREDENTIAL AzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'YourFullSASKey'; -- 注意SAS令牌开头不要带?，比如sv=2022-11-02&ss=bfqt&...&sig=xxxx
GO

小贴士：SAS令牌可以在Azure Portal的存储账户→共享访问签名里生成，记得勾选“Blob”资源类型，至少给“读取”权限，设置合适的有效期。

方式B：用存储账户密钥

CREATE DATABASE SCOPED CREDENTIAL AzureBlobStorageCredential
WITH IDENTITY = 'YourStorageAccountName', -- 替换成你的存储账户名
SECRET = 'YourStorageAccountKey'; -- 替换成你的存储账户密钥
GO

1.3 创建外部数据源 #

这个对象指向你的Blob容器：

CREATE EXTERNAL DATA SOURCE AzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://yourstorageaccount.blob.core.windows.net/yourcontainer', -- 替换成你的容器URL
    CREDENTIAL = AzureBlobStorageCredential -- 对应上面创建的凭据名
);
GO

步骤2：预览Blob里的JSON数据（可选但推荐） #

在正式导入前，先确认能正确读取JSON数据，避免导入后发现格式问题：

-- 读取单个JSON文件并预览
SELECT *
FROM OPENROWSET(
    BULK 'yourjsonfile.json', -- 替换成Blob里的JSON文件名
    DATA_SOURCE = 'AzureBlobStorage',
    SINGLE_CLOB -- 读取整个文件为单个字符大对象
) AS jsonData;

如果你的JSON是每行一个JSON对象（比如日志类数据），或者是JSON数组，想要解析成结构化数据，可以用OPENJSON拆分：

SELECT parsedJson.*
FROM OPENROWSET(
    BULK 'yourjsonfile.json',
    DATA_SOURCE = 'AzureBlobStorage',
    SINGLE_CLOB
) AS jsonData
CROSS APPLY OPENJSON(jsonData.BulkColumn)
WITH (
    Id INT '$.id', -- 替换成你的JSON字段路径和对应SQL类型
    Name NVARCHAR(100) '$.name',
    Email NVARCHAR(100) '$.email',
    CreatedAt DATETIME '$.created_at'
) AS parsedJson;

这里的$.id是JSON里的字段路径，你需要根据自己的JSON结构调整，比如嵌套字段可以写成$.user.profile.id。

步骤3：创建目标SQL表（如果还没有） #

根据JSON的结构，创建对应的SQL表，比如：

CREATE TABLE dbo.ImportedJsonData (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) UNIQUE NOT NULL,
    CreatedAt DATETIME,
    ImportedDate DATETIME DEFAULT GETDATE() -- 新增导入时间字段，方便跟踪
);
GO

步骤4：将JSON数据导入SQL表 #

用INSERT INTO...SELECT的方式，把解析后的JSON数据插入到目标表：

INSERT INTO dbo.ImportedJsonData (Id, Name, Email, CreatedAt)
SELECT parsedJson.Id, parsedJson.Name, parsedJson.Email, parsedJson.CreatedAt
FROM OPENROWSET(
    BULK 'yourjsonfile.json',
    DATA_SOURCE = 'AzureBlobStorage',
    SINGLE_CLOB
) AS jsonData
CROSS APPLY OPENJSON(jsonData.BulkColumn)
WITH (
    Id INT '$.id',
    Name NVARCHAR(100) '$.name',
    Email NVARCHAR(100) '$.email',
    CreatedAt DATETIME '$.created_at'
) AS parsedJson;
GO

如果你的Blob里有多个JSON文件，想要批量导入，可以用通配符（比如'*.json'），不过需要注意SQL Server的版本支持（2019及以上支持批量读取多个文件）。

步骤5：验证导入结果 #

最后查询目标表，确认数据是否成功导入：

SELECT * FROM dbo.ImportedJsonData;

• 如果遇到“无法访问外部数据源”的错误：检查Blob存储的防火墙设置，是否允许SQL Server的IP地址访问；或者确认SAS令牌/存储账户密钥有没有过期、权限是否足够。
• 嵌套JSON的处理：如果JSON有多层嵌套，可以在OPENJSON的WITH里用JSON_VALUE或者嵌套的OPENJSON来解析，比如AddressCity NVARCHAR(50) '$.address.city'。
• 大型JSON文件：如果JSON文件很大，建议分批导入，或者使用MAXRECURSION参数（如果有递归解析需求），避免内存溢出。

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