'AWS DMS - column adding order

1)when I add a column can i specify its order in the table? A column was adding to the end of table, without this parameter 2) How i can mask data

 {
    "rule-type": "transformation",
    "rule-id": "2",
    "rule-name": "2",
    "rule-target": "column",
    "object-locator": {
      "schema-name": "test_db_source",
      "table-name": "myTable"
    },
    "rule-action": "add-column",
    "value": "name1",
    "expression": "random()",
    "data-type": {
      "type": "string",
      "length": 255,
      "precision": 2
    }
  }
aws-dms


Solution 1:[1]

No way.

But if we want mask data:

  1. We can add new column with a new name

  2. Mask the data

  3. Remove the old column

  4. Rename the new column to the old name

"expression": "CASE WHEN $ssn_or_emp_no glob '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN 'Redacted-SSN' ELSE $ssn_or_emp_no END"

For further examples: https://go.aws/3FJUbQe

Solution 2:[2]

We can add and remove column with same name with above or any other expression. I tried it and it works fine.

Here is the selection and transformation JSON: (Oracle <--> PostgreSQL)

{
"rules": [
    {
        "rule-type": "selection",
        "rule-id": "1",
        "rule-name": "1",
        "object-locator": {
            "schema-name": "HR",
            "table-name": "PERSONS"
        },
        "rule-action": "include",
        "filters": []
    },
    {
        "rule-type": "transformation",
        "rule-id": "895136847",
        "rule-name": "895136847",
        "rule-target": "table",
        "object-locator": {
            "schema-name": "HR",
            "table-name": "PERSONS"
        },
        "rule-action": "rename",
        "value": "persons",
        "old-value": null
    },
    {
        "rule-type": "transformation",
        "rule-id": "804771206",
        "rule-name": "804771206",
        "rule-target": "schema",
        "object-locator": {
            "schema-name": "HR"
        },
        "rule-action": "rename",
        "value": "hr",
        "old-value": null
    },
    {
        "rule-type": "transformation",
        "rule-id": "2",
        "rule-name": "2",
        "rule-target": "column",
        "object-locator": {
            "schema-name": "HR",
            "table-name": "PERSONS"
        },
        "rule-action": "add-column",
        "value": "ssn",
        "expression": "CASE WHEN $SSN glob '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN hash_sha256($SSN) ELSE $SSN END",
        "data-type": {
            "type": "string",
            "length": 65
        }
    },
    {
        "rule-type": "transformation",
        "rule-id": "3",
        "rule-name": "3",
        "rule-target": "column",
        "object-locator": {
            "schema-name": "HR",
            "table-name": "PERSONS",
            "column-name": "SSN"
        },
        "rule-action": "remove-column"
    }
]

}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ouflak
Solution 2 Abhi

Related Questions