使用AWS re:Post即您表示您同意 AWS re:Post 使用条款
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).

主题
迁移与现代化
标签
AWS 数据库迁移服务
语言
English
klaabu
已提问 1 个月前263 查看次数
3 回答
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
专家
Osvaldo Marte
已回答 1 个月前
  • klaabu
    1 个月前

    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
    1 个月前

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

0
已接受的回答

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
已回答 1 个月前
  • klaabu
    1 个月前

    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
    1 个月前

    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
已回答 1 个月前

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈，并能促进提问者的职业发展。

回答问题的准则

相关内容