Redigera

Dela via

Examples of bulk access to data in Azure Blob Storage

  • Artikel

Applies to: SQL Server 2017 (14.x) and later Azure SQL Database Azure SQL Managed Instance

The BULK INSERT and OPENROWSET statements can directly access a file in Azure Blob Storage. The following examples use data from a CSV (comma separated value) file (named inv-2017-01-19.csv), stored in a container (named Week3), stored in a storage account (named newinvoices).

All the paths to the container and to the files on Blob Storage are case-sensitive. If not correct, it might return an error like the following example:

Cannot bulk load. The file "file.csv" does not exist or you don't have file access rights.

Create the credential

The external data source must be created with a database scoped credential that uses the SHARED ACCESS SIGNATURE identity. To create a shared access signature (SAS) for your storage account, see the Shared access signature property on the storage account property page in the Azure portal. For more information on shared access signatures, see Grant limited access to Azure Storage resources using shared access signatures (SAS). For more information on credentials, see CREATE DATABASE SCOPED CREDENTIAL.

Create a database scoped credential using IDENTITY, which must be SHARED ACCESS SIGNATURE. Use the SAS token generated for the Blob Storage account. Verify that your SAS token doesn't have a leading ?, that you have at least read permission on the object that should be loaded, and that the expiration period is valid (all dates are in UTC time).

For example:

CREATE DATABASE SCOPED CREDENTIAL UploadInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 'sv=2018-03-28&ss=b&srt=sco&sp=rwdlac&se=2019-08-31T02:25:19Z&st=2019-07-30T18:25:19Z&spr=https&sig=KS51p%2BVnfUtLjMZtUTW1siyuyd2nlx294tL0mnmFsOk%3D';

Known issues

Requests from Azure SQL Database and Azure SQL Managed Instance using SAS tokens might be blocked with the following error:

Msg 4861, Level 16, State 1, Line 27
Cannot bulk load because the file "FileName.extension" could not be opened. Operating system error code 5(Access is denied.).

Only a subset of Azure services are currently on the trusted services list. For a complete list of trusted services and updates on Azure storage firewall settings, see Trusted access for resources registered in your subscription.

Examples

Along with the examples in this article, you can also review the Azure SQL Database import data samples on GitHub.

Access data in a CSV file referencing an Azure Blob Storage location

The following example uses an external data source pointing to an Azure storage account, named MyAzureInvoices.

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://newinvoices.blob.core.windows.net',
    CREDENTIAL = UploadInvoices
);

Then the OPENROWSET statement adds the container name (week3) to the file description. The file is named inv-2017-01-19.csv.

SELECT * FROM OPENROWSET(
   BULK 'week3/inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   FORMAT = 'CSV',
   FORMATFILE = 'invoices.fmt',
   FORMATFILE_DATA_SOURCE = 'MyAzureInvoices'
) AS DataFile;

Using BULK INSERT, use the container and file description:

BULK INSERT Colors2
FROM 'week3/inv-2017-01-19.csv'
WITH (DATA_SOURCE = 'MyAzureInvoices', FORMAT = 'CSV');

Access data in a CSV file referencing a container in an Azure Blob Storage location

The following example uses an external data source pointing to a container (named week3) in an Azure storage account.

CREATE EXTERNAL DATA SOURCE MyAzureInvoicesContainer
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
    CREDENTIAL = UploadInvoices
);

Then the OPENROWSET statement doesn't include the container name in the file description:

SELECT * FROM OPENROWSET(
   BULK 'inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoicesContainer',
   FORMAT = 'CSV',
   FORMATFILE = 'invoices.fmt',
   FORMATFILE_DATA_SOURCE = 'MyAzureInvoices'
) AS DataFile;

Using BULK INSERT, don't use the container name in the file description:

BULK INSERT Colors2
FROM 'inv-2017-01-19.csv'
WITH (DATA_SOURCE = 'MyAzureInvoicesContainer', FORMAT = 'CSV');

Related content