Following up the previous post, we are explaining today how to make same thing using Amazon S3. The procedure and steps are quite similar, let’s go and try!
Amazon side setup
Create a S3 bucket with no public access:

Upload datafile:

Grab the object URL for later:


Create a user in IAM:

Grant access to S3 for the new user created:

Create an access key:

Oracle autonomous database side setup
Create the credential with the access key id and secret created above:
BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => 's3rfidcredential', username => 'AKIA...ZFAP', password => 'r...J' ); END; /
Create the external table with the credential name and the url of the datafile uploaded to S3:
BEGIN DBMS_CLOUD.CREATE_EXTERNAL_TABLE( table_name =>'s3rfid201901', credential_name =>'s3rfidcredential', file_uri_list =>'https://orcldata.s3.eu-central-1.amazonaws.com/rfid201901.json', format => json_object('delimiter' value '|', 'truncatecol' value 'True'), column_list => 'jsoncol varchar2(4000)' ); END; /
Validate the external table:
EXECUTE DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE ('s3rfid201901') /
Query a few rows to see if the external table is working:
SELECT jsoncol FROM s3rfid201901 where rownum < 10

Now, let’s create a staging table from the external table:
CREATE TABLE s3rfid (id RAW (16) NOT NULL, date_loaded TIMESTAMP WITH TIME ZONE, jsondata CLOB CONSTRAINT s3_ensure_jsondata CHECK (jsondata IS JSON)) /
Now we populate the staging table:
insert into s3rfid (id, date_loaded, jsondata) select hextoraw(ora_hash(t.jsoncol)),sysdate, t.jsoncol from s3rfid201901 t where rownum < 400000 /
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 azurfid r where r.jsondata.st = '40466' minus 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 s3rfid r where r.jsondata.st = '40466' /

At this point, from previous and current post we have created 2 external tables (azurfid201901, s3rfid201901) and 2 staging tables(azurfid, s3rfid) already, all of them with the same origin datafile. Let’s do some checks, it is interesting to note that, with the same number of I/O requests, S3 is 10 times quicker than Azure, but it probably depends on where the object storage is located:







Full scan to the staging tables has obviously petty much the same response time:


That’s all for today, in next post we’ll do the same utilising Oracle OCI Object Storage. Hope it helps! 🙂
NOTE: This work have been done utilising an always free OCI account in Frankfurt

Next steps:
– test OCI Object Storage
– test free to use with Always Free Tiers: Oracle Data Visualization Desktop (aka Oracle Analytics Desktop) https://technology.amis.nl/2019/10/08/oracle-data-visualization-desktop-connecting-to-oracle-cloud-always-free-autonomous-database/
🙂
LikeLike
Thanks, the OCI use case is almost ready to be published early next week
LikeLike