What are the ways to get data from object storage to Oracle Autonomous Database?

Question

I am having CSV files in object storage with size > 85GB.

What are the efficient ways to load data into Oracle tables in Autonomous Database from CSV files in object storage.

Answer 1

Autonomous database has multiple data loading options from object store. There are UI tools as well as database sql packages. The external files can be in multiple formats like CSV, JSON, Parquet, AVRO, ORC, as well as exist in different supported object stores.

The DBMS_CLOUD PL/SQL Packages enables users to easily manage object store credentials as well as load data into Oracle tables.

https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/load-data-cloud-copy.html#GUID-76FC5A15-7ACC-4B59-87C0-20D4778E522D

A simple example would be:

#
# Store your object store credentials using the procedure DBMS_CLOUD.CREATE_CREDENTIAL.
#
SET DEFINE OFF
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => '[email protected]',
    password => 'password'
  );
END;
/

#
# Load data into an existing table using the procedure DBMS_CLOUD.COPY_DATA.
#
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/namespace-string/b/bucketname/o/channels.csv',
    format => json_object('type' value 'csv')
 );
END;
/

For a complete list of data loading options, you can refer to Autonomous Database (ADBS) Documentation:

• Chapter 3 Loading Data with Autonomous Database
• Topic: Loading Data from Files in the Cloud

https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/load-data.html#GUID-1351807C-E3F7-4C6D-AF83-2AEEADE2F83E

For a complete reference of DBMS_CLOUD package, you can refer to - https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/dbms-cloud-package.html#GUID-CE359BEA-51EA-4DE2-88DB-F21A9FC10721