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:

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
/

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
  /

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