This answer assumes you already have Glue connections to both Aurora and Redshift set up and have created a crawler for the Aurora database with an include path similar to "schema/%" to fetch all tables in that schema.
Given that, when you create a Glue Job in the console, you select a single table from the Glue Data Catalog as the source. As the target, you choose to "Create tables in your data target" and select the Redshift connection previously created and specify the target table. Once that Glue Job is created, you can create a Trigger to run it on a scheduled basis.
Now you're set up for one table, but if you wanted to do this for every table in that schema, you would have to modify the Glue script and either hard-code the list of tables that you want to sync and loop through them in the code, or you could also fetch the list of tables from the Glue API using the boto3 client if you're using pyspark.
If you want to run this continuously, there are a couple things to mention:
- For tables that have sequential primary keys, you can enable job bookmarks as mentioned here such that only new data is inserted into Redshift: https://docs.aws.amazon.com/glue/latest/dg/monitor-continuations.html
- If that's not an option, you'll likely need to truncate the table on every run. You can do this by adding a "preactions" key to the connection options when writing the dynamic frame.
"preactions":"truncate table target_table;"
Without doing one of those, duplicate data will continue to get loaded into Redshift.
For ongoing replication, Database Migration Service may be an option here. I realize there's more infrastructure to set up, but it replicates data using the binary logs and can be more efficient as a result. It can also do this continuously if near-real-time replication is important to you.
DMS from Aurora to RedshiftAccepted Answerasked 5 years ago
Continuously replicate AWS Aurora MySQL to RedshiftAccepted Answerasked 3 years ago
Aurora Serverless MySQL to access the binlog or Activity Streamsasked a year ago
Incremental Copy of RDS Aurora MySql Table to RedShiftasked 4 months ago
Migrate MySQL 8 to Aurora MySQL 8asked 9 months ago
COPY from S3 to Redshift with manifest failsasked 9 months ago
Migrate MySQL 5.5 to AWSAccepted AnswerEXPERTasked 4 years ago
AWS Glue load data from S3 to Redshiftasked 6 months ago
AWS Aurora MYSQL enabling Federated Serverasked 3 months ago
Need inputs on Glue to move data from Aurora MySQL to Redshiftasked 6 months ago