Load Data from Azure Blob Storage

This article explains how to download and upload blobs on Azure Blob Storage using the IMPORT and EXPORT commands.

Create connection

We recommend that you always use CREATE CONNECTION to define a connection and authenticate using the connection object in IMPORT/EXPORT commands. Replace the connection name, URL, and authentication details with your own values.

CREATE CONNECTION MY_BLOBSTORAGE
TO 'DefaultEndpointsProtocol=https;EndpointSuffix=core.windows.net'
USER '<AccountName>' 
IDENTIFIED BY '<AccountKey>';
<AccountName> Azure Blob Storage account name
<AccountKey> Azure Blob Storage account key

For information about how to get the correct account details, see View account access keys in the Microsoft Azure documentation.

For security reasons, make sure that you add your account key only in the IDENTIFIED BY field.

Import data

Run the following statement to import data from Azure Blob Storage.

IMPORT INTO table1 FROM CSV AT CLOUD AZURE BLOBSTORAGE MY_BLOBSTORAGE FILE '<container>/<blob>'

Export data

Run the following statement to export data to Azure Blob Storage.

EXPORT table1 INTO CSV AT CLOUD AZURE BLOBSTORAGE MY_BLOBSTORAGE FILE '<container>/<blob>'

File size limits

By default, the IMPORT and EXPORT commands will download or upload data blocks of 10 MiB in parallel using two threads. Because Azure Blob Storage allows a maximum of 50,000 blocks in a single blob, the maximum file size that you can import data to is ~500 GiB.

To change the default settings to allow a different maximum file size, edit the following database parameters:

etlAzureBlockSize

The minimum block size in bytes for Azure blob IMPORT/EXPORT operations. The default value is 10 MiB.

etlAzureMaxThreads

The maximum number of threads to use for Azure blob IMPORT/EXPORT operations. The default value is 2.

For information about how to edit database parameters, see Administration > Database Management in the section of this documentation for your selected deployment platform.

Invalid parameters will prevent the database from starting. To avoid unnecessary downtime, contact Support before you add or change any database parameters.