All Products
Search
Document Center

Data Transmission Service:Specify filter conditions

Last Updated:Sep 04, 2024

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 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 an ApsaraDB for Redis Enhanced Edition (Tair) instance, you can filter data only by using the prefix of keys.

  • If the source is an 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 an ApsaraDB for Redis Enhanced Edition (Tair) 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.

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 ApsaraDB for Redis Enhanced Edition (Tair) 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 ApsaraDB for Redis Enhanced Edition (Tair), MongoDB, or another type of database.

    • If the source is an ApsaraDB for Redis Enhanced Edition (Tair) instance:

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

      image.png

      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.

      image.png

      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.

      image.png

      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 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 specify 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 condition that you enter is 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 fields used to filter the objects to be synchronized are specified in an invalid format.

DTS-RETRY-ERR-0145

Unknown column (.*)? in

The fields in the filter condition configured for the task do not exist in the source.

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.

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 the destination Elasticsearch cluster is empty.

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 an ApsaraDB for Redis Enhanced Edition (Tair) 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

API operation

Description

ConfigureDtsJob

Configures a data migration or synchronization task. You can configure the filter field in the Dblist request parameter to specify a filter condition.