Filter data using SQL conditions - Data Transmission Service

When you configure a data synchronization or migration task, you can set filter conditions to sync or migrate only the data from the source database that meets your requirements. This feature is commonly used in scenarios such as sharding and partial data synchronization or migration.

Prerequisites

The current task must be in the Configure Objects phase. For instructions on how to reach this phase, see the relevant configuration documents in Sync solution overview and Migration solution overview.

Notes

Complex filter statements are not supported, such as COLUMN1 IN (SELECT id FROM table1).
If the source table or collection contains two columns whose names differ only in letter case, the filter may not produce the expected results.
When the source database type is Tair/Redis, you can only filter data by key prefix.
When the source database type is MongoDB, conditional filtering is supported only for full synchronization or migration tasks. It is not supported during incremental synchronization or migration.
When the source database type is any database other than Tair/Redis and MongoDB, use single quotes (') if your filter condition requires quotation marks.
Do not add a semicolon (;) at the end of the filter condition. Doing so may cause the task to fail.
If an UPDATE operation is performed on the source database and meets specific conditions—the pre-image value satisfies the filter condition but the post-image value does not—DTS converts the UPDATE statement into a DELETE statement by default and executes it on the destination database.
Important
In DTS instances where the source database is SQL Server, if the filtered field is not part of a clustered index, the filter logic will not take effect.
DTS uses pre-image filtering. If the value of a configured filter field changes, DTS applies the filter based on the value before the change.
Example: Suppose you configure the filter condition enabled = 1. DTS synchronizes only data that satisfies this condition. If you change the data field enabled = 1 to enabled = 0, the system treats this as first deleting the record with enabled = 1 and then inserting a new record with enabled = 0. Because the deletion satisfies the filter condition, the record is removed. The subsequent insert does not satisfy the condition, so it is not synchronized. To synchronize both enabled = 0 and enabled = 1 data, remove this filter condition and restart the synchronization.

Procedure

Note

The method for setting filter conditions depends on the source database type. There are three cases: Tair/Redis, MongoDB, and other database types.

Set individually

In the Configure Objects phase, move the task object to the Selected Objects area.
Note
When the source database type is MongoDB, select objects at the collection granularity. When the source database type is Tair/Redis, select objects at the Redis DB granularity. For all other source database types, select objects at the table granularity.
Right-click the target object.
In the dialog box that appears, enter the filter condition.
- Source database type is 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).
- Source database type is MongoDB: In the Edit Table dialog box, enter the Filter Conditions.
- Source database type is other: In the Edit Table dialog box, enter the Filter Conditions.
Note
For syntax examples, see Example statements.
After setting the condition, click OK.
Complete the remaining task configuration as prompted.

Batch set

In the Configure Objects phase, move the task objects to the Selected Objects area.
In the upper-right corner of the Selected Objects area, click Batch Edit.
In the Select Objects box, check the objects for which you want to set prefix filter conditions.
Note
Keep the default value for Select Type.
In the Select Editing Type area, click the Filter Conditions tab.
Note
If the source database type is Tair/Redis, click the Prefixes of Keys to Be Synchronized/Filtered Out tab.
Select an Select Edit Mode.
- Add: Keep existing filter conditions and add new ones.
- Overwrite: Clear existing filter conditions and apply new ones.
Set the filter condition.
- Source database type is Tair/Redis: Check Prefixes of Keys to Be Synchronized or Prefixes of Keys to Be Filtered Out as needed, and enter the corresponding prefixes.
- Source database type is MongoDB: Enter the filter condition in the text box.
- Source database type is other: Enter the filter condition in the text box.
Note
For syntax examples, see Example statements.
After setting the condition, click OK.
Complete the remaining task configuration as prompted.

Example statements

Source database type is Tair/Redis

Filter type	Example statement	Description
Sync single prefix	Enter in Prefixes of Keys to Be Synchronized: `dtsnew`	Sync only data whose keys in the selected objects start with `dtsnew` to the destination.
Filter single prefix	Enter in Prefixes of Keys to Be Filtered Out: `dtstest`	Filter out data whose keys in the selected objects start with `dtstest`. Sync all other data to the destination.
Sync multiple prefixes	Enter in Prefixes of Keys to Be Synchronized: `dtsnew,dtsold`	Sync only data whose keys in the selected objects start with `dtsnew` or `dtsold` to the destination.
Mixed sync and filter	Enter in Prefixes of Keys to Be Synchronized: `dts` Enter in Prefixes of Keys to Be Filtered Out: `dtstest`	Sync only data whose keys in the selected objects start with `dts` but do not start with `dtstest` to the destination.

Source database type is MongoDB

Filter type	Example statement	Description
Other	`{"_id":{$gt:"user100844658590795**",$lte:"user101674868045948**"}}`	Filter by user ID. Here, `$gt` means greater than, and `$lte` means less than or equal to. MongoDB filter conditions use different syntax than standard SQL WHERE clauses. For more information, see Mapping SQL to MongoDB statements.

Source database is another type

Note

The Filter Conditions supports these operators: English characters =, !=, <, >, and in.
If you have questions about writing filter conditions, review the FAQ first.

Example statements

Filter type	Example statement	Description
Value	`id > 100 and id <= 200 or id in (1, 2, 3)`	Sync or migrate only data where the `id` field is greater than 100 and less than or equal to 200, or where the `id` field is 1, 2, or 3.
String	`address in('hangzhou','shanghai')`	Sync or migrate only data where the `address` field is `hangzhou` or `shanghai`.
Time	`create_time>'2020-01-01'` or `create_time>'2020-01-01 00:00:00'`	Sync or migrate only data created after 2020. Important Do not use `create_time>'2020'`. When filtering time-based data, DTS uses the GMT+8 time zone. If your time data includes time zone information, convert the time in your filter condition to GMT+8 before entering it in the Filter Conditions.

Supported types

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 to do next

View filter statements
- Current task not yet configured: Go back to the Configure Objects and Advanced Settings phase. Expand the sync or migration object in the Selected Objects area, right-click the target object, and view the Filter Conditions.
- Current task already configured and running: Use the Create Similar Task or Modify Sync Objects feature to enter the Configure Objects and Advanced Settings phase. Expand the sync or migration object in the Selected Objects area, right-click the target object, and view the Filter Conditions. Cancel the task after viewing—no need to proceed further. For more information, see Create similar task and Modify sync objects.
Modify filter statements
- Current task not yet configured: Go back to the Configure Objects and Advanced Settings phase. Expand the sync or migration object in the Selected Objects area, right-click the target object, and modify the Filter Conditions.
- Current task already configured and running:
  - Migration task: Migration tasks do not support modifying filter conditions. You must 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 object, modify the Filter Conditions, and complete the remaining configuration as prompted. For more information, see Modify sync objects.

Common errors

If your filter condition is incorrect, the task may fail with one of the following errors. Use the error message and the guidance in What to do next to resolve the issue.

Note

The regular expression ((.*)?) in the error message represents a variable.

Error code	Error message	Description
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 column with a NOT NULL constraint on the destination was filtered, or the corresponding column value is missing in the source.
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 did my filter condition not take effect?
The filter statement may be invalid, or the task might be an incremental MongoDB task—which does not support filter conditions.
What data does a modified filter condition affect?
- Task not yet configured: For MongoDB sources, the change takes effect only during the full synchronization phase. For other source types, it takes effect during both full and incremental phases.
- Task already configured and running: For MongoDB sources, the change has no effect—not supported. For other source types, it affects only incremental data.
Can I batch set filter conditions?
No. You must set filter conditions individually for each target object.
Can I filter matching data and synchronize or migrate the rest?
- Source database type is Tair/Redis: Yes. Enter the Prefixes of Keys to Be Filtered Out to exclude matching keys.
- Source database is another type: No. Instead, define the opposite filter condition. For example, to exclude records where the age field equals 25, enter age > 25 or age < 25 in the Filter Conditions.
How do I filter multiple keys?
Separate them with commas (,).
Can I enter both Prefixes of Keys to Be Synchronized and Prefixes of Keys to Be Filtered Out?
Yes. Both rules will apply.
Can I call an API in a filter condition?
No.

Related API

API operation	Description
ConfigureDtsJob	Configures a DTS migration or sync task. Set the `filter` parameter in `Dblist` to define filter conditions.