How can I join tables from different schemas into a single table?

0
votes

I have multiple schemas (One for each client) with the same tables in Aurora - MySql. I want to collect the data from all the tables and create a new table in RedShift that would include all the columns from the source tables adding a new "schema" column that would be part of the primary key.

Example:

In Mysql I have:

Schema1.user (id, name) pk (id)

Schema2.user (id, name) pk (id)

...

Schema96.user (id, name) pk (id)

In Redshift I need:

dw.user (schema, id, name) pk (schema + id)

Is it possible to do this with transformation rules in AWS DMS?

1
aws-dms

1 Answers

0
votes

This looks like it is better done through a consolidation job in post-processing with Glue. Not sure you can do this with DMS currently, but you can explore the DMS transformations here.