批量bulkload数据到Azure SQL数据库

移动开发2024-10-19 22:53:08

本文介绍了如何在创建好Azure SQL数据库后

创建sql-server-samples数据库 AdventureWorks
从Azure blob storage中将csv格式数据批量导入到Azure SQL数据库。

1.环境说明

Azure SQL Server （Logic) 准备就绪

Azure SQL Server
Azure SQL准备就绪
使用SSMS 连接Azure SQL database 确保登录成功

Azure SQL

SQL Server Management Studio(SSMS) 连接成功

SSMS
Azure storage account 准备就绪
这里storage account是 public storage account
如果这里不设置为第一项的话，可能会遇到如下的错误
在不使用public storage account的情况下，需要额外设置一些属性，具体设置请参见本文第5节

Cannot bulk load because the file "folder/XXX" could not be opened. Operating system error code 5(Access is denied.).

Azure blob storage 准备就绪 [本地可以使用Storage Explorer访问]
sql-server-samples/AdventureWorks-oltp-install-script.zip unzip后的csv文件已上传到blob storage container中。

Storage Explorer访问信息
实验的数据脚本准备就绪
本实验使用的脚本
github.com/microsoft/sql-server-samples
该脚本不能在AzureSQL上执行，需要修改下，具体修改下面有说明。
如果git不能访问的朋友，可以直接用网盘
链接：https://pan.baidu.com/s/1UN1LTKUeJosV6ct_5f4NRw
提取码：qgue
相关工具 [需要本地安装]
- Storage Explorer 本地操作Azure Storage的工具
  https://azure.microsoft.com/en-us/products/storage/storage-explorer/
- SQL Server Management Studio 本地连接AzureSQL数据库执行操作的工具
  https://learn.microsoft.com/zh-tw/sql/ssms/download-sql-server-management-studio-ssms

2.创建数据库和用户 [用admin用户操作，可以暂时忽略这一步]

打开AdventureWorks-oltp-install-script.zip 文件包里面包含

instawdb.sql
csv格式的按表导出的数据文件

2.1 创建用户

在Azure portal创建的Azure SQL数据库Server admin 是一个全局的admin 用户。
你可以直接用这个全局的admin用户操作。可以暂时跳过这一步。

如果需要给每个数据库创建单独的用户，
可以通过下面的方式创建指定数据库相关权限的用户。
比如下面示例中创建了etluser

master中创建 login用户
···
use master
create login etluser with PASSWORD = 'strongpassword'
···
数据库级别用户映射登录用户

use AdventureWorks
CREATE USER [etluser] FOR LOGIN [etluser] with default_schema = dbo;

给用户增加角色

ALTER ROLE [db_datawriter] ADD MEMBER etluser
ALTER ROLE [db_accessadmin] ADD MEMBER etluser
ALTER ROLE [db_datareader] ADD MEMBER etluser
ALTER ROLE [db_ddladmin] ADD MEMBER etluser

下图是Azure SQL数据库中的角色列表

3. 测试 bulk load数据到Azure SQL数据库

SSMS切换到数据库AndventureWorks

3.1 创建Master Key

Ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql?msclkid=6dcae6fcb0a911ec9ca298df2a8f1224&view=sql-server-ver15

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strongpasswordforsqlserver';

-- 如果存在可以删除 master key
DROP MASTER KEY

3.2 需要storage account的SHARED ACCESS SIGNATURE

登录Azure portal找到 storage account，获取Shared Access Signature [SAS token]

3.3 创建database scoped credential

确保3.2步骤中获取的SAS token中的不要包含在SECRET 中。

 CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'sv=2022-11-02XXXXXXX';

3.4 创建外部数据源

确保LOCATION 设置的正确，直接从storage explorer中copy对应blob container的URL

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
 WITH ( TYPE = BLOB_STORAGE,
       LOCATION = 'https://XXXXXXX.blob.core.windows.net/adnventure',
       CREDENTIAL= MyAzureBlobStorageCredential);

创建成功的话可以在SSMS看到外部数据源

3.5 bulk insert

From 文件名的地方要注意下
比如我在Azure blob container中有个Address.csv文件完整的 URL

https://XXXXXXX.blob.core.windows.net/adnventure/adventure/AdventureWorks-oltp-install-script/Address.csv

External Data Source Location 信息
'https://XXXXXXX.blob.core.windows.net/adnventure'

BULK INSERT FROM 部分的信息
'adventure/AdventureWorks-oltp-install-script/Address.csv'

CREATE TABLE [Address](
    [AddressID] [int] IDENTITY(1,1) NOT NULL,
    [AddressLine1] [nvarchar](60) NOT NULL,
    [AddressLine2] [nvarchar](60) NULL,
    [City] [nvarchar](30) NOT NULL,
    [StateProvinceID] [int] NOT NULL,
    [PostalCode] [nvarchar](15) NOT NULL,
    [SpatialLocation] [geography] NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)

BULK INSERT [Address] FROM 'adventure/AdventureWorks-oltp-install-script/Address.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage',
    CHECK_CONSTRAINTS,
    CODEPAGE='ACP',
    DATAFILETYPE = 'char',
    FIELDTERMINATOR= '\t',
    ROWTERMINATOR = '\n',
    KEEPIDENTITY,
    TABLOCK);

4 修改instawdb.sql

4.1 instawdb.sql 文件说明

行1- 行120 数据库master中操作
创建database
如果database存在就删除，否则就创建AdventureWorks
行120 行之后切换到 AdventureWorks数据库
创建schema
创建xml schema
创建表
bulk load数据 //这部分需要调整增加DATA_SOURCE = 'MyAzureBlobStorage'
给表添加主键信息
给表添加Index
给表添加XML index
Create Full Text catalog and indexes
Create Foreign key constraints
Add table triggers.
创建View
创建Function
创建存储过程
Add Extended Properties
Drop DDL Trigger for Database

4.2 修改instawdb.sql

主要的修改包括以下几点

由于Azure SQL不支持脚本中的use 切换数据库语法
将instawdb.sql脚本拆分成两部分
- 1.sql . 在master中创建数据库AdventureWorks (1-120 行)
- 3.sql . 在AdventureWorks中创建schema，表，load数据等 (121行以后部分+ 两个环境变量）
  主要修改bulk insert部分
  调整前

BULK INSERT [Person].[Address] FROM '$(SqlSamplesSourceDataPath)Address.csv'
WITH (
    CHECK_CONSTRAINTS,
    CODEPAGE='ACP',
    DATAFILETYPE = 'char',
    FIELDTERMINATOR= '\t',
    ROWTERMINATOR = '\n',
    KEEPIDENTITY,
    TABLOCK
);

调整后加了 DATA_SOURCE = 'MyAzureBlobStorage'

BULK INSERT [Person].[Address] FROM '$(SqlSamplesSourceDataPath)Address.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage',
    CHECK_CONSTRAINTS,
    CODEPAGE='ACP',
    DATAFILETYPE = 'char',
    FIELDTERMINATOR= '\t',
    ROWTERMINATOR = '\n',
    KEEPIDENTITY,
    TABLOCK

增加脚本处理如下的信息
2.sql 在AndventureWorks中创建external data source
把blob storage创建为external data source

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='test@123';

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=XXXX'

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
 WITH ( TYPE = BLOB_STORAGE,
       LOCATION = 'https://XXXXX.blob.core.windows.net/adnventure',
       CREDENTIAL= MyAzureBlobStorageCredential);

执行的顺序是, 需要查询/SQLCMD模式
1.sql
2.sql
3.sql

5 问题及解决

5.1 问题1 Cannot bulk load

Cannot bulk load because the file "folder/XXX" could not be opened. Operating system error code 5(Access is denied.)

检查storage account的network部分的设置

5.1.1 Enabled from all networks

如果是选择了这个，那么就要确保本文上面的尤其是步骤4中的都创建而且是正确的。

5.1.2 Enabled from selected virtual networks and IP addresses

确保 Storage Account/Networking/Exceptions
为Azure SQL Server产生了Managed Identity
Storage Account ACL Blob container contributor 中加入了Azure SQL Server

查看全文

https://www.xamrdz.com/mobile/4kt1995143.html