Programmatically exchange of files with AWS S3 using Invantive's UniversalSQL

Wiki (en)
,
1

AWS Simple Storage Service (S3) for Data Storage

Invantive UniversalSQL has allowed storage of binary objects in tables for years. However, with increasing volumes of data processed and stored for long periods, a specific store for such BLOBs scales better for lower cost.

AWS provides the Simple Storage Service (“S3”). AWS S3 combines low cost, extreme scalability and high parallel performance. AWS S3 is accessible through an API.

This topic explains how to exchange binary data through the AWS S3 APIs using familiar Invantive UniversalSQL and PSQL-statements.

The associated package aws_s3 is available for use in all free and premium subscriptions starting release 24.0 and 23.1.240-BETA.

Configure AWS S3

The first step is to configure the AWS S3 credentials using aws_s3.configure:

declare
  l_client aws_s3_client;
begin
  l_client := aws_s3.configure
  ( 'eu-west-1' /* Or another region. */
  , 'AK...token to identify...NS'
  , 'wj9...some secret token...UW'
  ); 
...

The AWS S3 configuration can also be taken from system-wide settings unless Invantive UniversalSQL runs on a infrastructure shared with other companies. In that case, the configuration is enforced to have be done using aws_s3.configure.

Download Data using SQL with AWS S3

To download data with AWS S3 with SQL-statements the following code can be executed:

declare
  l_client  aws_s3_client;
  l_payload blob;
begin
  ... call to aws_s3.configure
  --
  -- Download binary data from S3.
  --
  l_payload := aws_s3.download_object
  ( l_client
  , 'my-bucket-name'
  , 'my-file.txt'
  );
end;

Upload Data from SQL to AWS S3

To upload data to AWS S3 with SQL-statements the following code can be executed:

declare
  l_client  aws_s3_client;
  l_payload blob;
begin
  ... call to aws_s3.configure
  --
  -- Upload all files in a folder to AWS S3.
  --
  for r
  in
  ( select basename(fle.file_path) key
    ,      rfe.file_contents payload
    from   files@os('c:\temp', '*.pdf', false) fle
    join   read_file@os(fle.file_path) rfe
    on     rfe.file_contents is not null    
  )
  loop
    dbms_output.put_line('Upload ' || r.key);
    aws_s3.upload_object(l_client, 'my-bycket-name', r.key, r.payload);
  end loop;
end;

Copy an Object

To copy an object within or across buckets, the following code can be used:

declare
  l_client  aws_s3_client;
begin
  ... call to aws_s3.configure
  --
  -- Copy data between S3 buckets or within an S3 bucket.
  --
  aws_s3.copy_object
  ( l_client
  , 'source-bucket-name'
  , 'my-file.txt'
  , 'target-bucket-name'
  , 'folder/my-file.txt'
  -- Optional storage class.
  -- Optional content type.
  );

Retrieve List of (Directory) Buckets

To retrieve the list of buckets in AWS S3, the following code can be used:

declare
  l_client aws_s3_client;
begin
  ... call to aws_s3.configure
  --
  for r
  in
  ( select name
    ,      creation_date
    from   table(aws_s3.list_buckets(l_client))
  )
  loop
    dbms_output.put_line(r.name);
  end loop;
end;

Create Table with Objects Stored in Bucket

To create a table with the objects stored in the S3 bucket, the following code can be used:

declare
  l_client aws_s3_client;
begin
  ... call to aws_s3.configure
  --
  create or replace table MyObjectsInS3@inmemorystorage
  as
  select *
  from   table(aws_s3.list_objects(l_client, 'bucket-name'))
  ;
end;

SQL-only Get List of Objects in Bucket

Although receiving and memorizing the S3 client configuration in a variable improves performance, it is also possible to use a SQL-only solution as in:

select name
,      creation_date
from   table
       ( aws_s3.list_buckets
         ( aws_s3.configure
           ( 'eu-west-1' /* Or another region. */
           , 'AK...token to identify...NS'
           , 'wj9...some secret token...UW'
           )
         )
       )