Load data from Azure Blob storage into Azure SQL | New Data Exposed episode

[Anna Hoffman]

Curious how loading data in Azure SQL Database is different than SQL Server? In this short episode, I walk through loading a single file (located in Azure Blob storage) into Azure SQL Database. This is all done using a SQL Notebook in Azure Data Studio. As a bonus, we add commentary on tips for getting better performance when loading data into Azure SQL Database.

You can view/share the latest episode on Channel9 and YouTube!

[awsahmed]

Hello Anna,

Thanks for this amazing post.

I have followed your vedio from youtube and microsoft documentation, while i try to do perform similar task i am blocked with error during Bulk insert step.

Below are my steps performed and error, please help.

----

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test_123';

---

CREATE DATABASE SCOPED CREDENTIAL MyCredentials
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sp=r&st=2021-04-28T05:36:45Z&se=2021-04-30T13:36:45Z&spr=https&sv=2020-02-10&sr=b&sig=NWmp5KNs6u6BBop8TbTD1IdeoVrsj5xhSvT0NRIxumg%3D'

---

CREATE EXTERNAL DATA SOURCE dataset
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://test.blob.core.windows.net/data/Data1/',
-- CREDENTIAL= MyCredentials
);

---

As table is already created by name Test with schema, i am doing bulk insert.

BULK INSERT Test
FROM '/data/Data1/test.csv'
WITH ( DATA_SOURCE = 'dataset',
FORMAT='CSV', CODEPAGE = 65001, --UTF-8 encoding
FIRSTROW=2,
ROWTERMINATOR = '0x0a',
TABLOCK);

ERROR:

Msg 4861, Level 16, State 1, Line 36
Cannot bulk load because the file "/data/Data1/test.csv" could not be opened. Operating system error code 5(Access is denied.).

Please advise how to overcome this error?

Thanks in advance. :)

[Anna Hoffman]

Try following along with this example: Exercise - Load data into Azure SQL Database - Learn | Microsoft Docs

The location "LOCATION = 'https://test.blob.core.windows.net/data/Data1/',"  should go to the container where the data is stored. Then in the bulk insert "FROM '/data/Data1/test.csv'" it should go from the container to the file. Right now your command is looking in "https://test.blob.core.windows.net/data/Data1//data/Data1/test.csv'. You may also have an extra '/'. Hope that helps