This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what the DBMS_CLOUD package is and how its used.
The DBMS_CLOUD package is what lets your Oracle database connect with cloud storage services like Amazon S3, Azure Blob Storage, and Oracle Cloud Infrastructure Object Storage
What is DBMS_CLOUD?
DBMS_CLOUD is a package introduced in Oracle Database 21c (and back ported to 19.10) that lets you work with files stored in cloud storage services directly from your database. With DBMS_CLOUD, you can easily load data from and store data in the cloud without ETL tools or middle-tier servers.
Key Features of DBMS_CLOUD
Load data from cloud storage into your database
Store data from your database into cloud storage
List, delete, and rename files in cloud storage
Supports major cloud storage providers (Amazon S3, Azure Blob Storage, Oracle Cloud Infrastructure Object Storage)
Getting Started with DBMS_CLOUD
To start using DBMS_CLOUD, you'll need to follow these steps:
Prerequisites:
Set up an Oracle database (DBMS_CLOUD is included in Autonomous DB and can be installed in 19c+ on-premises)
this is the MOS note showing how to install it: How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)
Set up an object storage account with your cloud provider
Create Credentials:
Use the CREATE_CREDENTIAL procedure to securely store the access details for your cloud storage. Example:
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OBJ_STORE_CRED1',
username => 'oracle-cloud-username',
password => 'auth-token'
);
END;
/
Loading Data:
DBMS_CLOUD provides the COPY_DATA procedure to load data from files in cloud storage into database tables. Here are a few examples:
Loading data from a CSV file:
BEGIN
DBMS_CLOUD.COPY_DATA(
table_name => 'EMPLOYEES',
credential_name => 'OBJ_STORE_CRED',
file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/employees.csv',
format => json_object('type' value 'CVS', 'skipheaders' value '1')
);
END;
/
Loading data from multiple CSV files using wildcards:
BEGIN
DBMS_CLOUD.COPY_DATA(
table_name => 'EMPLOYEES',
credential_name => 'OBJ_STORE_CRED',
file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/sales_*.csv',
format => json_object('type' value 'CVS', 'skipheaders' value '1')
);
END;
/
Loading data from a JSON file:
BEGIN
DBMS_CLOUD.COPY_DATA(
table_name => 'PRODUCTS',
credential_name => 'OBJ_STORE_CRED',
file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/products.json',
format => json_object('type' value 'json', 'compression' value 'gzip'));
END;
/
Exporting Data:
The EXPORT_DATA procedure allows you to export data from database tables to files in cloud storage. Here are a couple of examples:
Exporting data to a CSV file:
BEGIN
DBMS_CLOUD.EXPORT_DATA(
credential_name => 'OBJ_STORE_CRED',
file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/emp_export.csv',
query => 'SELECT * FROM employees',
format => json_object('type' value 'csv', 'compression' value 'gzip')
);
END;
/
Exporting data to a JSON file:
BEGIN
DBMS_CLOUD.EXPORT_DATA(
credential_name => 'OBJ_STORE_CRED',
file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/emp_export.json',
query => 'SELECT * FROM employees',
format => json_object('type' value 'json')
);
END;
/
Creating External Tables
DBMS_CLOUD allows you to create external tables that directly query data in cloud storage without loading it into the database. Here's an example:
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
table_name => 'SALES_DATA',
credential_name => 'OBJ_STORE_CRED',
file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/sales_*.csv',
format => json_object('type' value 'csv', 'skipheaders' value '1', 'delimiter' value ','),
column_list => 'SALE_ID NUMBER,
CUSTOMER_ID NUMBER,
PRODUCT_ID NUMBER,
QUANTITY NUMBER,
SALE_DATE DATE,
PRICE NUMBER(10,2)'
);
END;
/
Creating Partitioned External Tables:
You can create partitioned external tables using the CREATE_EXTERNAL_PART_TABLE procedure. Here's an example:
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
table_name => 'EXT_SALES',
credential_name => 'OBJ_STORE_CRED',
format => json_object('delimiter' value ',', 'skipheaders' value '1'),
column_list => 'SALE_ID NUMBER, SALE_DATE DATE, AMOUNT NUMBER',
partitioning_clause => 'PARTITION BY RANGE (SALE_DATE)
(PARTITION SALES_2020 VALUES LESS THAN (TO_DATE(''01-01-2021'', ''DD-MM-YYYY''))
LOCATION (''https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/sales_2020.csv''),
PARTITION SALES_2021 VALUES LESS THAN (TO_DATE(''01-01-2022'', ''DD-MM-YYYY''))
LOCATION (''https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/sales_2021.csv''))'
);
END;
/
Creating Hybrid Partitioned Tables:
Hybrid partitioned tables allow you to have some partitions stored in the database and others in cloud storage. Here's an example:
BEGIN
DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
table_name => 'HYBRID_SALES',
credential_name => 'OBJ_STORE_CRED',
format => json_object('delimiter' value ',', 'skipheaders' value '1'),
column_list => 'SALE_ID NUMBER, SALE_DATE DATE, AMOUNT NUMBER',
partitioning_clause => 'PARTITION BY RANGE (SALE_DATE)
(PARTITION SALES_2020 VALUES LESS THAN (TO_DATE(''01-01-2021'', ''DD-MM-YYYY''))
EXTERNAL LOCATION (''https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/bucket/o/sales_2020.csv''),
PARTITION SALES_2021 VALUES LESS THAN (TO_DATE(''01-01-2022'', ''DD-MM-YYYY''))
EXTERNAL LOCATION (''https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/bucket/o/sales_2021.csv''),
PARTITION SALES_2022 VALUES LESS THAN (TO_DATE(''01-01-2023'', ''DD-MM-YYYY'')))'
);
END;
/
There are a handful of other procedures in DBMS_CLOUD like copy, delete, move, put and more. You can learn more about the procedures in the docs: Oracle DBMS_CLOUD docs
Conclusion:
DBMS_CLOUD is an easy way for organizations looking to integrate their Oracle databases with cloud storage services.
Additional Resources:
-Killian
Kommentare