Filter the data to be migrated

Limitations

• Complex filter statements are not supported. For example, COLUMN1 IN (SELECT id FROM table1).

  OLUMN1 IN (SELECT id FROM table1).

• If the source table or collection contains two columns whose names differ only in case, the filter task may fail to achieve the expected result.

  o columns whose names differ only in case, the filter task may fail to achieve the expected result.

• When the source database type is Tair/Redis, only prefix-based key filtering is supported.

  , only prefix-based key filtering is supported.

• When the source database type is MongoDB, only full synchronization or migration tasks support conditional filtering. Incremental synchronization or migration is not supported.

  synchronization or migration tasks support conditional filtering. Incremental synchronization or migration is not supported.

• When the source database type is a database other than Tair/Redis and MongoDB, if the filter condition requires quotation marks, use single quotation marks (').

  833fe828ef">, if the filter condition requires quotation marks, use single quotation marks (').

• Do not add a semicolon (;) at the end of a filter condition. Otherwise, the task may fail.

  end of a filter condition. Otherwise, the task may fail.

• If an UPDATE operation is performed on the source database and the operation meets specific conditions (the before-image value meets the filter condition, but the after-image value does not), DTS converts the UPDATE statement into a DELETE statement by default and executes it in the destination database.

  Important

  In a DTS instance whose source database is SQL Server, if the filter field is not a clustered index column, the filter logic does not take effect.

• The filter mechanism of DTS is pre-filtering. If the value of the configured filter field is changed, filtering is performed based on the value before the change.

  Example: Assume you configured the filter condition enabled = 1. DTS will only synchronize data that meets this filter condition. If the data field enabled = 1 is modified to enabled = 0, the system considers this as first deleting the enabled = 1 data and then writing the enabled = 0 data. Because the previous operation meets the filter condition, the data will be deleted; however, the subsequent data will not be synchronized because it does not meet the filter condition. If you need to synchronize both enabled = 0 and enabled = 1 data, remove the filter condition and re-synchronize.

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.