Use SQL conditions to filter data

This topic describes the application scenarios, limitations, and procedure for using SQL conditions to filter data after you select migration or synchronization objects when you create a data migration or synchronization project.

Application scenarios

When you create a data migration or synchronization project, you can set SQL conditions to filter data. Only the data that meets the filter conditions will be migrated to the destination database. This feature allows you to filter data in multiple scenarios, such as scheduled data synchronization or migration, data table sharding, and historical and dirty data filtering.

Limitations

• An SQL condition filters only fields of the current table. Cross-table filtering is not supported.

• SQL conditions take effect in full migration, full synchronization, and incremental synchronization.

• Do not perform DDL operations on columns to be filtered by SQL conditions. Otherwise, migration exceptions may occur.

Procedure

1. Configure the data migration or synchronization project to the Select Migration Objects or Select Synchronization Objects step.

   For more information, see the documentation of data migration or data synchronization projects of the corresponding type.

2. Select migration or synchronization objects. Then, in the Destination Object list in the Specify Migration Scope or Specify Synchronization Scope section, hover over the target table object and click Settings.

3. In the Settings dialog box, specify a standard SQL WHERE clause to filter data by row.

   Syntax of the SQL WHERE clause is as follows:

   • The system does not perform any case conversion on column names. Enter the correct column name and enclose the column name with escape characters (`), for example, `col`.

   • The system supports filter conditions in standard SQL WHERE clauses (=, !=, < and > operators only), and migrates only data that meets the WHERE conditions to the destination database, for example, `id` > 200.

   • The system supports only time filter conditions in the format of yyyy-MM-dd HH:mm:ss or yyyy-MM-dd in the WHERE clause. For example, if you want to retrieve incremental data after December 31, 2022, you can specify `date_now` > '2022-12-31' or `date_now` > '2022-12-31 00:00:00'.

   • If you want to retrieve data within a specified period, use the and or or statement. For example, if you want to retrieve data between December 31, 2021 and April 1, 2022, specify `init_date`>'2021-12-31' and `init_date`<'2022-04-01'.

   • Use single quotation marks (') in the filter criteria, for example, `address` in ('BEIJING','HANGZHOU').

4. Click Validate Syntax.

5. Click OK.

6. Complete subsequent project settings as prompted.