5.1.7 Loading Data From the Oracle Cloud (Autonomous Database Only)

Load data from the Oracle Cloud using Object Store References. (This function is only available for Autonomous Database users.)

• Granting Privileges to the Workspace Schema
  Before you can load data from the cloud, you must grant privileges to the workspace schema.
• User Interface Differences for Oracle Cloud Users
  For Oracle Cloud users, Data Workshop includes an additional Load from Cloud button on the Data Workshop home page.
• About Object Store References
  Data Workshop uses Object Store References to reference cloud-based URLs for loading data. Use Object Store References to create External Tables from the Cloud, which you can then import into your workspace or connect to your applications.
• Creating an Object Store Reference
  Create a new Object Store Reference.
• Creating an External Table from Cloud
  Create a table from an external source with an Object Store Reference.
• Loading Avro or Parquet Files into Object Browser from Cloud
  For Avro and Parquet files, first prepare an external table then load it into your workspace for local use.
• Loading Other Files into Object Browser from Cloud
  For non-Avro and non-Parquet files, view the Object Store Reference in Data Workshop then import the file into your workspace for local use.

5.1.7.1 Granting Privileges to the Workspace Schema

Before you can load data from the cloud, you must grant privileges to the workspace schema.

Grant the following privileges to the workspace schema:

• EXECUTE ON DBMS_CLOUD

• READ,WRITE ON DIRECTORY DATA_PUMP_DIR

For example, run the following as ADMIN:

GRANT EXECUTE ON DBMS_CLOUD TO {schema};
GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR TO {schema};

Where:

• schema is the workspace schema

See Also:

• DBMS_CLOUD Subprograms and REST APIs in Using Oracle Autonomous Database on Shared Exadata Infrastructure

• GRANT in Oracle Database SQL Language Reference

5.1.7.2 User Interface Differences for Oracle Cloud Users

For Oracle Cloud users, Data Workshop includes an additional Load from Cloud button on the Data Workshop home page.

APEX On-premise users only see Load Data and Unload Data buttons on the Data Workshop home page.

Oracle Cloud users also see the Load from Cloud button.

Description of the illustration load_from_cloud_button.png

5.1.7.3 About Object Store References

Data Workshop uses Object Store References to reference cloud-based URLs for loading data. Use Object Store References to create External Tables from the Cloud, which you can then import into your workspace or connect to your applications.

Creating an Object Store Reference is the first step to creating external tables in the cloud. Essentially, the Object Store Reference preserves the necessary credentials to authorize your APEX workspace to access external data, and therefore enable your apps to access external data.

You can store the following types of credentials in an Object Store Reference:

• Username/Password - Basic two-field username and password.
• OCI Native - Use the Oracle Cloud Infrastructure (OCI) protocol: User OCID, Private Key, Tenancy OCID, and Fingerprint.
• Web Credential - Reuse a web credential already stored in the workspace.
• DBMS_CLOUD Credential - Reuse a DBMS_CLOUD credential already stored in the workspace.

For Parquet and Avro files, Data Workshop asks for an external table name and where to derive the schema. Other file types can be up to 50MB.

When using an Object Store Reference to create an external table, APEX calls the data using the DBMS_CLOUD.CREATE_EXTERNAL_TABLE procedure.

See Also:

DBMS_CLOUD CREATE_EXTERNAL_TABLE Procedure in Using Oracle Autonomous Database on Shared Exadata Infrastructure

5.1.7.4 Creating an Object Store Reference

Create a new Object Store Reference.

Required

Before you begin, ensure you know the URL of the Object Store Bucket you want to call from Autonomous Data Warehouse.

Procedure

To create a new Object Store Reference:

1. Click the Create New button.
   The Object Store Reference dialog displays.
2. Enter the following information:
   1. URL - The URL is the URI of the Object Store bucket excluding the filename.

      Note:

      To learn more about the DBMS_CLOUD Package File URI format, see DBMS_CLOUD Package File URI Formats in Using Oracle Autonomous Database on Shared Exadata Infrastructure
   2. Reference Name - Copied from the URL field. You can change the Reference Name to something easier, such as Project Bucket.
   3. Credential Type - Select the authentication scheme used to access the Object Store bucket.

      Tip:

      For Amazon Web Services (AWS):

      • Username - Access Key ID
      • Password - Secret access key
   4. Enter the requested credentials for the selected Credential Type (options vary between credential type).

      Note:

      Enable Create a Web Credential to create a reusable authorization stored in the workspace. Web Credentials are useful for granting access to a data source without providing the full username and password, for example.
   5. Click Apply Changes.

The Object Store References page reloads with the success message Object Store Reference saved.

5.1.7.5 Creating an External Table from Cloud

Create a table from an external source with an Object Store Reference.

To view an Object Store Reference:

1. In Data Workshop, click Load from Cloud.
2. Select the Object Store Reference from the drop-down menu.
   The associated Object Store Bucket loads. A list of object names populates below.

To create an external table:

3. Click the filename of an object. Click more than one name to select multiple files to externalize.

   The Load File from Object Store dialog displays below. The selected filename appears in the File Name Pattern field.

   If selecting multiple files, the names appear in the order selected, separated by commas.

   Tip:

   Click the X icon to clear the File Name Pattern field of all selections.

4. Configure the Load File from Object Store section:
   1. File Type - Select whether the object file type is Avro, Parquet, or Other.
   2. Derive Data Definition - Choose First File to apply the schema to the rest of the batch. Choose All Files to preserve multiple schemas.
   3. External Table Name - Enter a name for the table.
   4. Click Create External Table.

   APEX loads the data (depending on size, this may take some time).

5. When loading is complete, do one of the following:
   • To review the table, click View Table. The Review External Table page displays.
   • To launch the Create App wizard, click Create Application.

5.1.7.6 Loading Avro or Parquet Files into Object Browser from Cloud

For Avro and Parquet files, first prepare an external table then load it into your workspace for local use.

To create an external table:

1. In Data Workshop, click Load from Cloud.
2. Select an Object Store Reference from the drop-down menu.
   The associated Object Store Bucket loads. A list of object names populates below.
3. Click the object names you wish to import.

   If selecting multiple files, the names appear in the order selected, separated by commas.

   Tip:

   Click the X icon to clear the File Name Pattern field of all selections.

4. For File Type, select Avro or Parquet.
5. Click Create External Table.
   The Review External Table page displays.

To load the data to a local table:

6. On the Review External Table page under Load Data, configure how APEX imports the external table into the workspace:
   1. Load Into - Choose whether to create a New Table or overwrite an Existing Table.
   2. New Table Name - Enter a name for the table. Click the Configure button to choose which columns to load.
   3. Monitor Progress - Toggle whether to view a progress bar during import. (Useful for large tables.)
   4. Commit Interval - Choose the rate of row inserations at which a COMMIT operation is executed.
   5. Drop External Table after Load - Enable to retain the external table afterward. Disable to drop the selected external table.
   A preview of the final import version displays at the bottom.
7. Click Load Data to Local Table.

5.1.7.7 Loading Other Files into Object Browser from Cloud

For non-Avro and non-Parquet files, view the Object Store Reference in Data Workshop then import the file into your workspace for local use.

To import the file to your workspace from Cloud:

1. In Data Workshop, click Load from Cloud.
2. Select an Object Store Reference from the drop-down menu.
   The associated Object Store Bucket loads. A list of object names populates below.
3. Click the name of the object you wish to import.
4. For File Type, select Other.
5. Click Import.

   APEX loads the data.

   The Load Data dialog displays.

6. Configure how APEX imports the resulting table:
   1. Load To - Choose whether to create a New Table or overwrite an Existing Table.
   2. Table Owner - Choose a table owner.
   3. Table Name - Name the table. Note: For new tables, the name must be unique to the workspace.
   A preview of the first few rows displays at the bottom of the dialog.
7. Click Load Data.