Data Transmission Service:Specify filter conditions

Prerequisites

The DTS task is being configured in the Configure Objects and Advanced Settings step. For more information about how to configure a DTS task, see the topics listed in Overview of data synchronization scenarios and Overview of data migration scenarios.

Usage 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.

• If the source is a Tair/Redis instance, you can filter data only by using the prefix of keys.

• If the source is a 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.

• If the source is not a Tair/Redis instance or a MongoDB database, 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

1. In the Configure Objects and Advanced Settings step, move an object that you want to synchronize or migrate to the Selected Objects section and then right-click the object.

   Note

   If the source is a MongoDB database, you can select only collections as objects to be synchronized or migrated. If the source is a Tair/Redis instance, you can select only databases 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.

2. In the dialog box that appears, enter an SQL condition in the Filter Conditions field.

   The syntax of SQL conditions varies based on the type of the source, such as Tair/Redis, MongoDB, or another type of database.

   • If the source is a Tair/Redis instance:

     In the Edit Schema dialog box, configure the Prefixes of Keys to Be Synchronized or Prefixes of Keys to Be Filtered Out parameter.

     

     Sample conditions

     Filter type	Example	Description
     Synchronize objects identified by a specific prefix	dtsnew	Only objects whose keys are prefixed with dtsnew are synchronized to the destination.
     Filter out objects identified by a specific prefix	dtstest	All objects other than those whose keys are prefixed with dtstest are synchronized to the destination.
     Synchronize objects identified by multiple prefixes	dtsnew,dtsold	Only objects whose keys are prefixed with dtsnew or dtsold are synchronized to the destination.
     Mixed condition	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.

   • If the source is a MongoDB database:

     In the Edit Table dialog box, specify filter conditions in the Filter Conditions field.

     

     Sample conditions

     Filter type	Example	Description
     Other type	{"_id":{$gt:"user100844658590795****",$lte:"user101674868045948****"}}	Filter data by user ID. In this example, $gt indicates greater than, and $lte indicates smaller 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 in MongoDB documentation.

   • If the source is a database of another type:

     In the Edit Table dialog box, specify filter conditions in the Filter Conditions field.

     Note

     • In the Filter Conditions field, the following operators are supported in the SQL conditions: =, !=, <, >, and in.

     • For more information about how to specify filter conditions, see the "FAQ" section of this topic.

     

     Sample conditions

     Filter type	Example	Description
     Number	id > 100 and id <= 200 or id in (1, 2, 3)	Search for data whose id value is greater than 100 and smaller than or equal to 200, or whose id value is equal to 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.

3. Click OK.

4. Configure other parameters that are required for the DTS task.

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, click Duplicate Task or Reselect Objects in the Actions column of the DTS task 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. For more information, see Create a similar instance 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, you cannot modify the filter condition for the task. You must reconfigure a data migration task.

    • If the task is a data synchronization task, click Reselect Objects in the Actions column of the DTS task 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. Then, 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.

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. 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?

  • Yes. If the source is a Tair/Redis instance, you can configure the Prefixes of Keys to Be Filtered Out parameter to filter out data.

  • 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?

  No, you cannot call an API operation to specify filter conditions.

Related API operations