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:

BEGIN   DBMS_CLOUD.CREATE_CREDENTIAL(     
     credential_name => 'rfidcredential',     
     username => 'orcldata',     
     password => 'YufGUh2sC...Pg=='   
 ); END;
 /

Create the external table:

BEGIN
    DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
     table_name =>'rfid201901',
     credential_name =>'rfidcredential',
     file_uri_list =>'https://orcldata.blob.core.windows.net/rfid/rfid201901.json',
     format => json_object('delimiter' value '|', 'truncatecol' value 'True'),
     column_list => 'jsoncol varchar2(4000)'
  );
 END;
 /

Validate the external table and try to query some data:

EXECUTE DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE ('rfid201901')
/
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 
      r.id id, r.jsondata.st store,
      r.jsondata.ch[0].t time,
      r.jsondata.ch[0].its[0].co,
      r.jsondata.ch[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

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

Leave a Reply

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

WordPress.com Logo

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

Google photo

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

Twitter picture

You are commenting using your Twitter 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.