Using source filters

You can use source filters to limit the number and type of records transferred from your source to your target. For example, you can specify that only employees with a location of headquarters are moved to the target database. Filters are part of a selection rule. You apply filters on a column of data.

Source filters must follow these constraints:

• A selection rule can have no filters or one or more filters.

• Every filter can have one or more filter conditions.

• If more than one filter is used, the list of filters is combined as if using an AND operator between the filters.

• If more than one filter condition is used within a single filter, the list of filter conditions is combined as if using an OR operator between the filter conditions.

• Filters are only applied when rule-action = 'include'.

• Filters require a column name and a list of filter conditions. Filter conditions must have a filter operator that is associated with either one value, two values, or no value, depending on the operator.

• Column names, table names, view names, and schema names are case-sensitive. Oracle and Db2 should always use UPPER case.

• Filters only support tables with exact names. Filters do not support wildcards.

The following limitations apply to using source filters:

• Filters don't calculate columns of right-to-left languages.

• Don't apply filters to LOB columns.

• Apply filters only to immutable columns, which aren't updated after creation. If source filters are applied to mutable columns, which can be updated after creation, adverse behavior can result.

  For example, a filter to exclude or include specific rows in a column always excludes or includes the specified rows even if the rows are later changed. Suppose that you exclude or include rows 1–10 in column A, and they later change to become rows 11–20. In this case, they continue to be excluded or included even when the data is no longer the same.

  Similarly, suppose that a row outside of the filter's scope is later updated (or updated and deleted), and should then be excluded or included as defined by the filter. In this case, it's replicated at the target.

The following additional concerns apply when using source filters:

• We recommend that you create an index using the columns included in the filtering definition and the primary key.

Creating source filter rules in JSON

You can create source filters using the JSON filters parameter of a selection rule. The filters parameter specifies an array of one or more JSON objects. Each object has parameters that specify the source filter type, column name, and filter conditions. These filter conditions include one or more filter operators and filter values.

The following table shows the parameters for specifying source filtering in a filters object.

Parameter	Value
filter-type	source
column-name	A parameter with the name of the source column to which you want the filter applied. The name is case-sensitive.
filter-conditions	An array of one or more objects containing a filter-operator parameter and zero or more associated value parameters, depending on the filter-operator value.
filter-operator	A parameter with one of the following values: lte – less than or equal to one value ste – less than or equal to one value (lte alias) gte – greater than or equal to one value eq – equal to one value noteq – not equal to one value between – equal to or between two values notbetween – not equal to or between two values null – NULL values notnull – no NULL values
value or start-value and end-value or no values	Zero or more value parameters associated with filter-operator: If filter-operator is lte, ste, gte, eq, or noteq, use value to specify one value parameter. If filter-operator is between or notbetween, use start-value and end-value to specify two value parameters. If filter-operator is null or notnull, specify no value parameters.

The following examples show some common ways to use source filters.

Example Single filter

The following filter replicates all employees where empid >= 100 to the target database.

 {
     "rules": [{
         "rule-type": "selection",
         "rule-id": "1",
         "rule-name": "1",
         "object-locator": {
             "schema-name": "test",
             "table-name": "employee"
         },
         "rule-action": "include",
         "filters": [{
             "filter-type": "source",
             "column-name": "empid",
             "filter-conditions": [{
                 "filter-operator": "gte",
                 "value": "50"
             },{
                 "filter-operator": "noteq",
                 "value": "100"
             }]
         }]
     }]
 }

Example Multiple filter operators

The following filter applies multiple filter operators to a single column of data. The filter replicates all employees where (empid <= 10) OR (empid is between 50 and 75) OR (empid >= 100) to the target database.

{
    "rules": [{
        "rule-type": "selection",
        "rule-id": "1",
        "rule-name": "1",
        "object-locator": {
            "schema-name": "test",
            "table-name": "employee"
        },
        "rule-action": "include",
        "filters": [{
            "filter-type": "source",
            "column-name": "empid",
            "filter-conditions": [{
                "filter-operator": "lte",
                "value": "10"
            }, {
                "filter-operator": "between",
                "start-value": "50",
                "end-value": "75"
            }, {
                "filter-operator": "gte",
                "value": "100"
            }]
        }]
    }]
} 
 

Example Multiple filters

The following filters apply multiple filters to two columns in a table. The filter replicates all employees where (empid <= 100) AND (dept = tech) to the target database.

{
    "rules": [{
        "rule-type": "selection",
        "rule-id": "1",
        "rule-name": "1",
        "object-locator": {
            "schema-name": "test",
            "table-name": "employee"
        },
        "rule-action": "include",
        "filters": [{
            "filter-type": "source",
            "column-name": "empid",
            "filter-conditions": [{
                "filter-operator": "lte",
                "value": "100"
            }]
        }, {
            "filter-type": "source",
            "column-name": "dept",
            "filter-conditions": [{
                "filter-operator": "eq",
                "value": "tech"
            }]
        }]
    }]
} 
 

Example Filtering NULL values

The following filter shows how to filter on empty values. It replicates all employees where dept = NULL to the target database.

{
    "rules": [{
        "rule-type": "selection",
        "rule-id": "1",
        "rule-name": "1",
        "object-locator": {
            "schema-name": "test",
            "table-name": "employee"
        },
        "rule-action": "include",
        "filters": [{
            "filter-type": "source",
            "column-name": "dept",
            "filter-conditions": [{
                "filter-operator": "null"
            }]
        }]
    }]
} 
 

Example Filtering using NOT operators

Some of the operators can be used in the negative form. The following filter replicates all employees where (empid is < 50) OR (empid is > 75) to the target database.

{
    "rules": [{
        "rule-type": "selection",
        "rule-id": "1",
        "rule-name": "1",
        "object-locator": {
            "schema-name": "test",
            "table-name": "employee"
        },
        "rule-action": "include",
        "filters": [{
            "filter-type": "source",
            "column-name": "empid",
            "filter-conditions": [{
                "filter-operator": "notbetween",
                "start-value": "50",
                "end-value": "75"
            }]
        }]
    }]
} 
 

Example Using Mixed filters operators

Start with AWS DMS version 3.5.0, you can mix inclusive operators and negative operators.

The following filter replicates all employees where (empid != 50) AND (dept is not NULL) to the target database.

{
    "rules": [{
        "rule-type": "selection",
        "rule-id": "1",
        "rule-name": "1",
        "object-locator": {
            "schema-name": "test",
            "table-name": "employee"
        },
        "rule-action": "include",
        "filters": [{
            "filter-type": "source",
            "column-name": "empid",
            "filter-conditions": [{
                "filter-operator": "noteq",
                "value": "50"
            }]
        }, {
            "filter-type": "source",
            "column-name": "dept",
            "filter-conditions": [{
                "filter-operator": "notnull"
            }]
        }]
    }]
} 
 

Note the following when using null with other filter operators:

• Using inclusive, negative and null filter conditions together within the same filter will not replicate records with NULL values.

• Using negative and null filter conditions together without inclusive filter conditions within the same filter will not replicate any data.

• Using negative filter conditions without a null filter condition set explicitly will not replicate records with NULL values.

Filtering by time and date

When selecting data to import, you can specify a date or time as part of your filter criteria. AWS DMS uses the date format YYYY-MM-DD and the time format YYYY-MM-DD HH:MM:SS for filtering. The AWS DMS comparison functions follow the SQLite conventions. For more information about SQLite data types and date comparisons, see Datatypes in SQLite version 3 in the SQLite documentation.

The following filter shows how to filter on a date. It replicates all employees where empstartdate >= January 1, 2002 to the target database.

Example Single date filter

{
    "rules": [{
        "rule-type": "selection",
        "rule-id": "1",
        "rule-name": "1",
        "object-locator": {
            "schema-name": "test",
            "table-name": "employee"
        },
        "rule-action": "include",
        "filters": [{
            "filter-type": "source",
            "column-name": "empstartdate",
            "filter-conditions": [{
                "filter-operator": "gte",
                "value": "2002-01-01"
            }]
        }]
    }]
}