sqlamazon-web-servicessqliteaws-dms

AWS DMS Hash column content in existing column


I have two identical databases, with two identical tables. The source database's table has data and the target database's table has no data.

The tables in both databases are named testt.

What the data looks like now in the source database

id   name
----------
1    tim
2    bob
3    john

What I want the testt table to look like after the migration:

id   name
---------------------------
1    0wg9jg-wjeigwg-jieg
2    jwigj-iwjgw-ijgg
3    wjeoiweg-wjegiweg-jei

I just need the content of the name column in testt on the target database to be hashed (or any uuid is fine).

My attempt:

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "SelectTable",
      "object-locator": {
        "schema-name": "public",
        "table-name": "testt"
      },
      "rule-action": "include"
    },
    {
      "rule-type": "transformation",
      "rule-id": "2",
      "rule-name": "HashNameColumn",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "public",
        "table-name": "testt",
        "column-name": "name"
      },
      "rule-action": "transform",
      "expression": "sha256($column)",
      "rule-description": "hasing on the name column"
    }
  ]
}

When I use this on AWS's console it just gives a super generic invalid expression error.

Any help would be greatly appreciated! Thanks.


Solution

  • You cannot directly edit an existing column in that way. You have to use a temp db and first hash it in a new column and then transfer it back. It's explained really well here: https://blog.saeloun.com/2021/11/21/pii-removal-aws-dms/