Filter task data with an SQL condition

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

Filtering applies only to columns in the current table. Cross-table filtering is not supported. To filter multiple tables, specify a separate condition for each.

Procedure

1. In the Configure Objects and Advanced Settings step, move task objects to the Selected Objects pane. Hover over the target table and right-click it.

2. In the Edit Table dialog box, enter an SQL WHERE clause in the Filter Conditions field.

   Note

   • The Filtering Conditions field supports standard SQL WHERE clauses. Supported operators: =, !=, <, >, and in. Only data that meets the condition is synchronized or migrated. Example: order>100.

   • Time-based filtering requires a specific date format. For example, to filter data created after 2020, enter create_time>'2020-01-01' or create_time>'2020-01-01 00:00:00'. The format create_time>'2020' is not supported.

   • Use single quotation marks (') in the Filtering Conditions field. Example: address in('hangzhou','shanghai').

   • If the source table has two columns with names that differ only by case (such as A and a), the WHERE clause filters only on the uppercase column. Example: WHERE A=10 is supported, but WHERE a=10 is not.

   • ApsaraDB for MongoDB uses a different filter syntax. For example, to filter by user ID, enter {"_id":{$gt:"user100844658590795****",$lte:"user101674868045948****"}}. In this syntax, gt means "greater than" and lte means "less than or equal to".

3. Click OK.

4. Complete the remaining task configuration as prompted.