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

标签：文件 Managed MI Instance 导入 SQL Azure CSV

　　《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文件

 

　　

标签：文件,Managed,MI,Instance,导入,SQL,Azure,CSV
From： https://www.cnblogs.com/threestone/p/16785705.html