How to query OCI Object Storage data from your on-premises Oracle 19c database

I have previous shared how we can access Avro file with Oracle BigData types from our on-premises Oracle database using either a NFS or local filesystem.

I have also blogged on how to configure and access Object Storage from your on-premises Oracle 19c database using DBMS_CLOUD to query Avro, ORC, Parquet and other data files

In this post I will show how we can query Avro data stored in Oracle Cloud Infrastructure (OCI) Object Storage bucket.

Oracle Cloud

Let’s start by logging on to our OCI account, using the hamburger menu navigate to Storage -> Object Storage & Archive Storage -> Buckets.

Select bucket name, and then click the 3 vertical pips to the right of the object to identifying the URL

In the Object Details Basic Information we can see the URL Path (URI), for example:

We can see the format of the URL is thus:

https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucketname>/o/<object name>

Create Table

Using the DBMS_CLOUD.CREATE_EXTERNAL_TABLE package we can create a table, in this example the format is the self describing parquet format so the syntax could not be simpler.

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'sales_extended_ext',
    credential_name =>'OCI_TOKEN',
    file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/<namespace>/b/<bucket>/o/sales_extended.parquet',
    format =>  '{"type":"parquet", "schema": "first"}'
   );
END;
/

We can now describe the table

SQL> desc sales_extended_ext;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID					    NUMBER(10)
 CUST_ID					    NUMBER(10)
 TIME_ID					    VARCHAR2(4000)
 CHANNEL_ID					    NUMBER(10)
 PROMO_ID					    NUMBER(10)
 QUANTITY_SOLD					    NUMBER(10)
 AMOUNT_SOLD					    NUMBER(10,2)
 GENDER 					    VARCHAR2(4000)
 CITY						    VARCHAR2(4000)
 STATE_PROVINCE 				    VARCHAR2(4000)
 INCOME_LEVEL					    VARCHAR2(4000)

Count number of rows in the table

SQL> select count(1) from SALES_EXTENDED_EXT;

  COUNT(1)
----------
    916039

Elapsed: 00:00:01.12

And select a few rows,

SET LINESIZE 200
SET TIMING ON

COLUMN gender FORMAT A10 HEADING 'Gender'
COLUMN city FORMAT A30 HEADING 'City'
COLUMN income_level FORMAT A30 HEADING 'Income Level'
COLUMN state_province FORMAT A20 HEADING 'State'

select prod_id, quantity_sold, gender, city, state_province, income_level from sales_extended_ext where rownum < 10;

   PROD_ID QUANTITY_SOLD Gender     City			   State		Income Level
---------- ------------- ---------- ------------------------------ -------------------- ------------------------------
	13	       1 M	    Adelaide			   South Australia	K: 250,000 - 299,999
	13	       1 M	    Dolores			   CO			L: 300,000 and above
	13	       1 M	    Cayuga			   ND			F: 110,000 - 129,999
	13	       1 F	    Bergen op Zoom		   Noord-Brabant	C: 50,000 - 69,999
	13	       1 F	    Neuss			   Nordrhein-Westfalen	J: 190,000 - 249,999
	13	       1 F	    Darwin			   Northern Territory	F: 110,000 - 129,999
	13	       1 M	    Sabadell			   Barcelona		K: 250,000 - 299,999
	13	       1 F	    Orangeville 		   IL			C: 50,000 - 69,999
	13	       1 M	    Gennevilliers		   Ile-de-France	D: 70,000 - 89,999

9 rows selected.

Elapsed: 00:00:01.09
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

---

Summary

In this post I have shared how we can use the new DBMS_CLOUD package which became available with Oracle 19.9 to query data stored in an Object Store.

[twitter-follow screen_name=’RonEkins’ show_count=’yes’]