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
질문됨 한 달 전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
답변함 한 달 전
  • klaabu
    한 달 전

    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
    한 달 전

    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
답변함 한 달 전
  • klaabu
    한 달 전

    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
    한 달 전

    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
답변함 한 달 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠