AWSDMS

AWS DMS – Target TableName Differs

AWS DMS is a tool that supports both homogenous and heterogeneous migration, helping to migrate to aws cloud.
During most of the migrations, the source and target table names remain the same, in which case the Mappings.json file is pretty simple. As an example (Oracle to PostgreSQL)

 {
    "rules":
    [
        {
            "rule-type": "selection",
            "rule-id": "1",
            "rule-name": "1",
            "object-locator":
            {
                "schema-name": "DEVO",
                "table-name": "TEST_DEMO"
            },
            "rule-action": "include"
        },
        {
            "rule-type": "transformation",
            "rule-id": "2",
            "rule-name": "2",
            "rule-action": "convert-lowercase",
            "rule-target": "schema",
            "object-locator":
            {
                "schema-name": "%"
            }
        },
        {
            "rule-type": "transformation",
            "rule-id": "3",
            "rule-name": "3",
            "rule-action": "convert-lowercase",
            "rule-target": "table",
            "object-locator":
            {
                "schema-name": "%",
                "table-name": "%"
            }
        },
        {
            "rule-type": "transformation",
            "rule-id": "4",
            "rule-name": "4",
            "rule-action": "convert-lowercase",
            "rule-target": "column",
            "object-locator":
            {
                "schema-name": "%",
                "table-name": "%",
                "column-name": "%"
            }
        }
    ]
}

The above Mappings.json, includes the table DEVO.TEST_DEMO to be migrated from Oracle to PostgreSQL, with the transformation of convert-lowercase for schema, table-name and column name.
But what if, the target table name is different, migrating from DEVO.TEST_DEMO to devo.test_demo_new. In such scenario, below Mappings.json can be used —

    {
      "rules": [
        {
          "rule-type": "selection",
          "rule-id": "1",
          "rule-name": "1",
          "object-locator": {
            "schema-name": "DEVO",
            "table-name": "TEST_DEMO"
          },
          "rule-action": "include"
        },
        {
          "rule-type": "transformation",
          "rule-id": "2",
          "rule-name": "2",
          "rule-action": "convert-lowercase",
          "rule-target": "schema",
          "object-locator": {
            "schema-name": "%"
          }
        },
        {
          "rule-type": "transformation",
          "rule-id": "3",
          "rule-name": "3",
          "rule-action": "rename",
          "rule-target": "table",
          "object-locator": {
            "schema-name": "devo",
            "table-name": "TEST_DEMO"
          },
          "value": "test_demo_new"
        },
        {
          "rule-type": "transformation",
          "rule-id": "4",
          "rule-name": "4",
          "rule-action": "convert-lowercase",
          "rule-target": "table",
          "object-locator": {
            "schema-name": "%",
            "table-name": "%"
          }
        },
        {
          "rule-type": "transformation",
          "rule-id": "5",
          "rule-name": "5",
          "rule-action": "convert-lowercase",
          "rule-target": "column",
          "object-locator": {
            "schema-name": "devo",
            "table-name": "test_demo",
            "column-name": "%"
          }
        }
      ]
    }

Leave a Reply