Filter data by using SQL conditions - Data Transmission Service

When you configure objects for a data migration or data synchronization task in Data Transmission Service (DTS), you can specify SQL conditions to migrate or synchronize only data that meets the specified conditions to the destination database. This feature is applicable to scenarios such as database sharding, table partitioning, and synchronization or migration of partial data.

Prerequisites

The DTS task is being configured in the Configure Objects step. See data synchronization scenarios and data migration scenarios for details on how to configure a DTS task.

Notes

You cannot use complex SQL statements as filter conditions. Example: COLUMN1 IN (SELECT id FROM table1).
If two columns in the source table or collection have the same name that differ only in capitalization, the data may not be filtered as expected.
Depending on the source:
- Tair/Redis instance: you can filter data only by using the prefix of keys.
- MongoDB database: you can specify filter conditions only for full data synchronization or full data migration. You cannot specify filter conditions for incremental data synchronization or incremental data migration.
- Others: use single quotation marks (') in an SQL condition if necessary.
A filter condition cannot end with a semicolon (;). Otherwise, the DTS task may fail.
Assume that you execute an UPDATE statement in the source database. If the previous image value meets filter conditions whereas the new image value does not meet filter conditions, DTS converts the UPDATE statement to a DELETE statement and then executes the DELETE statement in the destination database.
Important
In a DTS instance whose source database is a SQL Server database, filter conditions take effect only if the fields to be filtered are clustered index columns.

Procedure

Note

The method to set filter conditions varies based on the type of the source, such as Tair/Redis, MongoDB, or another type of database.

Single setting

In the Configure Objects step, move an object that you want to synchronize or migrate to the Selected Objects section.
Note
If the source is a Tair/Redis instance, you can select only databases as objects to be synchronized or migrated.
If the source is a MongoDB database, you can select only collections as objects to be synchronized or migrated.
If the source is not a Tair or Redis instance or a MongoDB database, you can select only tables as objects to be synchronized or migrated.
Right-click the object.
In the dialog box that appears, enter filter conditions.
- 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.
- MongoDB database: In the Edit Table dialog box, specify filter conditions in the Filter Conditions field.
- Others: In the Edit Table dialog box, specify filter conditions in the Filter Conditions field.
Note
For the syntax of filter conditions, see Sample conditions.
After you complete the settings, click OK.
Configure other parameters that are required for the DTS task.

Batch settings

In the Configure Objects step, move an object that you want to synchronize or migrate to the Selected Objects section.
In the upper-right corner of the Selected Objects section, click Batch Edit.
In the Select Objects section, select the objects that you want to edit (set prefix filter conditions).
Note
Select Type can remain as the default.
In the Select Editing Type section, 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 Select Edit Mode.
- Add: Retain existing filter conditions and add new filter conditions.
- Overwrite: Clear existing filter conditions and use new filter conditions.
Set filter conditions.
- 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 filter conditions.
Note
For the syntax of filter conditions, see Sample conditions.
After you complete the settings, click OK.
Configure other parameters that are required for the DTS task.

Sample conditions

Tair/Redis instance

Filter type	Sample statements	Description
Synchronize a single prefix	Set the Prefixes of Keys to Be Synchronized parameter to `dtsnew`	Only data with keys that start with `dtsnew` in the objects to be synchronized are synchronized to the destination.
Filter out a single prefix	Set the Prefixes of Keys to Be Filtered Out parameter to `dtstest`	Filter out data whose keys start with `dtstest` from the objects to be synchronized, and synchronize the remaining data to the destination.
Synchronize multiple prefixes	Set the Prefixes of Keys to Be Synchronized parameter to `dtsnew,dtsold`	Only objects whose keys are prefixed with `dtsnew` and `dtsold` are synchronized to the destination.
Synchronization and filtering used together	Set the Prefixes of Keys to Be Synchronized parameter to `dts` Set the Prefixes of Keys to Be Filtered Out parameter to `dtstest`	Only objects whose keys are prefixed with `dts` and are not prefixed with `dtstest` are synchronized to the destination.

MongoDB database

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

Others

Note

In the Filter Conditions field, the following operators are supported in the SQL conditions: =, !=, <, >, and in.
If you have questions when filling in filter conditions, you can first check the FAQ.

Sample conditions

Filter type	Example	Description
Numeric value	`id > 100 and id <= 200 or id in (1, 2, 3)`	Filter data whose `id` value is greater than 100 and less than or equal to 200, or whose `id` value is 1, 2, or 3.
String	`address in('hangzhou','shanghai')`	Search for data whose `address` value is `hangzhou` or `shanghai`.
Time	`create_time>'2020-01-01'` or `create_time>'2020-01-01 00:00:00'`	Search for data created in 2020 and later. Important You cannot enter `create_time>'2020'`. DTS uses the Coordinated Universal Time (UTC)+8 time zone. If your time data contains time zone information, you must convert the time in the filter condition to the time in the UTC+8 time zone and then enter the converted filter condition in the Filter Conditions field.

What to do next

View filter conditions
- For a DTS task that is being configured: Go back to the Configure Objects and Advanced Settings step. In the Selected Objects section, view the objects to be synchronized or migrated, and right-click an object. In the dialog box that appears, view the value in the Filter Conditions field.
- For a DTS task that is configured and is running: You can use the Create Similar Task or Modify Synchronization Objects feature to go to the Configure Objects and Advanced Settings step. In the Selected Objects section, view the objects to be synchronized or migrated, and right-click an object. In the dialog box that appears, view the value in the Filter Conditions field (after you view the filter condition, you can directly cancel the task without proceeding further). For more information, see Create a similar task and Modify the objects to be synchronized.
Modify filter conditions
- For a DTS task that is being configured: Go back to the Configure Objects and Advanced Settings step. In the Selected Objects section, view the objects to be synchronized or migrated, and right-click an object. In the dialog box that appears, modify the value in the Filter Conditions field.
- For a DTS task that is configured and is running:
  - If the task is a data migration task: Data migration tasks do not support modifying filter conditions. You need to reconfigure the task.
  - If the task is a data synchronization task: Use the Modify Synchronization Objects feature to go to the Configure Objects and Advanced Settings step. In the Selected Objects section, view the objects to be synchronized, and right-click an object. In the dialog box that appears, modify the value in the Filter Conditions field and configure other parameters that are required for the task as prompted. For more information, see Modify the objects to be synchronized.

Common errors and troubleshooting

If the filter conditions that you specify are invalid, the following errors may occur. You can fix the reported error based on the error message and the "What to do next" section of this topic.

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 fields configured for the synchronization objects is incorrect.
DTS-RETRY-ERR-0145	Unknown column (.*)? in	The fields in the filter condition configured for the task do not exist in the source database.
DTS-RETRY-ERR-0147	You have an error in your SQL syntax	The fields in the filter condition configured for the task do not exist in the source database.
DTS-RETRY-ERR-0188	null value in column (.*)? violates not-null constraint	A column with the NOT NULL constraint in the destination is filtered out, or the value of the corresponding column in the source is NULL.
DTS-RETRY-ERR-0276	if (.*)? is specified it must not be empty	The value mapped to the `_id` column in Elasticsearch is empty. You need to filter out data with empty values.

FAQ

Why does a filter condition not take effect?
The filter condition is invalid or is specified for an incremental data synchronization task or an incremental data migration task whose source is a MongoDB database.
What does a filter condition apply to after the filter condition is modified?
- For a DTS task that is being configured: If the source is a MongoDB database, the filter condition applies to full data synchronization or full data migration. If the source is a database of another type, the filter condition applies to full data synchronization and incremental data synchronization or full data migration and incremental data migration.
- For a DTS task that is configured and is running: If the source is a MongoDB database, the filter condition does not apply to the DTS task (not supported). If the source is a database of another type, the filter condition applies to incremental data synchronization or incremental data migration.
Can I specify multiple filter conditions at a time?
No, you can specify filter conditions for multiple objects separately.
Can I filter out the data that meets the specified condition and synchronize or migrate the remaining data to the destination?
- If the source is a Tair/Redis instance: Yes, you can configure the Prefixes of Keys to Be Filtered Out parameter to filter out keys that meet the conditions.
- If the source is a database of another type, you cannot filter out data. You can use other filter conditions to meet your business requirements. For example, if you want to filter out data in which the value of the age field is 25, you can enter age > 25 or age < 25 in the Filter Conditions field.
How do I specify a filter condition if I want to filter data based on multiple keys?
You can use commas (,) to separate multiple keys.
Can I configure both the Prefixes of Keys to Be Synchronized and Prefixes of Keys to Be Filtered Out parameters?
Yes. In this case, the filter conditions specified by the Prefixes of Keys to Be Synchronized and Prefixes of Keys to Be Filtered Out parameters both take effect.
Can I call an API operation to specify filter conditions?
Not supported.

Related API operations

API operation	Description
Configure DTS migration or synchronization task	Configure DTS migration or synchronization tasks. You can configure the `filter` parameter in `Dblist` to set filter conditions.