CODEWITHSUNDEEP
t-sqlazure-sql-databaseazure-blob-storageazure-data-lakeexternal-data-source
MAK
MAK

Reputation: 125

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

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)

Upvotes: 2

Views: 3418

Answers (1)

MAK
MAK

Reputation: 125

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://learn.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.

Upvotes: 1

Related Questions