CODEWITHSUNDEEP
amazon-web-servicesamazon-rdsamazon-auroraaws-dmsdms
Sam
Sam

Reputation: 77

Syntax for DMS Transformation Rule For Arithmetic Expression


I needed some help with my DMS migration. Basically, I have a source and target database with a condition to add a new column to the target table where we do some arithmetic computation on the source column. But on AWS, I can only find examples of concatenating strings but no number calculations. Could someone please share their experience on how to do arithmetic on number data.
Example of the string concat that I saw: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.html

My Table Schema and mapping.json file snippetsou:

# Source table example

CREATE TABLE USER_INFO (
    ID INT PRIMARY KEY,
    FIRST_NAME VARCHAR(50),
    LAST_NAME VARCHAR(50),
    EMAIL VARCHAR(50),
    GENDER VARCHAR(50),
    IP_ADDRESS VARCHAR(20)
);

# Json rule for DMS transformation
 {
        "rule-type": "transformation",
        "rule-id": "5",
        "rule-name": "5",
        "rule-action": "add-column",
        "rule-target": "column",
        "object-locator": {
            "schema-name": "source_database_name_goes_here",
            "table-name": "USER_INFO"
        },
        "value": "new_column_name_for_target_table",
        "expression": "$ID*1000+2",  ////////// Does this work? $ID is source table ID field//////////////
        "data-type": {
             "type": "integer",
             "length": 10
        }

Upvotes: 1

Views: 1079

Answers (1)

Sergio N
Sergio N

Reputation: 316

your rule expresion will work, I have used transformation rule below and works fine

{
"rules": [
{
"rule-type": "transformation",
"rule-id": "644091346",
"rule-name": "644091346",
"rule-target": "column",
"object-locator": {
"schema-name": "%",
"table-name": "%"
},
"rule-action": "add-column",
"value": "USER_ID_NEW",
"expression": "$USER_ID*10",
"data-type": {
"type": "int8"
}
},
{
"rule-type": "selection",
"rule-id": "643832693",
"rule-name": "643832693",
"object-locator": {
"schema-name": "ADMIN",
"table-name": "TB"
},
"rule-action": "include",
"filters": []
}
]}

you could review link below to deep dive little more about transformation rules.

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

Upvotes: 0

Related Questions