Azure SQL Managed Instance (3) 把本地Excel文件通过OPENROWSET导入到Azure SQL Managed Instance

　　《Windows Azure Platform 系列文章目录》

 

　　本文介绍如何使用Azure Blob Storage，把本地Excel文件导入到Azure SQL MI

　　在使用SQL MI的时候，通常会把本地Excel文件导入到Azure SQL MI，都会通过服务器路径进行上传，比如下面一句话：

INSERT INTO  dbo.orderdetails
select *,createtime=GETDATE()
from Openrowset('Microsoft.ACE.OLEDB.12.0',
                 'EXCEL 12.0;HDR=YES;User id=admin;Password=;IMEX=1;DATABASE=D:\order.xlsx',
                 'select * from [Sheet1$]')
WHERE [orderid] IS NOT NULL;

　　但是我们在使用Azure SQL MI的时候，SQL MI并不知道C盘、D盘这些路径，这时候就需要把文件上传到Azure Blob Storage来进行导入操作

 

　　主要步骤：

　　第一步：Excel文件不能直接上传到SQL MI，必须转换为csv文件

　　第二步：将CSV文件到Azure SQL MI数据表 (第一次导入CSV文件时需要，主要为了生成Format文件，后续有了Format文件就无需执行第二步，直接执行第四步通过T-SQL语句导入)

　　第三步：需要通过bcp命令，导出数据表文件为Format文件 (第一次导入CSV文件时需要，主要为了生成Format文件，后续有了Format文件就无需执行第二步，直接执行第四步通过T-SQL语句导入)

　　第四步：将CSV文件和Format上传到Azure Blob，并通过T-SQL语句导入

 

　　接下来进入我们的演示步骤：

　　第一步：Excel文件不能直接上传到SQL MI，必须转换为csv文件，步骤略。

 

　　第二步：将CSV文件到Azure SQL MI数据表 (第一次导入CSV文件时需要，主要为了生成Format文件，后续有了Format文件就无需执行第二步，直接执行第四步通过T-SQL语句导入)

　　1.我们打开SQL Server Management Studio，连接到SQL MI，步骤略。

　　2.点击Database，右键点击TASK，Import Data，如下图：

　　

　　3.在Choose a Data Source窗口中，选择Flat File Source，然后点击Browse选择本地的CSV文件，进行上传

　　

 

　　4.在SQL Server Import and Export Wizard窗口中，我们点击Properties

　　

　　

　　5.在弹出的窗口中，输入SQL MI登录的用户名、密码和需要导入的Database。如下图：

　　

 

　　6.下图中，点击Next

　　

 

　　7.下图中，点击Run Immediately

　　

 

　　8.查看导入进度，如下图：

　　

 

　　9.导入成功后，我们在SQL MI可以查看到已经导入成功的Table，如下图dbo.testcsv：

　　

 

　　第三步：需要通过bcp命令，导出数据表文件为Format文件 (第一次导入CSV文件时需要，主要为了生成Format文件，后续有了Format文件就无需执行第二步，直接执行第四步通过T-SQL语句导入)

　　1.我们打开本地CMD命令行，输入

where bcp.exe

　　

 

　　2.cd进入到bcp.exe所在的目录，然后执行下面的命令：

bcp [dbname].[schema].[tablename] format nul -c -f [fmt文件本地路径] -t,  -U "[登录数据库的用户名]" -P "[登录数据库的密码]" -S "[SQLMI的DNS地址]"

　　比如我们执行下面的脚本，把testcsv.fmt文件保存到本地的D盘，如下图

bcp [dbname].[schema].[tablename] format nul -c -f D:\testcsv.fmt -t,  -U "[登录数据库的用户名]" -P "[登录数据库的密码]" -S "[SQLMI的DNS地址]"

　　执行完毕后，我们用记事本打开D盘的testcsv.fmt文件，如下图：

　　这个format文件我们需要保留下来，后续可以在csv文件schema不变的情况下可以继续使用

　　

 

 

　　第四步：将CSV文件和Format上传到Azure Blob，并通过T-SQL语句导入

　　1.我们把上面步骤中用到的testcsv.csv文件和testcsv.fmt文件，都上传到Azure Blob存储账户里，保存在container 名称为factory01

　　注意需要把container属性设置为private，不允许匿名访问

　　上传完毕后，全路径为：

　　https://leisqlmi01storage.blob.core.chinacloudapi.cn/factory01/factory01.csv

　　https://leisqlmi01storage.blob.core.chinacloudapi.cn/factory01/factory01.fmt

 

　　2.点击Azure Blob存储账户，点击Container，点击Generate SAS Token and URL，然后复制SAS Token

　　

 

 

　　

　　3.我们回到SQL Server Management Studio，打开需要访问的数据库，右键New Query，执行下面的脚本：

--下面的password的秘钥内容，可以根据需要修改PASSWORD值
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '12345#KL95234nl0zBe';  

CREATE DATABASE SCOPED CREDENTIAL factory01
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '[这里是你在上面步骤复制的SAS Token]';


--设置设置External Data Source
CREATE EXTERNAL DATA SOURCE factory01Container
    WITH (
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://leisqlmi01storage.blob.core.chinacloudapi.cn/factory01',
        CREDENTIAL = factory01
    );

--BULK 就是需要导入的CSV文件，已经保存到Azure Blob中
--Formatfile就是BCP命令导出的format文件
  SELECT * FROM OPENROWSET(
   BULK 'testcsv.csv',
   DATA_SOURCE = 'factory01Container',
   FORMAT = 'CSV',
   FORMATFILE='testcsv.fmt',
   FORMATFILE_DATA_SOURCE = 'factory01Container'
   ) AS DataFile;

 

　　这样就可以通过OPENROWSET，访问保存到Azure Blob中的CSV文件