How to export data from AWS Aurora Postgres DB to Redshift?

Question

I have a Postgres DB hosted on AWS Aurora from which I need to retrieve data and insert it into Redshift.

My current approach is as follows:

Create an Aurora DB connection using Psycopg2.
With Aurora connection created above, query the Aurora DB table and export the resultset as a CSV file to S3 using OUTFILE
From S3, Redshift connection using Psycopg2.

I'm trying to optimize this by removing the S3 service and connecting Aurora to Redshift directly.

Here's what I want to do for which I couldn't find resources:

Query the Aurora table - table1 and directly export the result set into the Redshift table - table1.

I'm not even sure if this is possible with the current system. Any thoughts?

John Rotenstein · Accepted Answer · 2019-11-11 11:58:49Z

1

There are two ways to get data into an Amazon Redshift database:

COPY command to load from Amazon S3
INSERT statement to insert data provided as part of the SQL statement

The COPY method is recommended for normal data loading. It runs in parallel across slices and stores the data as efficiently as possible given that it is appending data.

The INSERT command is acceptable for a small number of inserts, but not a good idea for inserting lots of rows. Where possible, insert multiple rows at a time. It is acceptable to use INSERT ... SELECT statements, which can insert bulk data from a different table in one operation.

So, the only way to remove Amazon S3 from your operation is to code the data into an INSERT statement, but this is not an optimal way to load the data.

answered Nov 11, 2019 at 11:58

John Rotenstein

273k28 gold badges456 silver badges541 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Underoos Over a year ago

As per my current approach, I can upload the files to S3 and use COPY command to load them into Redshift. I'm just thinking if there is even better way to directly dump from Aurora DB to Redshift.

John Rotenstein Over a year ago

Nope. Only those two ways. Other tools can do it for you (eg AWS Database Migration Service), but they use the COPY command too.

Collectives™ on Stack Overflow

How to export data from AWS Aurora Postgres DB to Redshift?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related