Use SQL conditions to filter data

This topic describes the scenarios, limits, and procedure of data filtering by using SQL conditions after you select the migration objects when you create a data migration task.

Application scenarios

When you create a data migration task, you can specify SQL conditions to filter data. Only the data that meets the filter conditions will be migrated to the target database. This feature applies to multiple scenarios, such as periodical data migration, data table splitting, historical data filtering, and dirty data cleansing.

Limitations

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

• SQL conditions are supported in full migration and incremental synchronization.

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

Procedure

1. Create a data migration task and proceed to the Select Type & Objects step.

   For more information, see the topic about creating a data migration task between the corresponding data sources in the Data migration chapter.

2. After you select the migration objects, find the desired object in the object list on the right side, and click Row Filtering.

   

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 statements (=, !=, < and > operators only), and migrates only data that meets the WHERE conditions to the target 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. After validating the columns, click OK.

6. Complete subsequent task settings as prompted.