Oracle Cloud Infrastructure Documentation

Load Data into Autonomous Database from Google Cloud Storage

This example shows how to load data from Google Cloud Storage to Autonomous Database.

You have various options to perform data loading into Autonomous Database, such as:

  • Using UI options: You can use the Data Studio Load tool user interface to create credentials for the cloud store location, select files containing data, and run data load jobs.

    See Loading Data from Cloud Storage

  • Using Rest Data Services APIs: You can use the Data Studio Load tool APIs to create links to the cloud store location and run data load jobs.

    See Using Data Studio Data Load APIs in PL/SQL

  • Using SQL commands as explained in this example.

All these methods use the same PL/SQL package DBMS_CLOUD for loading data. However, Data Studio provides additional benefits over SQL commands. It not only helps to analyze the source and create table definitions but also performs validation checks.

You require 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

Parent topic: Examples for Loading Data into 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.

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.