S3 import into RDS Postgres

I wanted to test the functionality to pull S3 data into a Postgres RDS database so i’ve set-up a test that pulls a csv file from a s3 bucket into the RDS instance the RDS instance needs to have the s3Import feature set-up via a role, see the screen shot below 

So we first create the extension 

strata=> CREATE EXTENSION aws_s3 CASCADE;
NOTICE:  installing required extension “aws_commons”
CREATE EXTENSION

Next we create a table to store the data 

strata=> CREATE TABLE userstest (username text, email text, email_verified text, emaildate text, usercreatedate text, userlastmodifieddate text, enabled text);
CREATE TABLE
Time: 73.205 ms

We now import the data from the s3 bucket

strata=> SELECT aws_s3.table_import_from_s3(
   'userstest',
   'username,email,email_verified,emaildate,usercreatedate,userlastmodifieddate,enabled',
   'DELIMITER '',''',
   aws_commons.create_s3_uri('dba-refresh-datapump', 'COGNITO_unconfirmed_users.csv', 'eu-west-1')
);
                                        table_import_from_s3
----------------------------------------------------------------------------------------------------
 687 rows imported into relation "userstest" from file COGNITO_unconfirmed_users.csv of 73697 bytes
(1 row)

Time: 313.070 ms

We can now query the data in the table

strata=> select * from userstest limit 5;
               username               |            email            | email_verified | emaildate  | usercreatedate | userlastmodifieddate |   enabled
--------------------------------------+-----------------------------+----------------+------------+----------------+----------------------+-------------
 9d001911-4af9-4bde-a88c-b7ba013a82d7 | testingmail@outlook.com     | FALSE          | 23/09/2020 | 23/09/2020     | TRUE                 | UNCONFIRMED
 82a610ca-6b2c-4207-98a4-1bbabf96dcb4 | testingmail@aol.com         | FALSE          | 16/09/2020 | 16/09/2020     | TRUE                 | UNCONFIRMED
 843d3d15-f29c-44d4-a2be-d33cd1c458c3 | testingmail@btinternet.com  | FALSE          | 25/09/2020 | 25/09/2020     | TRUE                 | UNCONFIRMED
 ab35563f-95bf-4852-961e-526ae7115b41 | testingmail@hotmail.co.uk   | FALSE          | 22/09/2020 | 22/09/2020     | TRUE                 | UNCONFIRMED
 c87cc479-8eeb-4fc5-a920-b0b65f91bbff | testingmail@talktalk.net    | FALSE          | 22/09/2020 | 22/09/2020     | TRUE                 | UNCONFIRMED
(5 rows)

Time: 54.084 ms

Simple as that 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s