By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

AWS DMS and generated colums

0

i'm having problems with generated columns in my migration (DMS) process from on premise (MySQL) to aws (Aurora).

Different blog posts for example: https://medium.com/ekino-france/8-things-to-know-when-using-aws-dms-for-db-relocation-5904a117fb99 recommend that you should exclude generated columns via transformation rules. But I don't see any option there to exclude column during DMS process. There are options to remove and mess with prefixes/suffixes but don't see an option to just keep column empty/unchanged.

Can someone give me an example of transformation rule which exclude one column, from a table, in AWS DMS ?

I have read the https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.html bud did not notice the option from there. Nor did i find proper option combination under "Database migration tasks" (AWS console GUI).

Topics
Migration & Modernization
Tags
AWS Database Migration Service
Language
English
klaabu
asked 23 days ago200 views
3 Answers
1

One way to handle this is to use a column exclusion rule to exclude the generated column from the migration. This can be done by modifying the selection rule to include an exclude-columns parameter:

{
    "rules": [
        {
            "rule-type": "selection",
            "rule-id": "1",
            "rule-name": "1",
            "object-locator": {
                "schema-name": "test",
                "table-name": "Actor"
            },
            "rule-action": "include",
            "filters": [
                {
                    "filter-type": "exclude",
                    "column-name": "column_i_want_dms_to_ignore"
                }
            ]
        }
    ]
}

The filters array within the selection rule includes a filter of type exclude to exclude the generated column column_i_want_dms_to_ignore from the migration.

Key Sources:

profile picture
EXPERT
Osvaldo Marte
answered 22 days ago
  • klaabu
    22 days ago

    This looks promising, didn't notice such selection rule option in GUI.

    But can it be integrated to global selection rule, so DMS will import all tables in TEST database(schema), where some (Author, Auto) tables have generated columns. Something like:

    {
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "1",
      "object-locator": {
        "schema-name": "test",
        "table-name": "%"
      },
      "rule-action": "include",
    },
	{
      "rule-type": "selection",
      "rule-id": "2",
      "rule-name": "2",
      "object-locator": {
        "schema-name": "test",
        "table-name": "Actor"
      },
      "rule-action": "include",
      "filters": [
        {
          "filter-type": "exclude",
          "column-name": "column_i_want_dms_to_ignore"
        }
      ]
    },
	{
      "rule-type": "selection",
      "rule-id": "3",
      "rule-name": "3",
      "object-locator": {
        "schema-name": "test",
        "table-name": "Auto"
      },
      "rule-action": "include",
      "filters": [
        {
          "filter-type": "exclude",
          "column-name": "column_i_want_dms_to_ignore2"
        }
      ]
    }
  ]
}

    Such json gives

    Invalid table mappings document
  • klaabu
    22 days ago

    Damn, according to manual filter-type can only be source: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.Filters.html

0
Accepted Answer

The following example transforms the table named Actor in your source to remove column collected from it in your target.


{
 	"rules": [{
		"rule-type": "selection",
		"rule-id": "1",
		"rule-name": "1",
		"object-locator": {
			"schema-name": "test",
			"table-name": "Actor"
		},
		"rule-action": "include"
	}, {
		"rule-type": "transformation",
		"rule-id": "2",
		"rule-name": "2",
		"rule-action": "remove-column",
		"rule-target": "column",
		"object-locator": {
			"schema-name": "test",
			"table-name": "Actor",
			"column-name": "collected"
		}
	}]
 }
AWS
subhashr
answered 23 days ago
  • klaabu
    23 days ago

    This removes the column, but I want to keep the column. Those generated (virtual) columns are generated by expression when changing other column on that table like:

    `column_i_want_dms_to_ignore` tinyint(1) GENERATED ALWAYS AS (if((`second_column` is null),1,NULL)) VIRTUAL,

    So you cant directly insert to it, but DMS somewhy does and gets error like: "The value specified for generated column 'blah' in table 'blah2' is not allowed."

    So im looking transformation rule that ignores the column rather than deletes it.

  • klaabu
    19 days ago

    This is the actual solution. My brain somehow neglected this as I thought it will remove the whole column from the table (schema). But in the column scope (rule-target) in the transformation rule it just removes the entry.

0

so basically you are looking to ignore a column to migrate to target which is there on source on target this column is autogenerated using expressions as defined on target table definition

AWS
subhashr
answered 23 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content