Transforming the content of a column that is being migrated to Dynamo using DMS

Question

I have a DMS task that is migrating data from MySQL to DynamoDB. I have an object mapping rule defined which maps record to record. I also want to do a transformation to the content that is being migrated for two columns.
Here is the transformation rule that I would ideally like to add:

```
{
      "rule-type": "transformation",
      "rule-id": "3",
      "rule-name": "3",
      "rule-target": "column",
      "rule-action": "add-column",
      "object-locator": {
        "schema-name": "%",
        "table-name": "USER_SAVED_SEARCHES_TBL"
      },
      "value": "startTime",
      "expression": "CASE WHEN $START_TIME==-1 THEN null ELSE $START_TIME END"
    },
```
It's my understanding however that I can't use both object mapping and transformation rule in the same rule set. How do I achieve the above using object mapping?

Answer

Hello,

In order to perform an Object Mapping and Transformation Rule, you would be required to separate them into their own rule set. An example can be seen below: [1]

```
{
    "rules": [
        {
            "rule-type": "selection", 
            "rule-id": "1",
            "rule-name": "1",
            "object-locator": {
                "schema-name": "%",
                "table-name": "USER_SAVED_SEARCHES_TBL"
            },
            "rule-action": "include"
        },
        {
            "rule-type": "transformation",
            "rule-id": "2",
            "rule-name": "2",
            "rule-action": "add-column",
            "rule-target": "column",
            "object-locator": {
                "schema-name": "%",
                "table-name": "USER_SAVED_SEARCHES_TBL"
            },
            "value": " startTime",
            "expression": "CASE WHEN $START_TIME==-1 THEN null ELSE $START_TIME END",
            "data-type": {
                 "type": "datetime"
            }
        }
    ]
}
```

#### References:

[1] Expressions: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.html

Transforming the content of a column that is being migrated to Dynamo using DMS

References:

Relevant content