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

Published 04-23-2020 10:53 AM 2,355 Views
Microsoft

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!

2 Comments
Occasional Visitor

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. :)

 

 

 

 

Microsoft

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1333098%22%20slang%3D%22en-US%22%3ELoad%20data%20from%20Azure%20Blob%20storage%20into%20Azure%20SQL%20%7C%20New%20Data%20Exposed%20episode%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1333098%22%20slang%3D%22en-US%22%3E%3CP%3ECurious%20how%20loading%20data%20in%20Azure%20SQL%20Database%20is%20different%20than%20SQL%20Server%3F%20In%20this%20short%20episode%2C%20I%20walk%20through%20loading%20a%20single%20file%20(located%20in%20Azure%20Blob%20storage)%20into%20Azure%20SQL%20Database.%20This%20is%20all%20done%20using%20a%20SQL%20Notebook%20in%20Azure%20Data%20Studio.%20As%20a%20bonus%2C%20we%20add%20commentary%20on%20tips%20for%20getting%20better%20performance%20when%20loading%20data%20into%20Azure%20SQL%20Database.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EYou%20can%20view%2Fshare%20the%20latest%20episode%20on%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fchannel9.msdn.com%2FShows%2FData-Exposed%2FLoad-data-from-Azure-Blob-storage-into-Azure-SQL%3FWT.mc_id%3Ddataexposed-c9-niner%22%20target%3D%22_self%22%20rel%3D%22noreferrer%20noopener%22%3EChannel9%3C%2FA%3E%3CSPAN%3E%26nbsp%3Band%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D-7fjR3yPUVU%3FWT.mc_id%3Ddataexposed-c9-niner%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noreferrer%20noopener%22%3EYouTube!%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1333098%22%20slang%3D%22en-US%22%3E%3CP%3ECheck%20out%20the%20latest%20episode%20on%20Data%20Exposed!%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2300936%22%20slang%3D%22en-US%22%3ERe%3A%20Load%20data%20from%20Azure%20Blob%20storage%20into%20Azure%20SQL%20%7C%20New%20Data%20Exposed%20episode%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2300936%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Anna%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20this%20amazing%20post.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20followed%20your%20vedio%20from%20youtube%20and%20microsoft%20documentation%2C%20while%20i%20try%20to%20do%20perform%20similar%20task%20i%20am%20blocked%20with%20error%20during%20Bulk%20insert%20step.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20are%20my%20steps%20performed%20and%20error%2C%20please%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E----%3C%2FP%3E%3CP%3ECREATE%20MASTER%20KEY%20ENCRYPTION%20BY%20PASSWORD%20%3D%20'Test_123'%3B%3C%2FP%3E%3CP%3E---%3C%2FP%3E%3CP%3ECREATE%20DATABASE%20SCOPED%20CREDENTIAL%20MyCredentials%3CBR%20%2F%3EWITH%20IDENTITY%20%3D%20'SHARED%20ACCESS%20SIGNATURE'%2C%3CBR%20%2F%3ESECRET%20%3D%20'sp%3Dr%26amp%3Bst%3D2021-04-28T05%3A36%3A45Z%26amp%3Bse%3D2021-04-30T13%3A36%3A45Z%26amp%3Bspr%3Dhttps%26amp%3Bsv%3D2020-02-10%26amp%3Bsr%3Db%26amp%3Bsig%3DNWmp5KNs6u6BBop8TbTD1IdeoVrsj5xhSvT0NRIxumg%253D'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E---%3C%2FP%3E%3CP%3ECREATE%20EXTERNAL%20DATA%20SOURCE%20dataset%3CBR%20%2F%3EWITH%20(%20TYPE%20%3D%20BLOB_STORAGE%2C%3CBR%20%2F%3ELOCATION%20%3D%20'%3CA%20href%3D%22https%3A%2F%2Ftest.blob.core.windows.net%2Fdata%2FData1%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftest.blob.core.windows.net%2Fdata%2FData1%2F%3C%2FA%3E'%2C%3CBR%20%2F%3E--%20CREDENTIAL%3D%20MyCredentials%3CBR%20%2F%3E)%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E---%3C%2FP%3E%3CP%3EAs%20table%20is%20already%20created%20by%20name%20Test%20with%20schema%2C%20i%20am%20doing%20bulk%20insert.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBULK%20INSERT%20Test%3CBR%20%2F%3EFROM%20'%2Fdata%2FData1%2Ftest.csv'%3CBR%20%2F%3EWITH%20(%20DATA_SOURCE%20%3D%20'dataset'%2C%3CBR%20%2F%3EFORMAT%3D'CSV'%2C%20CODEPAGE%20%3D%2065001%2C%20--UTF-8%20encoding%3CBR%20%2F%3EFIRSTROW%3D2%2C%3CBR%20%2F%3EROWTERMINATOR%20%3D%20'0x0a'%2C%3CBR%20%2F%3ETABLOCK)%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EERROR%3A%3C%2FP%3E%3CP%3EMsg%204861%2C%20Level%2016%2C%20State%201%2C%20Line%2036%3CBR%20%2F%3ECannot%20bulk%20load%20because%20the%20file%20%22%2Fdata%2FData1%2Ftest.csv%22%20could%20not%20be%20opened.%20Operating%20system%20error%20code%205(Access%20is%20denied.).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20advise%20how%20to%20overcome%20this%20error%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2342669%22%20slang%3D%22en-US%22%3ERe%3A%20Load%20data%20from%20Azure%20Blob%20storage%20into%20Azure%20SQL%20%7C%20New%20Data%20Exposed%20episode%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2342669%22%20slang%3D%22en-US%22%3E%3CP%3ETry%20following%20along%20with%20this%20example%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Flearn%2Fmodules%2Fazure-sql-deploy-configure%2F8-exercise-load-data%3FWT.mc_id%3Dazuresql4beg_azuresql-social-learn%26amp%3Bns-enrollment-type%3DLearningPath%26amp%3Bns-enrollment-id%3Dlearn.azure-sql-fundamentals%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EExercise%20-%20Load%20data%20into%20Azure%20SQL%20Database%20-%20Learn%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20location%20%22%3CSPAN%3ELOCATION%20%3D%20'%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftest.blob.core.windows.net%2Fdata%2FData1%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftest.blob.core.windows.net%2Fdata%2FData1%2F%3C%2FA%3E%3CSPAN%3E'%2C%22%26nbsp%3B%3C%2FSPAN%3E%20should%20go%20to%20the%20container%20where%20the%20data%20is%20stored.%20Then%20in%20the%20bulk%20insert%20%22%3CSPAN%3EFROM%20'%2Fdata%2FData1%2Ftest.csv'%22%20it%20should%20go%20from%20the%20container%20to%20the%20file.%20Right%20now%20your%20command%20is%20looking%20in%20%22%3CA%20href%3D%22https%3A%2F%2Ftest.blob.core.windows.net%2Fdata%2FData1%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftest.blob.core.windows.net%2Fdata%2FData1%2F%3C%2FA%3E%2Fdata%2FData1%2Ftest.csv'.%20You%20may%20also%20have%20an%20extra%20'%2F'.%20Hope%20that%20helps%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Apr 23 2020 10:53 AM
Updated by: