read file from Azure Blob Storage into Azure SQL Database

3
votes

I have already tested this design using a local SQL Server Express set-up.

I uploaded several .json files to Azure Storage In SQL Database, I created an External Data source:

CREATE EXTERNAL DATA SOURCE MyAzureStorage WITH (TYPE = BLOB_STORAGE, LOCATION = 'https://mydatafilestest.blob.core.windows.net/my_dir );

Then I tried to query the file using my External Data Source:

select *
from OPENROWSET
 (BULK 'my_test_doc.json', DATA_SOURCE = 'MyAzureStorage', SINGLE_CLOB) as data

However, this failed with the error message "Cannot bulk load. The file "prod_EnvBlow.json" does not exist or you don't have file access rights."

Do I need to configure a DATABASE SCOPED CREDENTIAL to access the file storage, as described here? https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql

What else can anyone see that has gone wrong and I need to correct?

2
sql-serverazure-sql-databaseazure-storage
did you find a way to do this in the end? (i.e. not using OPENROWSET) – joshi123
@joshi123 - I have not be able to re-visit this for quite a while so have no update. – CarCrazyBen
I found a solution, see answer below! – joshi123

2 Answers

2
votes

OPENROWSET is currently not supported on Azure SQL Database as explained in this documentation page. You may use BULK INSERT to insert data into a temporary table and then query this table. See this page for documentation on BULK INSERT.

1
votes

Now that OPENROWSET is in public preview, the following works. Nb the key option is in case your blob is not public. I tried it on a private blob with the scoped credential option and it worked. nnb if you are using a SAS key make sure you delete the leading ? so the string should start with sv as shown below.

Make sure the blobcontainer/my_test_doc.json section specifies the correct path e.g. container/file. 

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2017****************';

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
   LOCATION = 'https://yourstorage.blob.core.windows.net',
   CREDENTIAL= MyAzureBlobStorageCredential);

DECLARE @json varchar(max)
SELECT @json = BulkColumn FROM OPENROWSET(BULK 'blobcontainer/my_test_doc.json', 
SINGLE_BLOB, DATA_SOURCE = 'MyAzureBlobStorage',
            FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as j;

select @json;

More detail provided in these docs