DMS Transformation and Mapping Rules Example - Oracle to PostgreSQL

Question

Hi,

I am wanting to migrate some tables from Oracle to PostgreSQL. Only 5 out of 50 tables.
I used ora2pg to generate the DDL and created the tables.

I only want to migrate some columns of these 5 tables. Is this possible? 
I think it is possible to filter the tables to migrate just a list of tables but not sure about the columns.
Can i have just one DMS to do all these and has to be one DMS for each table?
It's a pain to have to use one DMS for each as there is no option to copy a DMS task.

If anyone know of any link out there that may have discussed how to do this, please advise. Preferably a step by step instruction of some sort. 
I've been searching for awhile now :-)

Answer

for more information please refer

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.html#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.Values

if you more columns to remove you use include column option

The include-column value of the rule-action parameter changes the mode of the table to drop all columns by default and include the columns specified. Multiple columns are included in the target by invoking the include-column rule multiple times.

Answer

thats correct but make sure atleast one of the column with primary key so that lob column migration and cdc runs fine.

Answer

you should be able to migrate just 5 tables with specific columns

below example migrates only 2 tables

and removes specific column from each table

```
{
  "rules": [
    {
      "rule-type": "transformation",
      "rule-id": "319684261",
      "rule-name": "319684261",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "public",
        "table-name": "cycle_2023_01_24",
        "column-name": "testcolumn2"
      },
      "parallel-load": null,
      "rule-action": "remove-column",
      "value": null,
      "old-value": null
    },
    {
      "rule-type": "transformation",
      "rule-id": "319641293",
      "rule-name": "319641293",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "public",
        "table-name": "cycle_2023_01_23",
        "column-name": "testcolumn1"
      },
      "parallel-load": null,
      "rule-action": "remove-column",
      "value": null,
      "old-value": null
    },
    {
      "rule-type": "selection",
      "rule-id": "318744002",
      "rule-name": "318744002",
      "object-locator": {
        "schema-name": "public",
        "table-name": "cycle_2023_01_24"
      },
      "rule-action": "include",
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "144651340",
      "rule-name": "144651340",
      "object-locator": {
        "schema-name": "public",
        "table-name": "cycle_2023_01_23"
      },
      "rule-action": "include",
      "filters": []
    }
  ]
}
```

Answer

Hi,
Finally got to try the include-column thing. As it turns out, I don't need to use it. Looks like DMS has some sort of smarts into it.
If I create the target table with all the columns, even if I use include-column, DMS inserts to all columns.
If I create the target table with only the columns that I wanted, it only populates these columns. 
I was expecting maybe DMS will complain if there is non 1-1 column mappings but it didn't.

DMS Transformation and Mapping Rules Example - Oracle to PostgreSQL

Relevant content