CODEWITHSUNDEEP
sqlazureblobazure-blob-storagebulkinsert
Dylan Smyth
Dylan Smyth

Reputation: 162

Is there a way BULK INSERT from local Azure Blob Storage?

TL;DR I am trying to point SQL to BULK INSERT from Local Azure Blob Storage

The problem:

Hi all,

I'm trying to connect my local SQL Server database instance to the blob storage emulator as an external connection, however I'm getting a "Bad or inaccessible location specified" error. Here are the steps I'm taking:

I have created the following MasterDatabaseKey and CREDENTIALS as follows:

IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##') 
DROP MASTER KEY;

--Create Master Key
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD='MyStrongPassword';

and database credentials:

-- DROP DB Credentials If Exist
IF EXISTS (SELECT * FROM sys.database_credentials WHERE name = 'credentials')
DROP DATABASE SCORED CREDENTIAL credentials;

--Create scoped credentials to connect to Blob
CREATE DATABASE SCOPED CREDENTIAL credentials
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 
'Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw=='; --local storage key
GO

then I created the following External Data Source:

CREATE EXTERNAL DATA SOURCE external_source
WITH 
(
    TYPE = BLOB_STORAGE,
    LOCATION = 'http://127.0.0.1:10000/devstoreaccount1/container/some_folder/',
    CREDENTIAL = credentials
)

But when I run the BULK INSERT command:

BULK INSERT [dbo].[StagingTable] FROM 'some_file_on_blob_storage.csv' WITH (DATA_SOURCE = 'external_source', FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

but it fails and returns

Bad or inaccessible location specified in external data source "external_source".

How can I load a file from Local Blob Storage into SQL Server?

Upvotes: 2

Views: 2796

Answers (1)

Leon Yue
Leon Yue

Reputation: 16401

Nick.McDermaid has point out the error correctly. From your code and the error message, the error is caused by the wrong LOCATION syntax:

  • Do not add a trailing /, file name, or shared access signature parameters at the end of the LOCATION URL when configuring an external data source for bulk operations.

Ref here: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&tabs=dedicated#examples-bulk-operations

Change value to LOCATION = 'http://127.0.0.1:10000/devstoreaccount1/container/some_folder', the error should be solved. I tested and all works well. enter image description here

For you another question, we can not answer you directly. I suggest you post another question with you detailed code. We're all glad to help you.

Update:

About your another question, I tested and found that we must set the Shared access signature(SAS) 'Allowed resource type' = Object, then we can access container and child folder and the files in the container.

enter image description here

Example, both the statements work well. enter image description here

HTH.

Upvotes: 3

Related Questions