amazon-web-servicesaws-dms

OR/AND operators in AWS DMS' mapping rule


I need to use source filtering in DMS task but couldn't find in AWS' documentation on how to construct yml to reflect below sql-like logic:

(updated_at is null AND created_at >= <mydate>) OR (updated_at is not null AND updated_at >= <mydate>)

It seems that OR/AND are supported but to a very limited construct.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.Filters.html#CHAP_Tasks.CustomizingTasks.Filters.Applying

What I came up with evaluates only first mapping rule, second one is skipped:

  {
    "rule-type": "selection",
    "rule-id": "9005",
    "rule-name": "9005",
    "object-locator": {
      "schema-name": "dbo",
      "table-name": "table_one"
    },
    "rule-action": "include",
    "filters": [
      {
        "filter-type": "source",
        "column-name": "updated_at",
        "filter-conditions": [
          {
            "filter-operator": "null"
          }
        ]
      },
      {
        "filter-type": "source",
        "column-name": "created_at",
        "filter-conditions": [
          {
            "filter-operator": "gte",
            "value": "2023-04-01"
          }
        ]
      }
    ]
  },
  {
    "rule-type": "selection",
    "rule-id": "9010",
    "rule-name": "9010",
    "object-locator": {
      "schema-name": "dbo",
      "table-name": "table_one"
    },
    "rule-action": "include",
    "filters": [
      {
        "filter-type": "source",
        "column-name": "updated_at",
        "filter-conditions": [
          {
            "filter-operator": "notnull"
          }
        ]
      },
      {
        "filter-type": "source",
        "column-name": "updated_at",
        "filter-conditions": [
          {
            "filter-operator": "gte",
            "value": "2023-04-01"
          }
        ]
      }
    ]
  }

Thanks in advance!


Solution

  • AWS responded to my support case.

    For the time being, advanced OR/AND features are not available in DMS' mapping. Their technical team will investigate to introduce such quality in undefined future.