To Nha Notes | Oct. 21, 2022, 3:42 p.m.
Although most AWS DMS sources support source filtering, some sources such as MongoDB or Amazon DocumentDB don't support this feature. Check the AWS DMS documentation about Sources for data migration to confirm whether there are limitations to the source engine that you're using. Check more here https://aws.amazon.com/premiumsupport/knowledge-center/dms-source-filter-conditions/
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": "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 Multiple filters using NOT operators
You can use only one negative filter condition within a single filter (OR operation). However, you can use multiple filters (AND operation) with the a negative filter condition. 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"
}]
}]
}]
}
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"
}]
}]
}]
}
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.Filters.html