AWS – Database Migration Service – Wildcard Include and Exclude Table

AWS Database Migration Services(DMS) provides an easy to use the web interface to create a database migration task.

One can do wildcard include(All tables in a Schema) and exclude certain tables. DMS provides an option to Map Tables via Wizard or JSON Editor.

Here is how the UI looks like:

While it is less effort in creating one database migration task with all tables belonging to one schema also know as wildcard include, for all practical reasons, you may want to move certain tables to a different task. For example, exclude tables without primary keys to a different task. This can be achieved using the Table Mapping Wizard. But when you have too many tables to be excluded, using Mapping Wizard is not an easy option, so an alternative approach is using JSON Editor.

Here is an example of JSON to include schema SCOTT and exclude tables – STG_EMP_TASKS, STG_EMP_BENEFITS, and STG_EMP_ASSIGNMENTS

{
  "rules": [
    {
      "rule-type": "transformation",
      "rule-id": "1",
      "rule-name": "1",
      "rule-target": "schema",
      "object-locator": {
        "schema-name": "SCOTT"
      },
      "rule-action": "rename",
      "value": "SCOTT",
      "old-value": null
    },
    {
      "rule-type": "selection",
      "rule-id": "2",
      "rule-name": "2",
      "object-locator": {
        "schema-name": "SCOTT",
        "table-name": "%"
      },
      "rule-action": "include",
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "3",
      "rule-name": "3",
      "object-locator": {
        "schema-name": "SCOTT",
        "table-name": "STG_EMP_TASKS"
      },
      "rule-action": "exclude",
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "4",
      "rule-name": "4",
      "object-locator": {
        "schema-name": "SCOTT",
        "table-name": "STG_EMP_BENEFITS"
      },
      "rule-action": "exclude",
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "5",
      "rule-name": "5",
      "object-locator": {
        "schema-name": "SCOTT",
        "table-name": "STG_EMP_ASSIGNMENTS"
      },
      "rule-action": "exclude",
      "filters": []
    }
  ]
}

For someone familiar with Goldengate, this rule-action – exclude is equivalent to TABLEEXCLUDE parameter of goldengate.

Here is the link to AWS – DMS documentation – https://aws.amazon.com/dms/