Load Data from Files in the Cloud

The PL/SQL package DBMS_CLOUD provides support for loading data from text, Parquet, and Avro files in the Cloud to your tables in Autonomous Data Warehouse.

Topics

The package DBMS_CLOUD supports loading from files in the following cloud services: Oracle Cloud Infrastructure Object Storage, Oracle Cloud Infrastructure Object Storage Classic, Azure Blob Storage, and Amazon S3.

Load Data - Create Credentials and Copy Data into an Existing Table

For data loading from files in the Cloud, you need to first store your object storage credentials in your Autonomous Data Warehouse and then use the procedure DBMS_CLOUD.COPY_DATA to load data.

The source file in this example, channels.txt, has the following data:

S,Direct Sales,Direct
T,Tele Sales,Direct
C,Catalog,Indirect
I,Internet,Indirect
P,Partners,Others
  1. Store your object store credentials using the procedure DBMS_CLOUD.CREATE_CREDENTIAL. For example:
    SET DEFINE OFF
    BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'DEF_CRED_NAME',
        username => 'adwc_user@example.com',
        password => 'password'
      );
    END;
    /

    This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name. Note that this step is required only once unless your object store credentials change. Once you store the credentials you can then use the same credential name for all data loads.

    For complete details and an example using Oracle Cloud Infrastructure Object Storage credentials, see Loading Your Data Into Autonomous Data Warehouse.

    For detailed information about the parameters, see CREATE_CREDENTIAL Procedure.

    Note:

    Some tools like SQL*Plus and SQL Developer use the ampersand character (&) as a special character. If you have the ampersand character in your password use the SET DEFINE OFF command in those tools as shown in the example to disable the special character and get the credential created properly.
  2. Load data into an existing table using the procedure DBMS_CLOUD.COPY_DATA. For example:
    CREATE TABLE CHANNELS
       (channel_id CHAR(1),
        channel_desc VARCHAR2(20),
        channel_class VARCHAR2(20)
       );
    /
    
    BEGIN
     DBMS_CLOUD.COPY_DATA(
        table_name =>'CHANNELS',
        credential_name =>'DEF_CRED_NAME',
        file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/adwc/b/adwc_user/o/channels.txt',
        format => json_object('delimiter' value ',')
     );
    END;
    /
    

    The parameters are:

    • table_name: is the target table‚Äôs name.

    • credential_name: is the name of the credential created in the previous step.

    • file_uri_list: is a comma delimited list of the source files you want to load.

    • format: defines the options you can specify to describe the format of the source file, including whether the file is of type text, Parquet, or Avro.

    For detailed information about the parameters, see COPY_DATA Procedure and COPY_DATA Procedure for Parquet or Avro Files.

Load Data – Monitor and Troubleshoot Loads

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

  • 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';

TABLE_NAME OWNER_NAME  TYPE   STATUS     START_TIME              UPDATE_TIME           LOGFILE_TABLE   BADFILE_TABLE
---------- ----------- ------- ---------- ---------------------- --------------------- --------------- -------------
CHANNELS   SH          COPY   COMPLETED  06-NOV-18 01.55.19.3    06-NOV-18 01.55.28.2  COPY$21_LOG     COPY$21_BAD

Using this SELECT statement with a WHERE clause predicate on the TYPE column, shows load operations with the type COPY.

The LOGFILE_TABLE column shows the name of the table you can query to look at the log of a load operation. For example, the following query shows the log of the load operation:

select * from COPY$21_LOG;

The column BADFILE_TABLE shows the name of the table you can query to look at the rows that got errors during loading. For example, the following query shows the rejected records for the load operation:

select * from COPY$21_BAD;

Depending on the errors shown in the log and the rows shown in the specified BADFILE_TABLE table you can correct the error by specifying the correct format options in DBMS_CLOUD.COPY_DATA.

Note:

The LOGFILE_TABLE and BADFILE_TABLE tables are stored for two days for each load operation and then removed automatically.

See DELETE_ALL_OPERATIONS Procedure for information on clearing the user_load_operations table.

Monitor and Troubleshoot Parquet and Avro File Loading

As with other data files, Parquet and Avro data loads generate logs that are viewable in the tables dba_load_operations and user_load_operations. Each load operation adds a record to dba[user]_load_operations that indicates the table containing the logs.

The log table provides summary information about the load.

Note:

For Parquet and Avro files, when the format parameter type is set to the value parquet or avro the BADFILE_TABLE table is always empty.

  • PRIMARY KEY constraint errors throw an ORA error.
  • If data for a column encounters a conversion error, for example, the target column is not large enough to hold the converted value, the value for the column is set to NULL. This does not produce a rejected record.

Load Data – List Credentials

The PL/SQL package DBMS_CLOUD provides the ability to store your object storage credentials in the database using the procedure DBMS_CLOUD.CREATE_CREDENTIAL. You can list credentials from the view ALL_CREDENTIALS.

For example, to list credentials, run the following command:

SELECT credential_name, username, comments FROM all_credentials;

CREDENTIAL_NAME                                            USERNAME    
---------------------------–-----------------------------  --------------------
COMMENTS
---------------------------–-----------------------------  --------------------
ADB_TOKEN                                                  user_name@example.com
{"comments":"Created via DBMS_CLOUD.create_credential"}
DEF_CRED_NAME                                              user_name@example.com
{"comments":"Created via DBMS_CLOUD.create_credential"}
 

See ALL_CREDENTIALS for more information.

Load Data – Delete Credentials

The PL/SQL package DBMS_CLOUD provides the ability to store your object storage credentials in the database using the procedure DBMS_CLOUD.CREATE_CREDENTIAL. You remove credentials with DBMS_CLOUD.DROP_CREDENTIAL.

For example, to remove the credential named DEF_CRED_NAME, run the following command:

BEGIN
   DBMS_CLOUD.DROP_CREDENTIAL('DEF_CRED_NAME');
END;

For more information about the DBMS_CLOUD procedures and parameters, see Summary of DBMS_CLOUD Subprograms.