Filter data using SQL conditions - Data Transmission Service

When you configure objects for a data synchronization or migration task, you can set filter conditions. This lets you synchronize or migrate only the data that meets your requirements from the source database to the destination database. This feature is often used for scenarios such as database sharding, table partitioning, and partial data synchronization or migration.

Prerequisites

The task is in the Configure Objects step. For information about how to navigate to this step, see the topics in Data synchronization scenarios and Data migration scenarios.

Notes

Complex filter statements are not supported. For example, COLUMN1 IN (SELECT id FROM table1).
If a source table or collection contains two columns whose names differ only in case, the filtering task may produce unexpected results.
If the source database is a Tair/Redis instance, you can filter data only by key prefix.
If the source database is a MongoDB database, you can set filter conditions only for full data migration or synchronization tasks. Filter conditions are not supported for incremental migration or synchronization.
If the source database is not a Tair/Redis or MongoDB database, use single quotation marks (') in a filter condition if quotation marks are needed.
Do not add a semicolon (;) at the end of a filter condition. Otherwise, the task may fail.
If you run an UPDATE statement on the source database, and the pre-image of the data meets the filter condition but the post-image does not, Data Transmission Service (DTS) converts the UPDATE statement into a DELETE statement. DTS then runs the DELETE statement on the destination database.
Important
For a DTS instance whose source is a SQL Server database, this filtering logic does not take effect if the filtered field is not a clustered index column.

Procedure

Note

The method for setting filter conditions depends on the source database type: Tair/Redis, MongoDB, or another database type.

Set for a single object

In the Configure Objects step, move the task objects to the Selected Objects box.
Note
If the source is a MongoDB database, select objects at the collection level. If the source is a Tair/Redis instance, select objects at the Redis DB level. For other database types, select objects at the table level.
Right-click the destination object.
In the dialog box that appears, enter the filter condition.
- If the source database is a Tair/Redis instance, in the Edit Schema dialog box, configure the Prefixes of Keys to Be Synchronized (whitelist) or Prefixes of Keys to Be Filtered Out (blacklist) parameter.
- If the source is a MongoDB database, specify a Filter Conditions in the Edit Table dialog box.
- If the source is another type of database, enter a value in the Filter Conditions text box in the Edit Table dialog box.
Note
For more information about the syntax of filter conditions, see Sample statements.
After you complete the settings, click OK.
Follow the prompts to complete the task configuration.

Set for multiple objects in a batch

In the Configure Objects step, move the task objects to the Selected Objects box.
In the upper-right corner of the Selected Objects area, click Batch Edit.
In the Select Objects dialog box, select the objects that you want to edit.
Note
Keep the default value for Select Type.
In the Select Editing Type area, click the Filter Conditions tab.
Note
If the source is a Tair/Redis instance, click the Prefixes of Keys to Be Synchronized/Filtered Out tab.
Select an Select Edit Mode.
- Add: Retains the existing filter conditions and adds the new condition.
- Overwrite: Clears the existing filter conditions and uses the new condition.
Set the filter condition.
- If the source is a Tair/Redis instance: Based on your business requirements, select Prefixes of Keys to Be Synchronized or Prefixes of Keys to Be Filtered Out, and then enter the corresponding prefixes.
- If the source is a MongoDB database: In the text box, enter filter conditions.
- If the source is another type of database: In the text box, enter the filter condition.
Note
For more information about the syntax of filter conditions, see Sample statements.
After you complete the settings, click OK.
Follow the prompts to complete the task configuration.

Sample statements

Source instance: Tair/Redis

Filter type	Sample statement	Description
Synchronize a single prefix	Set Prefixes of Keys to Be Synchronized to `dtsnew`.	Synchronizes only data whose keys start with `dtsnew` from the source objects to the destination database.
Filter out a single prefix	Set Prefixes of Keys to Be Filtered Out to `dtstest`.	Filters out data whose keys start with `dtstest` from the source objects and synchronizes the remaining data to the destination database.
Synchronize multiple prefixes	Set Prefixes of Keys to Be Synchronized to `dtsnew,dtsold`.	Synchronizes only data whose keys start with `dtsnew` or `dtsold` from the source objects to the destination database.
Use synchronization and filtering together	Set Prefixes of Keys to Be Synchronized to `dts`. Set Prefixes of Keys to Be Filtered Out to `dtstest`.	Synchronizes only data whose keys start with `dts` but not `dtstest` from the source objects to the destination database.

Source database: MongoDB

Filter type	Sample statement	Description
Other	`{"_id":{$gt:"user100844658590795**",$lte:"user101674868045948**"}}`	Filters data by user ID. In this example, `$gt` indicates greater than, and `$lte` indicates less than or equal to. The statements supported for MongoDB filter conditions are different from standard SQL WHERE statements. For more information, see Query and Projection Operators and SQL to MongoDB Mapping Chart.

Other database sources

Note

The Filter Conditions field supports the following operators: =, !=, <, >, and in.
If you have questions when you enter filter conditions, you can check the FAQ.

Sample statements

Filter type	Sample statement	Description
Numeric value	`id > 100 and id <= 200 or id in (1, 2, 3)`	Synchronizes or migrates only data where the value of the `id` field is greater than 100 and less than or equal to 200, or the value of the `id` field is 1, 2, or 3.
String	`address in('hangzhou','shanghai')`	Synchronizes or migrates only data where the value of the `address` field is `hangzhou` or `shanghai`.
Time	`create_time>'2020-01-01'` or `create_time>'2020-01-01 00:00:00'`	Synchronizes or migrates only data created after 2020. Important You cannot enter `create_time>'2020'`. DTS uses the GMT+8 time zone to filter time-type data. If your time data includes time zone information, you must first convert the time in the filter condition to the GMT+8 time zone. Then, enter the converted time in the Filter Conditions field.

What to do next

View filter statements
- If the task is not yet configured: Go to the Configure Objects and Advanced Settings step. In the Selected Objects box, expand the task objects. Then, right-click the destination object to view the Filter Conditions.
- If the task is already configured and running: Use the Create Similar Task or Modify Synchronization Objects feature to go to the Configure Objects and Advanced Settings step. In the Selected Objects box, expand the task objects. Then, right-click the destination object to view the Filter Conditions. After you view the condition, you can cancel the task without proceeding. For more information, see Create a similar task and Modify the objects to be synchronized.
Modify filter statements
- If the task is not yet configured: Go to the Configure Objects and Advanced Settings step. In the Selected Objects box, expand the task objects. Then, right-click the destination object and modify the Filter Conditions.
- The task is configured and running:
  - Migration task: You cannot modify the filter condition for a migration task. You must reconfigure the task.
  - Synchronization task: Use the Modify Synchronization Objects feature to go to the Configure Objects and Advanced Settings step. In the Selected Objects box, expand the objects for the sync task. Then, right-click the destination object, modify the Filter Conditions, and follow the prompts to complete the task configuration. For more information, see Modify the objects to be synchronized.

Common errors

If you enter an incorrect filter condition, the task may report an error. Fix the error based on the error message and the information in the What to do next section.

Note

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

Error code	Error message	Description
DTS-RETRY-ERR-0070	In process of processing data (.)? failed (.)? Syntax error in SQL statement	The format of the filter condition for the synchronization object is incorrect.
DTS-RETRY-ERR-0145	Unknown column (.*)? in	The field specified in the filter condition for the task does not exist in the source database.
DTS-RETRY-ERR-0147	You have an error in your SQL syntax	The filter condition for the current job configuration references a field that 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 in the destination database was filtered out, or the corresponding column in the source database has a null value.
DTS-RETRY-ERR-0276	if (.*)? is specified it must not be empty	The value mapped to the `_id` column in Elasticsearch is empty. You must filter out the data that has an empty value for this column.

FAQ

Why does the filter condition not take effect?
The filter condition statement is invalid. Alternatively, if the source is a MongoDB database, filter conditions are not supported for incremental tasks.
After I modify a filter condition, what is the scope of the change?
- If the task is not yet configured: For a MongoDB source, the condition takes effect only during the full task phase. For other database sources, the condition takes effect during both the full and incremental task phases.
- If the task is already configured and running: For a MongoDB source, the condition does not take effect. For other database sources, the condition takes effect only on incremental data.
Can I set filter conditions in batches?
No, you cannot. You must set filter conditions for each destination object separately.
Can I filter out data that meets a condition and synchronize or migrate the rest of the data to the destination database?
- If the source is a Tair/Redis instance: Yes, you can. You can enter a value for Prefixes of Keys to Be Filtered Out to filter out keys that meet the condition.
- If the source is another type of database: No, you cannot. You can try to set an opposite filter condition to meet your requirements. For example, to filter out data where the value of the age field is 25, you can enter age > 25 or age < 25 in the Filter Conditions field.
If I need to filter by multiple keys, how do I specify the filter condition?
Use commas (,) to separate the keys.
Can I specify both Prefixes of Keys to Be Synchronized and Prefixes of Keys to Be Filtered Out at the same time?
Yes, you can. The rules for both synchronization and filtering take effect.
Are API calls supported in filter conditions?
Not supported.

Related API operations

API operation	Description
ConfigureDtsJob	Configures a DTS migration or synchronization task. Set the `Dblist` parameter and configure the `filter` parameter to set a filter condition.