Using DMS not able to add column transformation for existing tables in redshift

Question

I have created an DMS service with sources as MySQL and endpoint as redshift , so i created an DMS task to add datetime and update operations columns for my existing table in redshift which is not being happening.Is it possible to add columns to my renamed tables in redshift?

venkatesh · Answer

Below is my transformation rule and to add columns to renamed tables in redshift with name GA_SCD_STATUS_MT doesn't work , so my requirement is it achievable?

{
    "rules": [
        {
            "rule-type": "transformation",
            "rule-id": "137766153",
            "rule-name": "137766153",
            "rule-target": "column",
            "object-locator": {
                "schema-name": "GOAUDITS",
                "table-name": "GA_SCD_STATUS_MT"
            },
            "parallel-load": null,
            "rule-action": "add-column",
            "value": "DMS_UPDATED_AT",
            "old-value": null,
            "expression": "current_timestamp",
            "data-type": {
                "type": "datetime"
            }
        },
        {
            "rule-type": "transformation",
            "rule-id": "2",
            "rule-name": "2",
            "rule-target": "column",
            "object-locator": {
                "schema-name": "GOAUDITS",
                "table-name": "GA_SCD_STATUS_MT"
            },
            "rule-action": "add-column",
            "value": "DMS_OPS",
            "expression": "operation_indicator('D', 'U', 'I')",
            "data-type": {
                "type": "string",
                "length": 50
            }
        },
        {
            "rule-type": "selection",
            "rule-id": "137734000",
            "rule-name": "137734000",
            "object-locator": {
                "schema-name": "GOAUDITS",
                "table-name": "GA_SCD_STATUS_MT"
            },
            "rule-action": "include",
            "filters": [],
            "parallel-load": null,
            "isAutoSegmentationChecked": false
        }
    ]
}

MilindOke · Answer

Yes it is possible but I do not see any column transformation rule in what you have shared. Can you refer https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.html and check for add-column rule action.

Also consider if you can leverage DEFAULT value for table columns if you want something like insert date time column on table. This can make it easier on DMS.

Using DMS not able to add column transformation for existing tables in redshift

相關內容