During task configuration, set filter conditions to sync or migrate only the rows that match your criteria. This is useful for sharding scenarios and partial data synchronization or migration.
Prerequisites
Before you begin, ensure that:
The task is in the Configure Objects phase. For instructions on reaching this phase, see Sync solution overview and Migration solution overview.
Limitations
Complex subquery filters are not supported, such as
COLUMN1 IN (SELECT id FROM table1).If the source table or collection has two columns whose names differ only in letter case, the filter may not produce the expected results.
Tair/Redis sources: filter by key prefix only.
MongoDB sources: conditional filtering is supported only during full synchronization or migration, not during incremental synchronization or migration.
All other source database types: if your filter condition requires quotation marks, use single quotes (
').Do not end a filter condition with a semicolon (
;). Doing so may cause the task to fail.
For SQL Server sources, the filter takes effect only when the filtered field is part of a clustered index. If the filtered field is not part of a clustered index, the filter logic will not take effect.
How it works
DTS applies filter conditions using pre-image filtering: the filter is evaluated against the value of a field *before* any change, not after.
When an UPDATE operation changes a filtered field value from one that satisfies the condition to one that does not, DTS converts the UPDATE into a DELETE on the destination database.
Example: You configure the filter condition enabled = 1. If a record changes from enabled = 1 to enabled = 0, DTS treats this as:
Delete the record where
enabled = 1— the pre-image satisfies the condition, so the deletion is replicated to the destination.Insert a new record where
enabled = 0— this does not satisfy the condition, so it is not synchronized.
To synchronize both enabled = 0 and enabled = 1 records, remove the filter condition and restart the synchronization.
Set filter conditions individually
In the Configure Objects phase, move the target object to the Selected Objects area.
Select objects at the appropriate granularity for your source database type: collection granularity for MongoDB, Redis DB granularity for Tair/Redis, and table granularity for all other database types.
Right-click the target object.
In the dialog box that appears, enter the filter condition. For syntax examples, see Example statements.
Tair/Redis: In the Edit Schema dialog box, enter the Prefixes of Keys to Be Synchronized (whitelist) or Prefixes of Keys to Be Filtered Out (blacklist).
MongoDB and all other database types: In the Edit Table dialog box, enter the Filter Conditions.
Click OK.
Complete the remaining task configuration as prompted.
Batch set filter conditions
In the Configure Objects phase, move the target objects to the Selected Objects area.
In the upper-right corner of the Selected Objects area, click Batch Edit.
In the Select Objects box, select the objects for which you want to set filter conditions.
Keep the default value for Select Type.
In the Select Editing Type area, click the Filter Conditions tab.
For Tair/Redis sources, click the Prefixes of Keys to Be Synchronized/Filtered Out tab instead.
Select an edit mode under Select Edit Mode:
Add: Keep existing filter conditions and add new ones.
Overwrite: Clear existing filter conditions and apply new ones.
Enter the filter condition. For syntax examples, see Example statements.
Tair/Redis: Select Prefixes of Keys to Be Synchronized or Prefixes of Keys to Be Filtered Out, then enter the corresponding prefixes.
MongoDB and all other database types: Enter the filter condition in the text box.
Click OK.
Complete the remaining task configuration as prompted.
Example statements
Tair/Redis sources
| Scenario | Where to enter | Example | What it does |
|---|---|---|---|
| Sync a single prefix | Prefixes of Keys to Be Synchronized | dtsnew | Syncs only keys that start with dtsnew |
| Exclude a single prefix | Prefixes of Keys to Be Filtered Out | dtstest | Excludes keys that start with dtstest; syncs everything else |
| Sync multiple prefixes | Prefixes of Keys to Be Synchronized | dtsnew,dtsold | Syncs keys that start with dtsnew or dtsold |
| Combined sync and exclude | Prefixes of Keys to Be Synchronized: dts; Prefixes of Keys to Be Filtered Out: dtstest | — | Syncs keys starting with dts, except those starting with dtstest |
MongoDB sources
MongoDB filter conditions use a different syntax than SQL WHERE clauses. For more information, see Mapping SQL to MongoDB statements.
| Scenario | Example | What it does |
|---|---|---|
| Filter by ID range | {"_id":{$gt:"user100844658590795**",$lte:"user101674868045948**"}} | Syncs records where _id is greater than the lower bound and less than or equal to the upper bound. $gt means greater than; $lte means less than or equal to. |
Other database types
The Filter Conditions field supports these operators: =, !=, <, >, and in.
When combining multiple conditions, and joins conditions that all must be true; or joins conditions where at least one must be true. You can mix both operators in a single expression, as in the examples below.
If you have questions about writing filter conditions, check the FAQ first.
| Scenario | Example | What it does |
|---|---|---|
| Filter by numeric range | id > 100 and id <= 200 or id in (1, 2, 3) | Syncs rows where id is between 101 and 200 (inclusive), or where id is 1, 2, or 3 |
| Filter by string values | address in('hangzhou','shanghai') | Syncs rows where address is hangzhou or shanghai |
| Filter by date | create_time>'2020-01-01' or create_time>'2020-01-01 00:00:00' | Syncs rows created after January 1, 2020 |
For time-based filters:
Do not use shortened formats such as
create_time>'2020'.DTS evaluates time conditions in the GMT+8 time zone. If your source data includes time zone information, convert the time value to GMT+8 before entering it in Filter Conditions.
Supported field types
Only fields whose data types appear in the following table can be used in filter conditions. If a field's type is not listed for your database, the filter condition does not apply to that field.
| Database | Supported data types |
|---|---|
| PostgreSQL | CHAR, BIGINT, SMALLINT, INTEGER, FLOAT4, FLOAT8, VARCHAR, DATE, TIMESTAMP, NUMERIC |
| Oracle | VARCHAR2, NVARCHAR2, NUMBER, DATE, ROWID, CHAR, BINARY_FLOAT, BINARY_DOUBLE, CLOB, NCLOB, TIMESTAMP, TIMESTAMP WITH TIME ZONE, UROWID |
| MySQL | DECIMAL, INTEGER, BIGINT, FLOAT4, DOUBLE, DATE, TIME, DATETIME, YEAR, VARCHAR |
| SQL Server | NCHAR, DATE, TIME, DATETIME2, TINYINT, SMALLINT, INT, SMALLDATETIME, REAL, DECIMAL, DATETIME, FLOAT, BIT, NUMERIC, BIGINT, VARCHAR, CHAR, TIMESTAMP, NVARCHAR, MONEY, SMALLMONEY, UNIQUEIDENTIFIER |
| DB2 LUW | SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, FLOAT, DOUBLE, DECFLOAT, CHAR, CHARACTER, VARCHAR, GRAPHIC, NCHAR, VARGRAPHIC, NVARCHAR, DATE, TIME, TIMESTAMP, BLOB, CLOB, DBCLOB, LONG VARG, LONG VARCHAR, LONG VARGRAPHIC, BINARY, VARBINARY, XML |
| DB2 AS400 | BIGINT, INTEGER, SMALLINT, DECIMAL, NUMERIC, FLOAT, DECFLOAT, CHAR, VARCHAR, NCHAR, BINARY, DATE, TIME, TIMESTAMP, XML |
What's next
View filter conditions
Task not yet started: Go to the Configure Objects and Advanced Settings phase. In the Selected Objects area, expand the sync or migration object, right-click the target object, and view Filter Conditions.
Task already running: Use the Create Similar Task or Modify sync objects feature to enter the Configure Objects and Advanced Settings phase. Expand the object in the Selected Objects area, right-click the target, and view Filter Conditions. Cancel the task after viewing — no further action is needed. For more information, see Create similar task.
Modify filter conditions
Task not yet started: Go to the Configure Objects and Advanced Settings phase. In the Selected Objects area, expand the object, right-click the target, and edit Filter Conditions.
Task already running:
*Migration task:* Modifying filter conditions is not supported. Reconfigure the task.
*Synchronization task:* Use the Modify sync objects feature to enter the Configure Objects and Advanced Settings phase. Expand the sync object in the Selected Objects area, right-click the target, modify Filter Conditions, and complete the remaining configuration as prompted.
Troubleshooting
If a filter condition is invalid, the task may fail with one of the following errors.
The regular expression (.*)? in error messages represents a variable.| Error code | Error message | Cause |
|---|---|---|
| DTS-RETRY-ERR-0070 | In process of processing data (.*)? failed (.*)? Syntax error in SQL statement | The filter field format for the sync object is invalid. |
| DTS-RETRY-ERR-0145 | Unknown column (.*)? in | The field specified in the filter condition does not exist in the source database. |
| DTS-RETRY-ERR-0147 | You have an error in your SQL syntax | The field specified in the filter condition does not exist in the source database. |
| DTS-RETRY-ERR-0188 | null value in column (.*)? violates not-null constraint | A NOT NULL column on the destination was filtered out, or the corresponding source column value is missing. |
| DTS-RETRY-ERR-0276 | if (.*)? is specified it must not be empty | The value mapped to the Elasticsearch _id column is empty. Filter out records with empty values. |
FAQ
Why didn't my filter condition take effect?
The most common causes are an invalid filter expression and using a filter on an incremental MongoDB task, which does not support filter conditions. Check that your expression uses only supported operators and field types, and verify that your task is not an incremental-only MongoDB task.
What data does a modified filter condition affect?
It depends on whether the task has started:
*Task not yet started:* For MongoDB sources, the change takes effect only during the full synchronization phase. For all other source types, it takes effect during both full and incremental phases.
*Task already running:* For MongoDB sources, changes have no effect — modifying filter conditions is not supported. For all other source types, the change affects only incremental data.
Can I filter matching data and sync or migrate everything else?
*Tair/Redis sources:* Yes. Enter the key prefixes you want to exclude in Prefixes of Keys to Be Filtered Out.
*All other source types:* No — DTS does not support exclusion-based filters for these databases. Instead, write the inverse condition. For example, to exclude rows where
age = 25, enterage > 25 or age < 25.
How do I filter by multiple key prefixes?
Separate the prefixes with commas (,).
Can I use both Prefixes of Keys to Be Synchronized and Prefixes of Keys to Be Filtered Out at the same time?
Yes. Both rules apply simultaneously. DTS syncs keys that match the synchronized prefixes, then excludes any of those that also match the filtered-out prefixes.
Can I call an API in a filter condition?
No.
API reference
| API operation | Description |
|---|---|
| ConfigureDtsJob | Configures a DTS migration or sync task. Set the filter parameter in Dblist to define filter conditions. |