Load Data into Autonomous Database from AWS S3

This example shows you how to load data from Amazon S3 object storage to Autonomous Database using SQL commands.

This example uses AWS access credentials for user account authentication and an object URL for accessing the object in Amazon S3 bucket.

To load data from an Amazon S3 bucket:

1. Create credentials for AWS user account in the Autonomous Database.

2. Copy data from the Amazon S3 bucket to the database.

Topics

• Prepare for Loading Data from AWS S3
  Verify the prerequisites and prepare for loading data from Amazon S3.
• Steps for Loading Data from AWS S3
  Run these steps to load data from Amazon S3 to Autonomous Database.

Prepare for Loading Data from AWS S3

Verify the prerequisites and prepare for loading data from Amazon S3.

Prerequisites

A data file, for example, data.txt exists in the Amazon S3 bucket that you can import. The sample file in this example has the following contents:

1,Direct Sales
2,Tele Sales
3,Catalog
4,Internet
5,Partners

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

1. Grant access privileges to the AWS IAM user for the Amazon S3 bucket.
   For more information, see Controlling access to a bucket with user policies.
2. Create an access key for the user.
   For more information, see Managing access keys for IAM users.
3. Obtain an object URL for the data file stored in the Amazon S3 bucket.
   For more information, see Accessing and listing an Amazon S3 bucket.

Steps for Loading Data from AWS S3

Run these steps to load data from Amazon S3 to Autonomous Database.

1. Store the AWS access credentials in your Autonomous Database and specify a credential name. This enables the database to authenticate with your AWS user account and access the items in the S3 bucket.

   SET DEFINE OFF
   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'AWS_CRED_NAME',
       username => 'username',
       password => 'password'
     );
   END;
   /

   Note:

   Here, the username is your AWS Access key ID and the password is your user access key.

   For detailed information about the parameters, see CREATE_CREDENTIAL Procedure.

   Creating a credential to access AWS resources is not required if you enable Amazon Resource Names (ARNs). See Use Amazon Resource Names (ARNs) to Access AWS Resources for more information.

   Optionally, you can test the access to S3 bucket as shown in this example.

   SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('AWS_CRED_NAME', ' https://aws-bucket-01.s3.amazonaws.com/');
   

2. Create a table in your database where you want to load the data.

   CREATE TABLE mytable (id NUMBER, name VARCHAR2(64));
   

3. Import data from the Amazon S3 bucket to your Autonomous Database.
   Specify the table name and the AWS credential name followed by the S3 object URL.

   BEGIN
         DBMS_CLOUD.COPY_DATA(
             table_name => 'mytable',
             credential_name => 'AWS_CRED_NAME',
             file_uri_list => https://aws-bucket-01.s3.amazonaws.com/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 Amazon S3 using UI options. For more information, see Create an Amazon S3 Cloud Store Location.

You have successfully imported data from Amazon S3 to your Autonomous Database. You can run this statement and verify the data in your table.

SELECT * FROM mytable;

ID  NAME
--  –-------------
 1  Direct Sales
 2  Tele Sales
 3  Catalog
 4  Internet
 5  Partners

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