When you configure the objects to be migrated in a data migration task, you can specify conditions to filter data. Only the data that meets the specified conditions is migrated to the destination database. This feature is applicable to scenarios such as regular data migration and table partitioning.

Procedure

  1. In the Configure Migration Types and Objects step, move the required objects to the Selected section, move the pointer over a database or table, and then click Edit. Select a table
  2. In the Edit Table dialog box, enter a condition in the Filter field.
    • Relational databases, such as MySQL and SQL Server. Enter an SQL condition
      Note
      • An SQL condition is a standard SQL WHERE statement. The following operators are supported: =, !=, <, and >. Only the data that meets the WHERE condition is migrated to the destination database. In this example, enter orderid>100.
      • 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 filter incremental data created after 2020, 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 apostrophes (') in a filter condition if necessary. For example, you can enter address in('hangzhou','shanghai').
      • Filter conditions are case-insensitive. If a table in the source database contains Column A and Column a, you can use an SQL WHERE statement to filter only Column A. In this case, you can enter WHERE A=10 rather than WHERE a=10.
    • Non-relational databases, such as MongoDB.
      Note You can specify a JSON-formatted condition. Only the data that meets the specified condition is migrated to the destination database. In this example, enter {"id":{$gt:"52b64e55dc4449b7ba3d1183a0ea171b"}}. Only the data with an ID greater than this value is migrated.
  3. Click Verify to check whether the syntax is valid.
    Note
    • If the syntax is valid, the Information message shows that the validation is passed.
    • If the syntax is invalid, the Error message appears. In this case, you must modify the condition based on the instructions.
  4. Click OK.
  5. Configure other parameters that are required for the data migration task.