CODEWITHSUNDEEP
aws-dms
Payal
Payal

Reputation: 1

AWS DMS add-column transformation

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

Upvotes: 0

Views: 1361

Answers (3)

Mradul Yd
Mradul Yd

Reputation: 131

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.

Upvotes: 0

Andy O'Brien
Andy O'Brien

Reputation: 1

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;

Upvotes: 0

Payal
Payal

Reputation: 1

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

Upvotes: 0

Related Questions