本文介绍了如何在创建好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
- Storage Explorer 本地操作Azure Storage的工具
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
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部分的设置
image.png
5.1.1 Enabled from all networks
如果是选择了这个，那么就要确保本文上面的尤其是步骤4中的都创建而且是正确的。
5.1.2 Enabled from selected virtual networks and IP addresses
-
确保 Storage Account/Networking/Exceptions
image.png 为Azure SQL Server产生了Managed Identity
Storage Account ACL Blob container contributor 中加入了Azure SQL Server
