Set up Oracle UTL_FILE functionality on Aurora PostgreSQL-Compatible - AWS Prescriptive Guidance

Set up Oracle UTL_FILE functionality on Aurora PostgreSQL-Compatible

Created by Rakesh Raghav (AWS) and anuradha chintha (AWS)

Environment: PoC or pilot

Source: Oracle

Target: Aurora PostgreSQL

R Type: Re-architect

Workload: Oracle

Technologies: Migration; Infrastructure; Databases

AWS services: Amazon S3; Amazon Aurora

Summary

As part of your migration journey from Oracle to Amazon Aurora PostgreSQL-Compatible Edition on the Amazon Web Services (AWS) Cloud, you might encounter multiple challenges. For example, migrating code that relies on the Oracle UTL_FILE utility is always a challenge. In Oracle PL/SQL, the UTL_FILE package is used for file operations, such as read and write, in conjunction with the underlying operating system. The UTL_FILE utility works for both server and client machine systems. 

Amazon Aurora PostgreSQL-Compatible is a managed database offering. Because of this, it isn't possible to access files on the database server. This pattern walks you through the integration of Amazon Simple Storage Service (Amazon S3) and Amazon Aurora PostgreSQL-Compatible to achieve a subset of UTL_FILE functionality. Using this integration, we can create and consume files without using third-party extract, transform, and load (ETL) tools or services.

Optionally, you can set up Amazon CloudWatch monitoring and Amazon SNS notifications.

We recommend thoroughly testing this solution before implementing it in a production environment.

Prerequisites and limitations

Prerequisites 

  • An active AWS account

  • AWS Database Migration Service (AWS DMS) expertise

  • Expertise in PL/pgSQL coding

  • An Amazon Aurora PostgreSQL-Compatible cluster

  • An S3 bucket

Limitations 

This pattern doesn't provide the functionality to act as a replacement for the Oracle UTL_FILE utility. However, the steps and sample code can be enhanced further to achieve your database modernization goals.

Product versions

  • Amazon Aurora PostgreSQL-Compatible Edition 11.9

Architecture

Target technology stack

  • Amazon Aurora PostgreSQL-Compatible

  • Amazon CloudWatch

  • Amazon Simple Notification Service (Amazon SNS)

  • Amazon S3

Target architecture 

The following diagram shows a high-level representation of the solution.

Data files are uploaded to an S3 bucket, processed using the aws_s3 extension, and sent to the Aurora instance.
  1. Files are uploaded from the application into the S3 bucket.

  2. The aws_s3 extension accesses the data, using PL/pgSQL, and uploads the data to Aurora PostgreSQL-Compatible.

Tools

  • Amazon Aurora PostgreSQL-Compatible – Amazon Aurora PostgreSQL-Compatible Edition is a fully managed, PostgreSQL-compatible, and ACID-compliant relational database engine. It combines the speed and reliability of high-end commercial databases with the cost-effectiveness of open-source databases.

  • AWS CLI – The AWS Command Line Interface (AWS CLI) is a unified tool to manage your AWS services. With only one tool to download and configure, you can control multiple AWS services from the command line and automate them through scripts.

  • Amazon CloudWatch – Amazon CloudWatch monitors Amazon S3 resources and use.

  • Amazon S3 – Amazon Simple Storage Service (Amazon S3) is storage for the internet. In this pattern, Amazon S3 provides a storage layer to receive and store files for consumption and transmission to and from the Aurora PostgreSQL-Compatible cluster.

  • aws_s3 – The aws_s3 extension integrates Amazon S3 and Aurora PostgreSQL-Compatible.

  • Amazon SNS – Amazon Simple Notification Service (Amazon SNS) coordinates and manages the delivery or sending of messages between publishers and clients. In this pattern, Amazon SNS is used to send notifications.

  • pgAdmin – pgAdmin is an open-source management tool for Postgres. pgAdmin 4 provides a graphical interface for creating, maintaining, and using database objects.

Code

To achieve the required functionality, the pattern creates multiple functions with naming similar to UTL_FILE. The Additional information section contains the code base for these functions.

In the code, replace testaurorabucket with the name of your test S3 bucket. Replace us-east-1 with the AWS Region where your test S3 bucket is located.

Epics

TaskDescriptionSkills required
Set up IAM policies.

Create AWS Identity and Access Management (IAM) policies that grant access to the S3 bucket and objects in it. For the code, see the Additional information section.

AWS administrator, DBA
Add Amazon S3 access roles to Aurora PostgreSQL.

Create two IAM roles: one role for read and one role for write access to Amazon S3. Attach the two roles to the Aurora PostgreSQL-Compatible cluster: 

  • One role for the S3Export feature

  • One role for the S3Import feature

For more information, see the Aurora PostgreSQL-Compatible documentation on importing and exporting data to Amazon S3.

AWS administrator, DBA
TaskDescriptionSkills required
Create the aws_commons extension.

The aws_commons extension is a dependency of the aws_s3 extension.

DBA, Developer
Create the aws_s3 extension.

The aws_s3 extension interacts with Amazon S3.

DBA, Developer
TaskDescriptionSkills required
Test importing files from Amazon S3 into Aurora PostgreSQL.

To test importing files into Aurora PostgreSQL-Compatible, create a sample CSV file and upload it into the S3 bucket. Create a table definition based on the CSV file, and load the file into the table by using the aws_s3.table_import_from_s3 function.

DBA, Developer
Test exporting files from Aurora PostgreSQL to Amazon S3.

To test exporting files from Aurora PostgreSQL-Compatible, create a test table, populate it with data, and then export the data by using the aws_s3.query_export_to_s3 function.

DBA, Developer
TaskDescriptionSkills required
Create the utl_file_utility schema.

The schema keeps the wrapper functions together. To create the schema, run the following command.

CREATE SCHEMA utl_file_utility;
DBA, Developer
Create the file_type type.

To create the file_type type, use the following code.

CREATE TYPE utl_file_utility.file_type AS (     p_path character varying(30),     p_file_name character varying );
DBA/Developer
Create the init function.

The init function initializes common variable such as bucket or region. For the code, see the Additional information section.

DBA/Developer
Create the wrapper functions.

Create the wrapper functions fopen, put_line, and fclose. For code, see the Additional information section.

DBA, Developer
TaskDescriptionSkills required
Test the wrapper functions in write mode.

To test the wrapper functions in write mode, use the code provided in the Additional information section.

DBA, Developer
Test the wrapper functions in append mode.

To test the wrapper functions in append mode, use the code provide in the Additional information section.

DBA, Developer

Related resources

Additional information

Set up IAM policies

Create the following policies.

Policy nameJSON
S3IntRead
{     "Version": "2012-10-17",     "Statement": [         {             "Sid": "S3integrationtest",             "Effect": "Allow",             "Action": [                 "s3:GetObject",                 "s3:ListBucket"             ],             "Resource": [          "arn:aws:s3:::testaurorabucket/*",          "arn:aws:s3:::testaurorabucket"             ]         }     ] }
S3IntWrite
{     "Version": "2012-10-17",     "Statement": [         {             "Sid": "S3integrationtest",             "Effect": "Allow",             "Action": [                 "s3:PutObject",                                 "s3:ListBucket"             ],             "Resource": [                "arn:aws:s3:::testaurorabucket/*",                "arn:aws:s3:::testaurorabucket"             ]         }     ] }

Create the init function

To initialize common variables, such as bucket or region, create the init function by using the following code.

CREATE OR REPLACE FUNCTION utl_file_utility.init(     )     RETURNS void     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ BEGIN       perform set_config       ( format( '%s.%s','UTL_FILE_UTILITY', 'region' )       , 'us-east-1'::text       , false );       perform set_config       ( format( '%s.%s','UTL_FILE_UTILITY', 's3bucket' )       , 'testaurorabucket'::text       , false ); END; $BODY$;

Create the wrapper functions

Create the fopen, put_line, and fclose wrapper functions.

fopen

CREATE OR REPLACE FUNCTION utl_file_utility.fopen(     p_file_name character varying,     p_path character varying,     p_mode character DEFAULT 'W'::bpchar,     OUT p_file_type utl_file_utility.file_type)     RETURNS utl_file_utility.file_type     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ declare     v_sql character varying;     v_cnt_stat integer;     v_cnt integer;     v_tabname character varying;     v_filewithpath character varying;     v_region character varying;     v_bucket character varying; BEGIN     /*initialize common variable */     PERFORM utl_file_utility.init();     v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );     v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );         /* set tabname*/     v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );     v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;     raise notice 'v_bucket %, v_filewithpath % , v_region %', v_bucket,v_filewithpath, v_region;         /* APPEND MODE HANDLING; RETURN EXISTING FILE DETAILS IF PRESENT ELSE CREATE AN EMPTY FILE */     IF p_mode = 'A' THEN         v_sql := concat_ws('','create temp table if not exists ', v_tabname,' (col1 text)');         execute v_sql;         begin         PERFORM aws_s3.table_import_from_s3             ( v_tabname,             '',               'DELIMITER AS ''#''',             aws_commons.create_s3_uri             (     v_bucket,                 v_filewithpath ,                 v_region)             );         exception             when others then              raise notice 'File load issue ,%',sqlerrm;              raise;         end;         execute concat_ws('','select count(*) from ',v_tabname) into v_cnt;         IF v_cnt > 0         then             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;         else                     PERFORM aws_s3.query_export_to_s3('select ''''',                             aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)                                           );             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;                 end if;         v_sql := concat_ws('','drop table ', v_tabname);                 execute v_sql;                 ELSEIF p_mode = 'W' THEN             PERFORM aws_s3.query_export_to_s3('select ''''',                             aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)                                           );             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;     END IF;         EXCEPTION         when others then             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;             raise notice 'fopenerror,%',sqlerrm;             raise; END; $BODY$;

put_line

CREATE OR REPLACE FUNCTION utl_file_utility.put_line(     p_file_name character varying,     p_path character varying,     p_line text,     p_flag character DEFAULT 'W'::bpchar)     RETURNS boolean     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ /************************************************************************** * Write line, p_line in windows format to file, p_fp - with carriage return * added before new line. **************************************************************************/ declare     v_sql varchar;     v_ins_sql varchar;     v_cnt INTEGER;     v_filewithpath character varying;     v_tabname  character varying;     v_bucket character varying;     v_region character varying;     BEGIN  PERFORM utl_file_utility.init(); /* check if temp table already exist */  v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );  v_sql := concat_ws('','select count(1) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace where n.nspname like ''pg_temp_%'''                          ,' AND pg_catalog.pg_table_is_visible(c.oid) AND Upper(relname) = Upper( '''                          ,  v_tabname ,''' ) ');    execute v_sql into v_cnt;     IF v_cnt = 0 THEN          v_sql := concat_ws('','create temp table ',v_tabname,' (col text)');         execute v_sql;         /* CHECK IF APPEND MODE */         IF upper(p_flag) = 'A' THEN             PERFORM utl_file_utility.init();                                     v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );             v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );                         /* set tabname*/                         v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;                                     begin                PERFORM aws_s3.table_import_from_s3                      ( v_tabname,                           '',                          'DELIMITER AS ''#''',                         aws_commons.create_s3_uri                            ( v_bucket,                                v_filewithpath,                                v_region    )                     );             exception                 when others then                     raise notice  'Error Message : %',sqlerrm;                     raise;             end;             END IF;         END IF;     /* INSERT INTO TEMP TABLE */                   v_ins_sql := concat_ws('','insert into ',v_tabname,' values(''',p_line,''')');     execute v_ins_sql;     RETURN TRUE;     exception             when others then                 raise notice  'Error Message : %',sqlerrm;                 raise; END; $BODY$;

fclose

CREATE OR REPLACE FUNCTION utl_file_utility.fclose(     p_file_name character varying,     p_path character varying)     RETURNS boolean     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ DECLARE     v_filewithpath character varying;     v_bucket character varying;     v_region character varying;     v_tabname character varying; v_sql character varying; BEGIN       PERFORM utl_file_utility.init();       v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );     v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );     v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );     v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;     raise notice 'v_bucket %, v_filewithpath % , v_region %', v_bucket,v_filewithpath, v_region ;         /* exporting to s3 */     perform aws_s3.query_export_to_s3         (concat_ws('','select * from ',v_tabname,'  order by ctid asc'),             aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)         );    v_sql := concat_ws('','drop table ', v_tabname);     execute v_sql;        RETURN TRUE; EXCEPTION        when others then      raise notice 'error fclose %',sqlerrm;      RAISE; END; $BODY$;

Test your setup and wrapper functions

Use the following anonymous code blocks to test your setup.

Test the write mode

The following code writes a file named s3inttest in the S3 bucket.

do $$ declare l_file_name varchar := 's3inttest' ; l_path varchar := 'integration_test' ; l_mode char(1) := 'W'; l_fs utl_file_utility.file_type ; l_status boolean; begin select * from utl_file_utility.fopen( l_file_name, l_path , l_mode ) into l_fs ; raise notice 'fopen : l_fs : %', l_fs; select * from utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket: for test purpose', l_mode ) into l_status ; raise notice 'put_line : l_status %', l_status; select * from utl_file_utility.fclose( l_file_name , l_path ) into l_status ; raise notice 'fclose : l_status %', l_status; end; $$

Test the append mode

The following code appends lines onto the s3inttest file that was created in the previous test.

do $$ declare l_file_name varchar := 's3inttest' ; l_path varchar := 'integration_test' ; l_mode char(1) := 'A'; l_fs utl_file_utility.file_type ; l_status boolean; begin select * from utl_file_utility.fopen( l_file_name, l_path , l_mode ) into l_fs ; raise notice 'fopen : l_fs : %', l_fs; select * from utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket: for test purpose : append 1', l_mode ) into l_status ; raise notice 'put_line : l_status %', l_status; select * from utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket : for test purpose : append 2', l_mode ) into l_status ; raise notice 'put_line : l_status %', l_status; select * from utl_file_utility.fclose( l_file_name , l_path ) into l_status ; raise notice 'fclose : l_status %', l_status; end; $$

Amazon SNS notifications

Optionally, you can set up Amazon CloudWatch monitoring and Amazon SNS notifications on the S3 bucket. For more information, see Monitoring Amazon S3 and Setting up Amazon SNS Notifications.