Syntax for DMS Transformation Rule For Arithmetic Expression

Question

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
        }

We have found it's often easier to create a view with the transformations in the source database and convert the view. — Robert Love, Jan 13 '23 at 02:46
Could you share an example please for above json. I could not find much out there and I am fairly new to this. — Sam, Jan 13 '23 at 16:32
I don't know what DBMS your source DB is, but you should be able to add a view. With that you can import new view instead of user_info. I say this as we tend to find greater power in DMBS ability to transform data over DMS. — Robert Love, Jan 13 '23 at 19:51

score 0 · Answer 1 · answered Jan 16 '23 at 16:05

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

Thank you for your answer. The only difference that I found was my code has `tyep: integer` where as I see that you used `type:int8`. Looking my schema abive do you think I should use `int8` as well ? — Sam, Jan 17 '23 at 15:52
I recommend you evaluate the field length or your data, int use 4 bytes, int8 use 8 bytes. — Sergio N, Jan 18 '23 at 00:32

Syntax for DMS Transformation Rule For Arithmetic Expression

1 Answers1