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 :-)

subhashr · 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.

subhashr · Answer

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

subhashr · 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": []
    }
  ]
}
```

Ed · 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