Question 19107 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

DMS Transformation For Creating New Column But Storing Value With Pre Existing Data – Amazon Web Sevices

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 ?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search