Offloading Oracle Data to Object Storage and Mounting as External Table in an Autonomous Database | Part One: Azure Object Storage

Oracle autonomous database (ADW/ATP) offers several approaches for storing the data:

  • data online stored in the database internal storage
  • data nearline as external tables in object storage
  • data offline in object storage

The autonomous database supports object storage provided by Oracle OCI, Amazon S3 and Azure, with them you can create external tables, external partitioned tables and external hybrid partitioned tables.

In this post we are explaining the Azure option, in next episodes we’ll explain the other options. Let’s do it!

First, create a storage account in Azure:

Azure storage location

Create a container, in this case we are using the azure cli:

az storage container create -n rfid --account-name orcldata

Export data from Oracle to a file and upload to the storage container:

az storage blob upload -f rfid201901.json -c rfid -n rfid201901.json --account-name orcldata

Create an url for the uploaded file and grab the created url for later:

az storage blob url -c rfid -n rfid201901.json --account-name orcldata

Get the access key of the storage account:

In the database side, create a credential with the storage account information gathered:

     credential_name => 'rfidcredential',     
     username => 'orcldata',     
     password => 'YufGUh2sC...Pg=='   
 ); END;

Create the external table:

     table_name =>'rfid201901',
     credential_name =>'rfidcredential',
     file_uri_list =>'',
     format => json_object('delimiter' value '|', 'truncatecol' value 'True'),
     column_list => 'jsoncol varchar2(4000)'

Validate the external table and try to query some data:

SELECT jsoncol FROM rfid201901 where rownum < 10
JSON data shown

So far so good, the table was created! Now you can query data directly from the external storage or create new objects from it. Let’a create a table to query the JSON data we have exported in the example:

CREATE TABLE rfid (id RAW (16) NOT NULL, date_loaded TIMESTAMP WITH TIME ZONE, jsondata CLOB CONSTRAINT ensure_jsondata CHECK (jsondata IS JSON))

Now let’s populate the table:

insert into rfid (id, date_loaded, jsondata) select hextoraw(ora_hash(t.jsoncol)),sysdate, t.jsoncol from rfid201901 t

Done! Let’s see if it works:

select id, store,[0].t time,[0].its[0].co,[0].its[0].q
  from rfid r where rownum < 5
Query results

Cool, we are able to query raw data from the object storage and create staging data from it! Hope it helps 🙂

NOTE: This work have been done utilising an always free OCI account in Frankfurt


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.