2

I have a stored procedure which accesses files on a server through a network path but now we are moving from the server to Azure Blob. Is it possible to access files on Azure Blob from a stored procedure?

The stored procedure basically reads CSV files on the server and inserts data from these files into tables.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fakhar Ahmad Rasul
  • 1,595
  • 1
  • 19
  • 37
  • To be clear, you want to make a call to receive data from an external system within a stored procedure in your database? – devNull Aug 10 '20 at 01:06
  • 1
    [This](https://jayendranarumugam.wordpress.com/2019/06/19/t-sql-bulk-insert-azure-csv-blob-into-azure-sql-database/) suggests it is possible - e.g. _This Bulk Insert command helps us to bulk insert our CSV file from Blob container to SQL Table_ – stuartd Aug 10 '20 at 01:26

1 Answers1

2

This feature was documented in public preview 2017, I had a a similar issue some time back.

Azure SQL Database enables you to directly load files stored in Azure Blob storage by using the following SQL statements:

  • BULK INSERT T-SQL command that loads a file from a Blob storage account into a SQL Database table

  • OPENROWSET table-value function that parses a file stored in Blob storage and returns the content of the file as a set of rows

The following example shows a BULK INSERT command that loads the content of the file into SQL Database:

BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorageAccount');

You can parse the content of a remote file by using the OPENROWSET function and return rows from the file as results:

SELECT Name, Color, Price, Size, Quantity, Data, Tags
FROM OPENROWSET(BULK 'data/product.bcp', DATA_SOURCE = 'MyAzureBlobStorage',
            FORMATFILE='data/product.fmt', FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as products;

As a prerequisite, you need to create an EXTERNAL DATA SOURCE that will point to your Azure Blob storage account. You'll use the name of this EXTERNAL DATA SOURCE in the DATA_SOURCE attribute. Here's an example of an EXTERNAL DATA SOURCE that points to a public Azure Blob storage account:

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

If your Azure Blob storage account is not public, you need to generate a shared access signatures (SAS) key for the account by using the Azure portal, put the SAS key in CREDENTIAL, and create an EXTERNAL DATA SOURCE with CREDENTIAL, as shown in the following example:

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2015-12-11&ss=b&srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z&spr=https&sig=copyFromAzurePortal';


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

References

https://azure.microsoft.com/en-us/updates/preview-loading-files-from-azure-blob-storage-into-sql-database/

https://github.com/Microsoft/sql-server-samples/tree/master/samples/features/sql-bulk-load/load-from-azure-blob-storage

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15

enter image description here

scott_lotus
  • 3,171
  • 22
  • 51
  • 69
  • I am getting `Incorrect syntax near 'EXTERNAL'.` when I am trying to create the external datasource. Is this only compatible with Azure SQL Databases? – Fakhar Ahmad Rasul Aug 10 '20 at 17:35
  • Morning Fakhar. The above statement is supported in Azure SQL yes but also SQL Server but at a minimum version of 2017 I think, see https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15 – scott_lotus Aug 11 '20 at 05:11