Filter the data to be migrated

Limitations

You can only filter fields within the current table. Cross-table filtering is not supported. If needed, you can specify a separate SQL condition for each table.

Procedure

1. In the Migration Types and Objects step of configuring the data migration task, after you move the objects that you want to migrate to the Selected box, hover over the table that you want to modify and click the Edit button that appears. In the Selected Objects area on the Migration Types and Objects configuration page, hover over a target table (such as the order table) and click the Edit button that appears on the right.

2. In the dialog box that appears, enter a filter condition in the Filter Conditions field.

   • For relational databases, such as MySQL and SQL Server:

     Note

     • The filter condition must be a standard SQL WHERE statement. Only the =, !=, <, and > operators are supported. The task migrates only data that meets this condition to the destination database. For this example, enter orderid>100.

     • You can also filter by time, but the time format in the SQL WHERE statement must be precise. For example, to filter for incremental data created after 2020, enter create_time>'2020-01-01' or create_time>'2020-01-01 00:00:00', not create_time>'2020'.

     • If you need to use quotation marks in a filter condition, use single quotation marks ('). For example: address in('hangzhou','shanghai').

     • Column names in a filter condition are case-insensitive. This can cause issues if a table in your source database contains columns with case-variant names (for example, A and a). In such cases, the SQL WHERE statement may only apply to one of them. For instance, WHERE A=10 might be supported while WHERE a=10 is not.

   • For non-relational databases, such as MongoDB: In the Edit Collection dialog box, set Collection Name to customer, and enter a JSON query condition in the Filter Condition text area. You can click the Verify Syntax button to validate the syntax of the condition.

     Note

     You can use JSON-formatted query conditions. The task migrates only data that meets the specified condition to the destination database. For example, entering {"id":{$gt:"52b64e55dc4449b7ba3d1183a0ea171b"}} migrates only data with an ID greater than the specified value.

3. Click Verify Syntax to validate the condition.

   Note

   • If the syntax is valid, an Tips dialog box appears with the message Validation passed.

   • If the syntax is invalid, an Error dialog box appears. You must modify the filter condition based on the information provided in the dialog box.

4. Click OK.

5. Follow the on-screen prompts to complete the data migration task configuration.