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.
CodePudding user response：
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.
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
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