AWS DMS add-column transformation

Question

I have the below rule for aws dms replication task. It copies rateattribute table from Test schema to target db sync-test schema. But it doesn't add a new column.

{
  "rules": [
    {
      "rule-type": "transformation",
      "rule-id": "929406550",
      "rule-name": "929406550",
      "rule-target": "column",
      "rule-action": "add-column",
      "object-locator": {
        "schema-name": "Test",
        "table-name": "rateattribute"
      },
      "value": "datacheck",
      "expression": "$LastModifiedTime",
      "data-type": {
        "type": "string",
        "length": 50
      }
    },
    {
      "rule-type": "selection",
      "rule-id": "812938400",
      "rule-name": "812938400",
      "object-locator": {
        "schema-name": "Test",
        "table-name": "rateattribute"
      },
      "rule-action": "include",
      "filters": []
    },
    {
      "rule-type": "transformation",
      "rule-id": "852878650",
      "rule-name": "852549480",
      "rule-target": "schema",
      "object-locator": {
        "schema-name": "Test"
      },
      "rule-action": "rename",
      "value": "sync-test",
      "old-value": null
    }
  ]
}

Any help would be really appreciated!

When copying data from source sql server to redsfhift, datetime2(7) column getting saved as varchar(37). Am trying to transform it to datetime. So am trying a tweak to add a column first with removing precision from datetime2 column values. Once it works, will add a new column to convert this to datetime

score 0 · Answer 1 · answered Nov 16 '22 at 11:03

0

Problem was it was an identity column. DMS does not copy data for identity columns. Removed identity default and added primary key

answered Nov 16 '22 at 11:03

Payal

1
1

score 0 · Answer 2 · answered Nov 17 '22 at 22:05

0

If you are using parquet as the data format it MUST be parquet version 2 for column addition to work.

The following endpoint s3 connection attribute fixed the issue for me: parquetVersion=PARQUET_2_0;

answered Nov 17 '22 at 22:05

Andy O'Brien

1

score 0 · Answer 3 · answered Nov 25 '22 at 10:28

I believe you want a particular column or columns on source with datetime precision 7 to be copied as datetime with same precision in target.

Here is what you can do to directly to change the datatype of the column-

 {
  "rule-type": "transformation",
  "rule-id": "1",
  "rule-name": "RuleName-1",
  "rule-action": "change-data-type",
  "rule-target": "column",
  "object-locator": {
    "schema-name": "Test",
    "table-name": "rateattribute",
    "column-name": "%"
  },
  "data-type": {
    "type": "datetime",
    "precision": 7
  }
}

Alternatively you can also change it across schemas or database by provide the respective names as '%'.

Some links here.

AWS DMS add-column transformation

3 Answers3