Load Data into Autonomous Database from Azure Blob Storage

This example shows you how to load data from Azure Blob Storage to Autonomous Database using SQL commands.

This example uses Azure access credentials for user account authentication and an object URL for accessing the object in your Azure Storage account container.

To load data from Azure Blob Storage:
  1. Create credentials for Azure user account in the Autonomous Database.

  2. Copy data from Azure Blob Storage to the database.

Topics

Prepare for Loading Data from Azure Blob Storage

Verify the prerequisites and prepare for loading data from Azure Blob Storage.

Prerequisites

A data file, for example, azure-data.txt exists in the Azure Storage account container that you can import. The sample file in this example has the following contents:
1,Azure Direct Sales
2,Azure Tele Sales
3,Azure Catalog
4,Azure Internet
5,Azure Partners

On the Azure side, log in to your Azure Storage account and do the following:

  1. Grant the required role, for example Storage Blob Data Contributor, to your Azure Storage account.
    For more information, see Assign Azure roles using the Azure portal.
  2. Obtain an access key for the Azure Storage account.
    For more information, see View account access keys.
  3. Obtain the object URL for the data file stored in the Azure Storage account container.

Steps for Loading Data from Azure Blob Storage

Run these steps to load data from Azure Blob Storage to Autonomous Database.

  1. Store the Azure Storage account credentials in your Autonomous Database and specify a credential name. This enables the database to authenticate with your Azure Storage account and access the items in the Azure Storage account container.
    SET DEFINE OFF
    BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'AZURE_CRED_NAME',
        username => 'username',
        password => 'password'
      );
    END;
    /

    Note:

    Here, the username is your Azure Storage account name and the password is your user access key.

    For detailed information about the parameters, see CREATE_CREDENTIAL Procedure.

    Creating a credential to access Azure Blob Storage is not required if you enable Azure service principal. See Use Azure Service Principal to Access Azure Resources for more information.

    Optionally, you can test the access to Azure Blob Storage as shown in this example.

    SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('AZURE_CRED_NAME', 'https://dbazure.blob.core.windows.net/my-azure-container/');
    
  2. Create a table in your database where you want to load the data.
    CREATE TABLE myazuretable (id NUMBER, name VARCHAR2(64));
    
  3. Import data from the Azure Blob Storage container to your Autonomous Database.
    Specify the table name and the Azure credential name followed by the Azure Blob Storage object URL.
    BEGIN
          DBMS_CLOUD.COPY_DATA(
              table_name => 'myazuretable',
              credential_name => 'AZURE_CRED_NAME',
              file_uri_list => 'https://dbazure.blob.core.windows.net/my-azure-container/azure-data.txt',
              format => json_object('delimiter' value ',')
          );
    END;
    /

    For detailed information about the parameters, see COPY_DATA Procedure.

    Moreover, you can also perform data loading into Autonomous Database from Azure Blob Storage using UI options. For more information, see Create an Microsoft Azure Cloud Store Location.

You have successfully imported data from Azure Blob Storage to your Autonomous Database. You can run this statement and verify the data in your table.
SELECT * FROM myazuretable;
ID  NAME
--  –-------------
 1  Azure Direct Sales
 2  Azure Tele Sales
 3  Azure Catalog
 4  Azure Internet
 5  Azure Partners

For more information about loading data, see Load Data from Files in the Cloud.