如何使用AWS DMS直接屏蔽含PII的列（无需新增列）

可以直接对包含PII信息的原列进行内容屏蔽，无需在数据仓库创建新列，通过AWS DMS表映射中的modify-column转换配合表达式即可实现，具体操作如下：

• 在DMS任务的Table Mapping配置中，针对目标表添加modify-column转换规则，指定与源列同名的目标列，通过expression参数编写PII屏蔽逻辑。
• 根据不同PII类型编写对应表达式（以下示例基于常见SQL语法，需适配目标数仓的函数规范）：
  • 邮箱屏蔽：保留前缀前2位与域名，中间用星号替换

    CONCAT(SUBSTRING(email, 1, 2), '****@', SPLIT_PART(email, '@', 2))
    

  • 手机号屏蔽：保留前3位与后4位，中间用星号替换

    CONCAT(SUBSTRING(phone, 1, 3), '****', SUBSTRING(phone, LENGTH(phone)-3, 4))
    

  • 身份证号屏蔽：保留前6位与后4位，中间用星号替换

    CONCAT(SUBSTRING(id_card, 1, 6), '********', SUBSTRING(id_card, LENGTH(id_card)-3, 4))
    

完整Table Mapping示例 #

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "1",
      "object-locator": {
        "schema-name": "source_schema",
        "table-name": "target_table"
      },
      "rule-action": "include"
    },
    {
      "rule-type": "transformation",
      "rule-id": "2",
      "rule-name": "Mask Email Column",
      "rule-action": "modify-column",
      "object-locator": {
        "schema-name": "target_schema",
        "table-name": "target_table",
        "column-name": "email"
      },
      "parameters": {
        "expression": "CONCAT(SUBSTRING(email, 1, 2), '****@', SPLIT_PART(email, '@', 2))",
        "replace-existing": "true"
      }
    },
    {
      "rule-type": "transformation",
      "rule-id": "3",
      "rule-name": "Mask Phone Column",
      "rule-action": "modify-column",
      "object-locator": {
        "schema-name": "target_schema",
        "table-name": "target_table",
        "column-name": "phone"
      },
      "parameters": {
        "expression": "CONCAT(SUBSTRING(phone, 1, 3), '****', SUBSTRING(phone, LENGTH(phone)-3, 4))",
        "replace-existing": "true"
      }
    }
  ]
}

• 关键参数说明：replace-existing设为true，表示直接覆盖原列内容，无需新增列。
• 注意：不同目标数据仓库的字符串函数语法可能存在差异（比如部分数仓用SUBSTR而非SUBSTRING），需根据实际环境调整表达式。

内容的提问来源于stack exchange，提问作者Algorithm Unlock