All Products
Search

This Product

    Data Transmission Service:Use SQL conditions to filter data

    Document Center

    Data Transmission Service:Use SQL conditions to filter data

    Last Updated:Oct 13, 2023

    When you select objects for a Data Transmission Service (DTS) task, you can specify SQL conditions to filter data. Only the data that meets the specified conditions is synchronized or migrated to the destination database. This feature is applicable to scenarios such as data synchronization or migration at regular intervals and table partitioning.

    Prerequisites

    A DTS task is being configured. The current step is Configure Objects and Advanced Settings. For more information about how to create and configure a DTS task, see Configure a data synchronization task by using the new DTS console.

    Limits

    • You can filter the fields only in the current table. Cross-table filtering is not supported. You can specify filter conditions separately for each table if necessary.

    • If the source database is an ApsaraDB for 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.

    Procedure

    1. In the Configure Objects and Advanced Settings step, move a table to the Selected Objects section and then right-click the table.

    2. In the Edit Table Name dialog box, enter one or more SQL conditions in the Filtering Conditions field. SQL过滤条件

      Note

      • An SQL condition is a standard SQL WHERE statement. The following operators are supported: =, !=, <, >, and in. Only the data that meets the WHERE condition is synchronized or migrated to the destination database. In this example, order>100 is used.

      • You can specify a time condition in an SQL WHERE statement. However, you must make sure that the specified time condition is valid. For example, to query incremental data generated in 2020 and later, you must enter create_time>'2020-01-01' or create_time>'2020-01-01 00:00:00'. You cannot enter create_time>'2020'.

      • You can use single quotation marks (') in an SQL condition if necessary. For example, you can enter address in('hangzhou','shanghai').

      • Filter conditions are not case-sensitive. If a table in the source database contains Column A and Column a, you can use an SQL WHERE statement to filter only data in Column A. In this case, you can enter WHERE A=10 rather than WHERE a=10.

      • Filter statements supported by ApsaraDB for MongoDB instances are different from standard SQL WHERE statements. For example, if you want to use user IDs as a filter condition, enter {"_id":{$gt:"user100844658590795****",$lte:"user101674868045948****"}}. In this statement, gt indicates greater than and lte indicates less than or equal to.

    3. Click OK.

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