AWS DMS - join source tables while migrating

Question

My current requirement is to migrate an on premise Oracle DB to Aurora MySql in AWS. One question I had was, is it possible to join data from multiple tables in source during the migration. For example, can 2 tables be joined and the resulting data be loaded to a single table in target using DMS.

JimmyL JimmyL · Accepted Answer · 2018-09-27T04:44:30

This isn't a feature of DMS.

DMS works on a table by table basis, and the method of transform is defined on the basis of schemas, tables, and columns.

I attempted to try and migrate two source tables that shared a key into one target table using the transformations available in DMS:

That is, TABLE1.ID=TABLE2.ID but both tables have other columns that are not in common.

I created a task as follows attempting to rename TABLE2 to TABLE1:

{
    "rules": [{
        "rule-type": "selection",
        "rule-id": "1",
        "rule-name": "1",
        "object-locator": {
            "schema-name": "TESTSCHEMA",
            "table-name": "TABLE1"
        },
        "rule-action": "include"
    },
    {
        "rule-type": "selection",
        "rule-id": "2",
        "rule-name": "2",
        "object-locator": {
            "schema-name": "TESTSCHEMA",
            "table-name": "TABLE2"
        },
        "rule-action": "include"
    },
    {
        "rule-type": "transformation",
        "rule-id": "3",
        "rule-name": "3",
        "rule-target": "table",
        "object-locator": {
            "schema-name": "TESTSCHEMA",
            "table-name": "TABLE2"
        },
        "rule-action": "rename",
        "value": "TABLE1"
    }]
}

One table succeeded and the other table failed.

AWS DMS - join source tables while migrating

1 Answers