将CSV从Azure Data Lake(Gen 2)加载到Azure SQL数据库 [英] Loading CSV from Azure Data Lake (Gen 2) to Azure SQL Database

问题描述

我有一个带有多个容器的Azure Data Lake Storage(第2代)帐户.我想将salesorderdetail.csv文件从Sales容器导入到Azure SQL数据库中.

I have an Azure Data Lake Storage (Gen 2) account with several containers. I would like to import the salesorderdetail.csv file from the Sales container into an Azure SQL database.

我已经使用Azure数据工厂成功构建了相同的过程，但是我现在想尝试仅通过标准T-SQL语句来使它正常工作.

I've successfully built the same process using Azure Data Factory, but I now want to try and get this working via standard T-SQL statements only.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XxxxxxXX#'

CREATE DATABASE SCOPED CREDENTIAL MK_Cred_Data_Load 
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', 
SECRET = 'sv=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX%3D'; 

CREATE EXTERNAL DATA SOURCE MK_ADLS_Sales
WITH (TYPE = BLOB_STORAGE, 
LOCATION = 'https://mkpracticestorageaccount.blob.core.windows.net/sales', 
CREDENTIAL = MK_Cred_Data_Load);

上面的代码似乎正常工作，因为我可以看到在SSMS的对象资源管理器"窗口中创建的外部数据源":

The above code appears to work correctly as I can see the External Data Source created in the Object Explorer window on SSMS:

以下代码应将salesorderdetail.csv文件中的数据插入到[lnd].salesorderdetail表中:

The following code should insert the data from the salesorderdetail.csv file into the [lnd].salesorderdetail table:

TRUNCATE TABLE [lnd].[SalesOrderDetail]

BULK INSERT [lnd].[SalesOrderDetail]
FROM 'salesorderdetail.csv'
WITH (DATA_SOURCE = 'MK_ADLS_Sales', FORMAT = 'CSV', FIRSTROW=2, 
FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

相反，它输出以下错误消息:

Instead, it outputs the following error message:

找不到参考的外部数据源"MK_ADLS_Sales".

Referenced external data source "MK_ADLS_Sales" not found.

我也尝试了以下方法，但是出现了与上面相同的错误消息.

I have also tried the following but the same error message appears as above.

SELECT * 
FROM OPENROWSET(BULK  'salesorderdetail.csv',
                DATA_SOURCE = 'MK_ADLS_Sales',
                SINGLE_CLOB) AS DataFile;

有人可以帮忙吗?谢谢！

Can someone please kindly assist? Thanks!

(使用SSMS v17.9，Azure SQL数据库标准版，Windows 10)

(Using SSMS v17.9, Azure SQL database standard edition, Windows 10)

推荐答案

Blob存储API尚不适用于Azure Data Lake Storage Gen2帐户. 禁用这些API是为了防止由于Blob存储API尚未与Azure Data Lake Gen2 API互操作而引起的意外数据访问问题.

Blob storage APIs aren't yet available to Azure Data Lake Storage Gen2 accounts. These APIs are disabled to prevent inadvertent data access issues that could arise because Blob Storage APIs aren't yet interoperable with Azure Data Lake Gen2 APIs.

来源: 这意味着，直到Blob支持API可用于ADLS Gen 2，我们才能使用

Which means that until Blob Support API is available for ADLS Gen 2, we can't use the T-SQL approach as described here.

这篇关于将CSV从Azure Data Lake(Gen 2)加载到Azure SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！