Loading CSV from Azure Data Lake (Gen 2) to Azure SQL Database

2
votes

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.

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);

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

enter image description here

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:

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!

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

1
tsqlazure-sql-databaseazure-storage-blobsazure-data-lakeexternal-data-source
ADLS Gen 2 doesn't currently support the Blob Storage API. So currently you can't connect directly from SQL Database. – David Browne - Microsoft
Just use Data Factory. – wBob
@DavidBrowne-Microsoft - I did not know that, thank you. Do you know if there are plans to include support for Blob Support API in ADLS Gen 2 once it has moved past Preview and into GA? – MAK
@wBob - I already have created the same process in ADF but I was hoping to find a way using T-SQL. Personally I find T-SQL statements easier to maintain / amend / share. – MAK
Yes. Interoperability with the blob storage APIs is an announced, and important feature of ADLS Gen 2. – David Browne - Microsoft

1 Answers

1
votes

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.

Source: https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-known-issues

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