Loading files from Azure Blob Storage into Azure SQL Database

Azure SQL Database enables you to directly load files stored on Azure Blob Storage using the BULK INSERT T-SQL command and OPENROWSET function.

Loading content of files form Azure Blob Storage account into a table in SQL Database is now single command:

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

 

BULK INSERT is existing command in T-SQL language that enables you to load files from file system into a table. New DATA_SOURCE option enables you to reference Azure Blob Storage account.

You can also use OPENROWSET function to parse content of the file and execute any T-SQL query on returned rows:

 SELECT Color, count(*)
FROM OPENROWSET(BULK 'data/product.bcp', DATA_SOURCE = 'MyAzureBlobStorage',
 FORMATFILE='data/product.fmt', FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as data
GROUP BY Color;

OPENROWSET function enables you to specify data sources where input file is placed, and data source where format file (the file that defines the structure of file) is placed.

If your file is placed on a public Azure Blob Storage account, you need to define EXTERNAL DATA SOURCE that points to that account:

 

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

Once you define external data source, you can use the name of that source in BULK INSERT and OPENROWSET.

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some strong password';

 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);

 

 

You can find full example with some sample files on SQL Server GitHub account.

Comments

• Anonymous
  February 24, 2017
  Hi Jovan, Thanks for enabling bulk Insert.I have created a .dat file with few sample records without header .Column sequence is same as table(Test) structure in azure sql DB. I tried 2 delimited 1. first time (space) 2.Second time coma(,). I followed below steps. MASTER KEY is already created earlier.but I am not able see data in table.--Step 1CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredentialWITH IDENTITY = 'SHARED ACCESS SIGNATURE',SECRET = 'Blob service SAS URL';--Step 2 CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage WITH ( TYPE = BLOB_STORAGE, LOCATION = 'container URl without container name', CREDENTIAL= MyAzureBlobStorageCredential);--Step 3BULK INSERT TestFROM 'Test.dat'WITH ( DATA_SOURCE = 'MyAzureBlobStorage');Output-0 row(s) affected)Question 1.Is there any steps? I am missing?Question 2.How we can define file format? can we skip header from source file?Question 3.If we have multiple files in folder then how we can process?
• Anonymous
  February 26, 2017
  If you trying to access files under a blob container, you can specify the location as LOCATION = 'https://myazureblobstorage.blob.core.windows.net/mycontainername'
  • Anonymous
    March 22, 2017
    Now i am able to load single file. Is there any option if we have more than 1 file in folder then Load the all the files.We are not sure about how many files will be available for different-2 load.ThanksMohan
• Anonymous
  March 05, 2017
  Does it support only Shared Access Signature? I've tried to use Primary key, but it tails that password is wrong. Thanks!
• Anonymous
  March 14, 2017
  "Cannot bulk load because the file "random/product.csv" could not be opened. Operating system error code 1117(The request could not be performed because of an I/O device error.). "Same error for both BULK INSERT and OPENROWSET
• Anonymous
  August 21, 2017
  typo:Loading content of files form Azure Blob Storage account into a table in SQL Database is now single command: