how to write a Trigger to insert data from aurora to redshift

Question

I am having some data in aurora mysql db, I would like to do two things:

HISTORICAL DATA:

1. To read the data from aurora(say TABLE A) do some processing and update some columns of a table in redshift(say TABLE B).

ALSO,

LATEST DAILY LOAD

2. To have a trigger like condition where whenever a new row is inserted in aurora table A then a trigger should update the columns in redshift table B with some processing.

what should be the best approach to handle such situation. Please understand I don't have a simple read and insert situation , I also have to perform some process as well between read and write.

Answer 1

Not sure if you have already solved the issue and if so please share the details.

We are looking at following approach

1. A cron will write the daily data batch into s3 (say 1 month or order)
2. Upon s3 arrival, load that file into Redshift via copy command (https://docs.aws.amazon.com/redshift/latest/dg/tutorial-loading-run-copy.html)

Looking for more ideas/thoughts for sure.