All Products
Search

This Product

    Data Transmission Service:Filter task data with an SQL condition

    Document Center

    Data Transmission Service:Filter task data with an SQL condition

    Last Updated:Aug 25, 2026

    Specify an SQL condition during task object configuration to synchronize or migrate only the data that meets the condition. This feature applies to periodic data synchronization, migration, and table splitting scenarios.

    Prerequisites

    You have started a task configuration and reached the Configure Objects and Advanced Settings step. To start a task configuration, follow Configure a data synchronization task (new console).

    Limitations

    • Complex filter statements are not supported. For example, COLUMN1 IN (SELECT id FROM table1).

      OLUMN1 IN (SELECT id FROM table1).

    • If the source table or collection contains two columns whose names differ only in case, the filter task may fail to achieve the expected result.

      o columns whose names differ only in case, the filter task may fail to achieve the expected result.

    • When the source database type is Tair/Redis, only prefix-based key filtering is supported.

      , only prefix-based key filtering is supported.

    • When the source database type is MongoDB, only full synchronization or migration tasks support conditional filtering. Incremental synchronization or migration is not supported.

      synchronization or migration tasks support conditional filtering. Incremental synchronization or migration is not supported.

    • When the source database type is a database other than Tair/Redis and MongoDB, if the filter condition requires quotation marks, use single quotation marks (').

      833fe828ef">, if the filter condition requires quotation marks, use single quotation marks (').

    • Do not add a semicolon (;) at the end of a filter condition. Otherwise, the task may fail.

      end of a filter condition. Otherwise, the task may fail.

    • If an UPDATE operation is performed on the source database and the operation meets specific conditions (the before-image value meets the filter condition, but the after-image value does not), DTS converts the UPDATE statement into a DELETE statement by default and executes it in the destination database.

      Important

      In a DTS instance whose source database is SQL Server, if the filter field is not a clustered index column, the filter logic does not take effect.

    • The filter mechanism of DTS is pre-filtering. If the value of the configured filter field is changed, filtering is performed based on the value before the change.

      Example: Assume you configured the filter condition enabled = 1. DTS will only synchronize data that meets this filter condition. If the data field enabled = 1 is modified to enabled = 0, the system considers this as first deleting the enabled = 1 data and then writing the enabled = 0 data. Because the previous operation meets the filter condition, the data will be deleted; however, the subsequent data will not be synchronized because it does not meet the filter condition. If you need to synchronize both enabled = 0 and enabled = 1 data, remove the filter condition and re-synchronize.

    Procedure

    Note

    The configuration method of filter conditions depends on the source database type. It falls into three categories: Tair/Redis, MongoDB, and other database types.

    Configure filter conditions for individual objects

    1. In the Configure Objects step, move the task objects to the Selected Objects area.

      Note

      When the source database type is MongoDB, the granularity of the task objects must be at the collection level. When the source database type is Tair/Redis, the granularity must be at the Redis DB level. When the source database is of other types, the granularity must be at the table level.

    2. Right-click the target object.

    3. In the dialog box that appears, enter the filter condition.

      • When the source database type is Tair/Redis: In the Edit Schema dialog box that appears, enter Prefixes of Keys to Be Synchronized (allowlist) or Prefixes of Keys to Be Filtered Out (blocklist).

      • When the source database type is MongoDB: In the Edit Table dialog box that appears, enter Filter Conditions.

      • When the source database type is other types: In the Edit Table dialog box that appears, enter Filter Conditions.

      Note

      For information about the syntax of filter conditions, see Sample statements.

    4. After you complete the settings, click OK.

    5. Follow the prompts to complete the subsequent task configuration.

    Configure filter conditions in batches

    1. In the Configure Objects step, move the task objects to the Selected Objects area.

    2. In the upper-right corner of the Selected Objects area, click Batch Edit.

    3. In the Select Objects box, select the objects to be edited (for setting prefix filter conditions).

      Note

      Keep the default value for Select Type.

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

    5. Select Select Edit Mode.

      • Add: Retain the existing filter conditions of the current task and add new filter conditions.

      • Overwrite: Clear the existing filter conditions of the current task and use the new filter conditions.

    6. Configure filter conditions.

      • When the source database type is Tair/Redis: Based on the actual scenario, select Prefixes of Keys to Be Synchronized or Prefixes of Keys to Be Filtered Out, and enter the corresponding prefix.

      • When the source database type is MongoDB: In the text box, enter the filter condition.

      • When the source database type is other types: In the text box, enter the filter condition.

      Note

      For information about the syntax of filter conditions, see Sample statements.

    7. After you complete the settings, click OK.

    8. Follow the prompts to complete the subsequent task configuration.

    Sample statements

    The source database type is Tair/Redis

    Filter type

    Sample statement

    Description

    Synchronize a single prefix

    Enter Prefixes of Keys to Be Synchronized: dtsnew

    Only synchronize data whose keys start with dtsnew among the objects to be synchronized to the destination.

    Filter out a single prefix

    Enter Prefixes of Keys to Be Filtered Out: dtstest

    Filter out data whose keys start with dtstest among the objects to be synchronized, and synchronize the remaining data to the destination.

    Synchronize multiple prefixes

    Enter Prefixes of Keys to Be Synchronized: dtsnew,dtsold

    Only synchronize data whose keys start with dtsnew or dtsold among the objects to be synchronized to the destination.

    Combine synchronization and filtering

    Enter Prefixes of Keys to Be Synchronized: dts

    Enter Prefixes of Keys to Be Filtered Out: dtstest

    Only synchronize data whose keys start with dts but do not start with dtstest among the objects to be synchronized to the destination.

    The source database type is MongoDB

    Filter type

    Sample statement

    Description

    Other

    {"_id":{$gt:"user100844658590795****",$lte:"user101674868045948****"}}

    Filter by user ID, where $gt means greater than, and $lte means less than or equal to. The statements supported by MongoDB filter conditions are different from standard SQL WHERE statements. For more information, see SQL to MongoDB mapping.

    The source database is of other types

    Note

    • Filter Conditions supports the following operators: =, !=, <, >, and in.

    • If you have questions when entering filter conditions, first see FAQ.

    Sample statements

    Filter type

    Sample statement

    Description

    Numeric

    id > 100 and id <= 200 or id in (1, 2, 3)

    Only synchronize or migrate data whose id field is greater than 100 and less than or equal to 200, or whose id field is 1, 2, or 3.

    String

    address in('hangzhou','shanghai')

    Only synchronize or migrate data whose address field is hangzhou or shanghai.

    Time

    create_time>'2020-01-01' or create_time>'2020-01-01 00:00:00'

    Only synchronize or migrate data created after 2020.

    Important

    • Entering create_time>'2020' is not supported.

    • When DTS filters time-type data, it uses the GMT+8 time zone. If your time data contains time zone information, convert the time in the filter condition to the corresponding GMT+8 time first, and then enter it in 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

    Follow-up operations

    • View filter statements

      • The current task is not configured: Return to the Configure Objects and Advanced Settings step. In the Selected Objects area, expand the objects to be synchronized or migrated, then right-click the target object to view Filter Conditions.

      • The current task is configured and running: You can use the Create Similar Task or Modify Sync Objects feature to enter the Configure Objects and Advanced Settings step. In the Selected Objects area, expand the objects to be synchronized or migrated, then right-click the target object to view Filter Conditions (after viewing, cancel the task; no need to proceed further). For more information, see Create a similar job and Modify synchronization objects.

    • Modify filter statements

      • The current task is not configured: Return to the Configure Objects and Advanced Settings step. In the Selected Objects area, expand the objects to be synchronized or migrated, then right-click the target object to modify Filter Conditions.

      • The current task is configured and running:

        • Migration task: Migration tasks do not support modifying filter conditions. You need to reconfigure the task.

        • Synchronization task: Use the Modify Sync Objects feature to enter the Configure Objects and Advanced Settings step. In the Selected Objects area, expand the objects to be synchronized, then right-click the target object to modify Filter Conditions, and follow the prompts to complete the subsequent task configuration. For more information, see Modify synchronization objects.

    Common errors

    If the filter condition you entered is incorrect, the task may return the following errors. Fix the errors based on the specific error message and Follow-up operations.

    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 format of the filter field configured for the synchronization object is incorrect.

    DTS-RETRY-ERR-0145

    Unknown column (.*)? in

    The field configured in the filter condition of the current task does not exist in the source database.

    DTS-RETRY-ERR-0147

    You have an error in your SQL syntax

    The field configured in the filter condition of the current task 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 is filtered out, or the source does not have a corresponding column 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 need to filter out data with empty values.

    FAQ

    • Why doesn't the filter condition I entered take effect?

      The filter condition statement is incorrect, or the current task is an incremental task with a MongoDB source database (which does not support filter conditions).

    • What is the effective scope after I modify a filter condition?

      • The current task is not configured: When the source database is MongoDB, filter conditions take effect only in the full task phase. When the source database is of other types, filter conditions take effect in both the full and incremental task phases.

      • The current task is configured and running: When the source database is MongoDB, filter conditions do not take effect (not supported). When the source database is of other types, filter conditions take effect only for incremental data.

    • Does DTS support batch configuration of filter conditions?

      No. You can enter filter conditions separately for multiple target objects.

    • Does DTS support filtering out data that meets certain conditions and synchronizing or migrating the remaining data to the destination?

      • When the source database type is Tair/Redis: Yes. You can enter Prefixes of Keys to Be Filtered Out to filter out keys that meet the conditions.

      • When the source database is of other types: No. You can set the opposite filter condition to meet your requirements. For example, if you need to filter out data whose age field is 25, you can enter age > 25 or age < 25 in Filter Conditions.

    • How do I enter a filter condition if there are multiple keys to filter?

      Use commas (,) as separators.

    • Can I enter both Prefixes of Keys to Be Synchronized and Prefixes of Keys to Be Filtered Out?

      Yes. Both the synchronization and filter rules for keys take effect.

    • Do filter conditions support API calls?

      No.

    Related APIs

    API

    Description

    ConfigureDtsJob

    Configure a DTS migration or synchronization task and configure the filter parameter in Dblist to set filter conditions.