Load Data into Autonomous Database from Google Cloud Storage

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

This example uses Google Cloud Platform (GCP) access credentials for user account authentication and an object URL for accessing the object in Google Cloud Storage bucket.

To load data from a Google Cloud Storage bucket:

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

2. Copy data from the Google Cloud Storage bucket to the database.

Topics

• Prepare for Loading Data from Google Cloud Storage
  Verify the prerequisites and prepare for loading data from Google Cloud Storage.
• Steps for Loading Data from Google Cloud Storage
  Run these steps to load data from Google Cloud Storage to Autonomous Database.

Prepare for Loading Data from Google Cloud Storage

Verify the prerequisites and prepare for loading data from Google Cloud Storage.

Prerequisites

A data file, for example, gcp-data.txt exists in the Google Cloud Storage bucket that you can import. The sample file in this example has the following contents:

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

On the Google side, log in to your Google Cloud Platform (GCP) account and do the following:

1. Create an access key for the user.
   For more information, see HMAC keys.
2. Obtain an object URL for the data file stored in the Google Cloud Storage bucket.
   For more information, see Request endpoints.

Steps for Loading Data from Google Cloud Storage

Run these steps to load data from Google Cloud Storage to Autonomous Database.

1. Store the Google Cloud Platform (GCP) access credentials in your Autonomous Database and specify a credential name. This enables the database to authenticate with your GCP user account and access the items in the Google Cloud Storage bucket.

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

   Note:

   Here, the username is your Google account access key and the password is your access key secret.

   For detailed information about the parameters, see CREATE_CREDENTIAL Procedure.

   Creating a credential to access Google Cloud Platform (GCP) is not required if you enable Google service account. See Use Google Service Account to Access Google Cloud Platform Resources for more information.

   Optionally, you can test the access to Google Cloud Storage bucket as shown in this example.

   SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('GOOGLE_CRED_NAME', 'https://bucketname.storage.googleapis.com/');
   

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

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

3. Import data from the Google Cloud Storage bucket to your Autonomous Database.
   Specify the table name and the GCP credential name followed by the Google Cloud Storage object URL.

   BEGIN
         DBMS_CLOUD.COPY_DATA(
             table_name => 'mygoogletable',
             credential_name => 'GOOGLE_CRED_NAME',
             file_uri_list => 'https://bucketname.storage.googleapis.com/gcp-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 Google Cloud Storage using UI options. For more information, see Create a Google Cloud Storage Location.

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

SELECT * FROM mygoogletable;

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

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