0

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?

Javier González
  • 13
  • 3

2 Answers2

0

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.

Theofanis
  • 303
  • 2
  • 5
0

We were looking to combine data from multiple clients in Aurora MySQL into one table in Redshift for data analytics, just like you are.

We solved this by setting up a DMS task with a transformation rule to rename the schema to the same name for each client:

{
  "rule-id": "1",
  "rule-type": "transformation",
  "rule-name": "rename-schema",
  "rule-action": "rename",
  "rule-target": "schema",
  "object-locator": {
    "schema-name": <client-schema-name>,
  },
  "value": "combined_data"
}

and then setting DMS Task Target table preparation mode to Do nothing to avoid there being a race condition between the clients to clear out the table before starting the migration task.

We also added the client as a new column by adding a transformation rule to each table:

{
  "rule-id": "2",
  "rule-type": "transformation",
  "rule-name": "add-client-column",
  "rule-action": "add-column",
  "rule-target": "column",
  "object-locator": {
      "schema-name": <client-schema-name>,
      "table-name": <table-name>,
  },
  "value": "client",
  "data-type": {
      "type": "string",
      "length": 255
  },
  "expression": "'<client-schema-name>'"
}

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.Creating.html

jjt
  • 173
  • 4
  • 9