In this post I will share to how to create an external table using the DBMS_CLOUD package and then run a query against a csv file uploaded into a Pure Storage FlashBlade S3 Object Storage bucket.
I have previously shared how to install and configure the Oracle DBMS_CLOUD package, if you want to follow this blog post, you may find it useful to review the above blog post first.
Upload Object
Let’s start by uploading a csv file using a previously created configuration and set of S3 Object Storage credentials created in my working with Oracle 19c database and on-premises S3 Object Storage post.
% ls -l airports.csv -rw-r--r--@ 1 rekins staff 4965317 3 Aug 15:44 airports.csv % wc -l airports.csv 72911 airports.csv
From the above we can see airports.csv is 72,911 rows and 4,965,317 bytes (4.7M).
Using the aws s3api utility we can upload the file into the S3 Object Storage, for example.
BUCKET=ora-bucket
ENDPOINT=https://s3-fbstaines02.amazonaws.com
s3File=airports.csv
aws s3api put-object --bucket ${BUCKET} --key ${s3File} --body /Users/rekins/s3/${s3File} --endpoint-url ${ENDPOINT} --profile fbstaines02 --ca-bundle pureCA.pemList Objects
We can confirm the object has been successfully upload with the aws s3api utility and list-objects option, for example.
BUCKET=ora-bucket
ENDPOINT=https://s3-fbstaines02.amazonaws.com
aws s3api list-objects --bucket ${BUCKET} --query 'Contents[].{Key: Key, Size: Size}' --endpoint-url ${ENDPOINT} --profile fbstaines02 --ca-bundle pureCA.pem
[
{
"Key": "airports.csv",
"Size": 4965317
}
]Create Table
Logon to an Oracle 19c Pluggable Database (PDB) schema with execute privileges on the DBMS_CLOUD package and S3 credentials configured.
Using the SQL below, create an external table, the example below creates an external table called airports_fb using the airports.csv file stored in an S3 Object Storage bucket called ora-bucket.
begin
dbms_cloud.create_external_table(
table_name => 'airports_fb',
credential_name => 'fb_cred',
file_uri_list => 'https://s3-fbstaines02.amazonaws.com/ora-bucket/airports.csv',
column_list => 'ident varchar2(20),
type varchar2(20),
name varchar2(20),
elevation_ft number(6),
continent varchar2(20),
iso_country varchar2(20),
iso_region varchar2(20),
municipality varchar2(20)',
format => json_object('type' value 'csv', 'skipheaders' value '1', 'ignoremissingcolumns' value 'true', 'conversionerrors' value 'store_null', 'rejectlimit' value 'unlimited')
);
end;
/
Query Table
Now we have created an external S3 table, let’s confirm the record count.
SQL> set linesize 200
SQL> select count(*) from airports_fb;
COUNT(*)
----------
72911
Elapsed: 00:00:00.94And attempt to run a query against the external table, for example list all London airports.
SQL> select name, iso_region, municipality from airports_fb where name like '%London%';
NAME ISO_REGION MUNICIPALITY
-------------------- -------------------- --------------------
London Airport CA-ON London
London Luton Airport GB-ENG London
London City Airport GB-ENG London
London Heliport GB-ENG London
London Colney GB-ENG
London Gliding Club GB-ENG Dunstable
New London Airport US-VA Forest
7 rows selected.
Elapsed: 00:00:00.98Explain Plan
If we describe the table is looks like any other table, for example
SQL> desc airports_fb;
Name Null? Type
----------------------------------------- -------- ---------------------------
IDENT VARCHAR2(20)
TYPE VARCHAR2(20)
NAME VARCHAR2(20)
ELEVATION_FT NUMBER(6)
CONTINENT VARCHAR2(20)
ISO_COUNTRY VARCHAR2(20)
ISO_REGION VARCHAR2(20)
MUNICIPALITY VARCHAR2(20)However, one way we can confirm our query is using an external table by creating an explain plan for it.
SQL> EXPLAIN PLAN FOR
2 select name, iso_region, municipality from airports_fb where name like '%London%' order by name;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 587737101
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (ORDER) | :TQ10001 |
| 3 | SORT ORDER BY | |
| 4 | PX RECEIVE | |
| 5 | PX SEND RANGE | :TQ10000 |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
| 6 | PX BLOCK ITERATOR | |
| 7 | EXTERNAL TABLE ACCESS FULL| AIRPORTS_FB |
--------------------------------------------------------
14 rows selected.SQLDeveloper
Alternatively, use SQLDeveloper to create a pretty, visual explain plan.
SQLDeveloper has some amazing functionality, one example is the ability to provide a simple way of inspecting an external tables Access Parameters and Location, where we can see the URL for the S3 Object Storage.
Table Update
So what happens if we try to update a row ?
SQL> update airports_fb set municipality = 'London' where name='London Colney';
update airports_fb set municipality = 'London' where name='London Colney'
*
ERROR at line 1:
ORA-30657: operation not supported on external organized tableOr, delete a row ?
SQL> delete from airports_fb where name='London Colney';
delete from airports_fb where name='London Colney'
*
ERROR at line 1:
ORA-30657: operation not supported on external organized tableAs expected they both fail as external tables don’t support DML, they are designed to be Read-Only, therefore any required updates need to be performed externally.
Summary
In this post I have demonstrated how a csv data file can be upload to an on-premises FlashBlade S3 Object Storage bucket and queried directly from an Oracle 19c database using the Oracle DBMS_CLOUD package.
If you want to learn how to download and upload objects to an S3 Object Storage from with an Oracle 19c database you may want to read this post next.