Loading Your Data Into Autonomous Transaction Processing

Before You Begin

This tutorial shows you how to load data from an Oracle Object Store into a database in Autonomous Transaction Processing. This tutorial takes approximately 15 minutes to complete.

Oracle Autonomous Transaction Processing Tutorial Series

This is the third in a series of tutorials for Autonomous Transaction Processing. Perform the tutorials sequentially.

Provisioning Autonomous Transaction Processing
Connecting SQL Developer to Autonomous Transaction Processing
Loading Your Data Into Autonomous Transaction Processing
Using Oracle Machine Learning with Autonomous Data Warehouse Cloud Service (set of additional tutorials)

Background

You can load data into Autonomous Transaction Processing using Oracle Database tools, and Oracle and 3rd party data integration tools. You can load data:

from files local to your client computer, or
from files stored in a cloud-based object store

For the fastest data loading experience Oracle recommends uploading the source files to a cloud-based object store, such as Oracle Cloud Infrastructure Object Storage, before loading the data into your Autonomous Transaction Processing database.

To load data from files in the cloud into your Autonomous Transaction Processing database, use the new PL/SQL DBMS_CLOUD package. The DBMS_CLOUD package supports loading data files from the following Cloud sources: Oracle Cloud Infrastructure Object Storage, Oracle Cloud Infrastructure Object Storage Classic, and Amazon AWS S3.

This tutorial shows how to load data from Oracle Cloud Infrastructure Object Storage using two of the procedures in the DBMS_CLOUD package:

create_credential: Stores the object store credentials in your Autonomous Transaction Processing schema.

You will use this procedure to create object store credentials in your Autonomous Transaction Processing atpc_user schema that you defined in a previous tutorial.

copy_data: Loads the specified source file to a table. The table must already exist in your Autonomous Transaction Processing database.

You will use this procedure to load tables in the atpc_user schema with data from data files staged in the Oracle Cloud Infrastructure Object Storage cloud service.
This tutorial shows how to load data to SH tables (sales history tables from an Oracle sample schema: SALES, COSTS, TIMES, PRODUCTS, CHANNELS, PROMOTIONS, CUSTOMERS, COUNTRIES, SUPPLEMENTARY_DEMOGRAPHICS).
For more information about loading data, see the documentation Loading Data from Files in the Cloud.

What Do You Need?

Access to a database in your Autonomous Transaction Processing cloud service. See previous tutorials in this series and the documentation: Using Oracle Autonomous Transaction Processing.
Data files already uploaded to a staging area; otherwise follow the steps in section 1, below, to upload your data files to the Oracle Cloud Infrastructure Object Storage service.

To use data files already in an object store, your cloud administrator must provide you the object store credentials and the URL path to the files that you will be copying to your Autonomous Transaction Processing tables.
If you will be uploading data files to an object store in Oracle Cloud Infrastructure Object Storage, your cloud administrator must provide you the cloud tenant name, and user name and password with read/write privileges to the object store location where the data is to be stored.

Oracle SQL Developer (see Oracle Technology Network download site). Version 18.3 or later. Versions 18.2 or later contain enhancements for key Autonomous Transaction Processing features.
Note:
If you are a Windows user on 64-bit platform, download the 'Windows 64-bit with JDK 8 included' distribution as it includes the files necessary to run SQL Developer and connect to your Autonomous Transaction Processing database.

If you are using a SQL Developer version earlier than 18.2, see the documentation topic Connecting with Oracle SQL Developer (earlier than Version 18.2).

Upload Data Files to Your Object Store

Upload to your cloud-based object store the data files that you want to load to your Autonomous Transaction Processing database. This tutorial uses an object store in the Oracle Cloud Infrastructure Object Storage service.

Log in to your Oracle Cloud Infrastructure Console with the following credentials provided by your Oracle Cloud administrator: cloud tenant, user name, password.

sign in with user name and password — Description of the illustration log_in_to_oci_object_storage_with_username_password

Select Object Storage from the menu at the top left of the Oracle Cloud Infrastructure console. Select Object Storage from the sub-menu.

select Object Storage — Description of the illustration select_object_storage

Select a compartment in which to create a bucket to upload your database table data.

select a compartment — Description of the illustration select_a_compartment

Click Create Bucket to create the storage bucket in which to upload your source files. You will later copy this staged data into tables in your Autonomous Transaction Processing database.

create a bucket — Description of the illustration create_a_bucket

Enter a bucket name, select the standard storage tier, and click Create Bucket.

create a bucket dialog — Description of the illustration create_a_bucket_dialog

Click Upload Object to begin selecting the data files to upload to the bucket.

click Upload Object — Description of the illustration click_upload_object

Navigate to the location of the data files on your local computer. Drag and drop each file individually or click Upload Object to upload each file individually.
This example uploads the data files of the SH tables (sales history tables from an Oracle sample schema). Click here to download a zip file of the 10 SH data files for you to upload to the object store. Unzip the data files from the zip file, because zip files cannot be uploaded. Upload each unzipped data file individually.
Note: Alternatively, you can use curl commands to upload large numbers of files.

upload data files to bucket — Description of the illustration upload_data_files_to_bucket

The data files are uploaded to the bucket. These files staged in the cloud are ready to be copied into the tables of your Autonomous Transaction Processing database. Remain logged in to Oracle Cloud Infrastructure Object Storage.

uploaded data files in bucket — Description of the illustration uploaded_data_files_in_bucket

Create an Object Store Auth Token

To load data from an Oracle Cloud Infrastructure Object Storage object store, you need to create an Auth Token for your object store account. The communication between your Autonomous Transaction Processing database and the object store relies on the Auth Token and username/password authentication.

If you have logged out of Oracle Cloud Infrastructure Object Storage, log back in with the following credentials provided by your Oracle Cloud administrator: cloud tenant, user name, password.
Hover your mouse cursor over the human figure icon at the top right of the console and click User Settings from the drop-down menu.

click user settings — Description of the illustration click_user_settings

Click Auth Tokens under Resources on the left of the console.

click Auth Tokens — Description of the illustration click_auth_tokens

Click Generate Token.

click generate token — Description of the illustration click_generate_token

A pop-up dialog appears. Set the Auth Token by performing the following steps:

In the pop-up dialog, enter a description.
Click the Generate Token button.
Copy the generated token to a text file. The token does not appear again.
Click Close.

enter description and click Generate Token — Description of the illustration enter_descriptions_and_click_generate_token

copy the generated token — Description of the illustration copy_the_generated_token

Create Object Store Credentials in your Autonomous Transaction Processing Schema

Now that you have created an object store Auth Token, store in your Autonomous Transaction Processing atpc_user schema the credentials of the object store in which your data is staged.

Open SQL Developer and connect to your Autonomous Transaction Processing database as user atpc_user. See the previous tutorial in this series, Connecting to SQL Developer and Creating Tables, for steps to connect SQL Developer to your Autonomous Transaction Processing database as atpc_user.
In a SQL Developer worksheet, use the create_credential procedure of the DBMS_CLOUD package to store the object store credentials in your atpc_user schema.

Create a credential name. You reference this credential name in the copy_data procedure in the next step.
Specify the credentials for your Oracle Cloud Infrastructure Object Storage service: The username and the object store Auth Token you generated in the previous step.
```
begin
  DBMS_CLOUD.create_credential (
    credential_name => 'OBJ_STORE_CRED',
    username => '<your username>',
    password => '<your Auth Token>'
  ) ;
end;
/
```

create credential in ADWC schema — Description of the illustration create_credential_in_adwc_schema

After you run this script, your object store's credentials are stored in your Autonomous Transaction Processing atpc_user schema.

Copy Data from Object Store to Autonomous Transaction Processing Database Tables

The copy_data procedure of the DBMS_CLOUD package requires that target tables must already exist in in your Autonomous Transaction Processing database. In the previous tutorial in this series, Connecting SQL Developer and Creating Tables, you created in your Autonomous Transaction Processing atpc_user schema all of the target tables.

Now run the copy_data procedure to copy the data staged in your object store to your Autonomous Transaction Processing atpc_user tables.

In a SQL Developer worksheet, use the copy_data procedure of the DBMS_CLOUD package to copy the data staged in your object store.
- For credential_name, specify the name of the credential you defined in section 3, Create Object Store Credentials in your Autonomous Transaction Processing Schema.
- For file_uri_list, specify the URL that points to the location of the file staged in your object store. The URL is structured as follows. The values you specify are in bold:
  https://swiftobjectstorage.<region name>.oraclecloud.com/v1/<tenant name>/<bucket name>/<file name>
- Click here for an example script. In the script, use your own table names, region name, tenant name, bucket name, and file names.
  Note: The region name, tenant name, and bucket name can all be found in one place by clicking the ellipsis option menu and going to file details.
  Note: If you receive an error message that your atpc_user does not have read/write privileges into the Object Store, you may need to properly set up your user privileges or contact your administrator to do so.
  
  Description of the illustration data_loading_script
After you run the procedure, observe that the data has been copied from the object store to the tables in your Autonomous Transaction Processing database.

Description of the illustration result_of_loading_table

Confirm Your Data Load

All data load operations done using the PL/SQL package DBMS_CLOUD are logged in the tables dba_load_operations and user_load_operations. These tables contain the following:

dba_load_operations: shows all load operations.
user_load_operations: shows the load operations in your schema.

Query these tables to see information about ongoing and completed data loads. For example:

SELECT table_name, owner_name, type, status, start_time, update_time, 
logfile_table, badfile_table 

FROM user_load_operations WHERE type = 'COPY';

Examine the results. The log and bad files are accessible as tables:

TABLE_NAME      STATUS        ROWS_LOADED  LOGFILE_TABLE   BADFILE_TABLE
----------      ------------  -----------  -------------   -------------
CHANNELS        FAILED                     COPY$1_LOG      COPY$1_BAD
CHANNELS        COMPLETED              5   COPY$2_LOG      COPY$2_BAD

Next Tutorial

Using Oracle Machine Learning with Autonomous Data Warehouse Cloud Service

Want to Learn More?

For more information on monitoring and troubleshooting loads, see the documentation topic Managing and Monitoring Performance of Autonomous Transaction Processing.
Autonomous Transaction Processing website