DMS Transformation For Creating New Column But Storing Value With Pre Existing Data

Question

I had a quick question regarding Amazon DMS transformation capabilities. So basically, I have a source database (MySQL) that I need to migrate to destination database (Aurora). During this migration we have a Primary Key called id that needs to transferred as source_id in the Aurora and we have another ID field in the aurora whose value is some calculation done to the id of the source. Basically as shown below:

Source DB (id) -----> Target DB (source_id)
Source DB (id) -----> Some Calculations (Example: id+50)-----> Target DB (ID)

Is this feasible via DMS ?

Answer 1

Here is the another custom rule, more specific for your requirement

1. Transform schema name from source_db to target_db
2. Transform table name from source_tb to target_db
3. Add Column source_id with value from id column
4. Transform column name id to column name ID.

Here is the code

---### SOURCE SCHEMA : SOURCE_DB
create table "source_tb" ("id" int, "name" varchar(32));

insert into "source_tb" values (1,'one');
insert into "source_tb" values (2,'two');
commit;

--## AWS DMS TASK RULE



{
  "rules": [
{
  "rule-type": "transformation",
  "rule-id": "894635485",
  "rule-name": "894635485",
  "rule-target": "column",
  "object-locator": {
    "schema-name": "SOURCE_DB",
    "table-name": "source_tb"
  },
  "rule-action": "add-column",
  "value": "source_id",
  "expresion": "$id",
  "data-type": {
    "type": "int8"
  }
},
{
  "rule-type": "transformation",
  "rule-id": "894635486",
  "rule-name": "894635486",
  "rule-target": "column",
  "object-locator": {
    "schema-name": "SOURCE_DB",
    "table-name": "source_tb"
  },
  "rule-action": "add-column",
  "value": "ID",
  "expresion": "$id+50",
  "data-type": {
    "type": "int8"
  }
},
{
  "rule-type": "transformation",
  "rule-id": "893830603",
  "rule-name": "893830603",
  "rule-target": "table",
  "object-locator": {
    "schema-name": "SOURCE_DB",
    "table-name": "source_tb"
  },
  "rule-action": "rename",
  "value": "target_tb",
  "old-value": null
},
{
  "rule-type": "transformation",
  "rule-id": "893722068",
  "rule-name": "893491548",
  "rule-target": "schema",
  "object-locator": {
    "schema-name": "SOURCE_DB"
  },
  "rule-action": "rename",
  "value": "TARGET_DB",
  "old-value": null
},
{
  "rule-type": "selection",
  "rule-id": "893475728",
  "rule-name": "893475728",
  "object-locator": {
    "schema-name": "SOURCE_DB",
    "table-name": "%"
  },
  "rule-action": "include",
  "filters": []
}
]}

You could review link below for dive deep.

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

Answer 2

regarding your specific requirement, below the JSON rules to be applied to the replication task.

{
  "rules": [
{
  "rule-type": "transformation",
  "rule-id": "6440913467",
  "rule-name": "644091347",
  "rule-target": "column",
  "object-locator": {
    "schema-name": "ADMIN",
    "table-name": "TB"
  },
  "rule-action": "add-column",
  "value": "source_id",
  "expression": "$id",
  "data-type": {
    "type": "int8"
  }
},
{
  "rule-type": "transformation",
  "rule-id": "644091346",
  "rule-name": "644091346",
  "rule-target": "column",
  "object-locator": {
    "schema-name": "ADMIN",
    "table-name": "TB"
  },
  "rule-action": "add-column",
  "value": "ID",
  "expression": "$id+50",
  "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