ApsaraDB for OceanBase:Use SQL conditions to filter data

Scenarios

When you create a data migration or synchronization project, you can specify 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 are supported in full migration, full synchronization, and incremental synchronization.

• Do not perform DDL operations on columns to be filtered by SQL conditions. Otherwise, migration or synchronization 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 topics about data migration or data synchronization projects of the corresponding data source types.

   

2. After you specify the database objects to be migrated or synchronized, move the pointer over the table object that you want to configure in the destination object list on the right of the Specify Migration Scope or Specify Synchronization Scope section, and click Settings.

   

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

   

   The 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 (`). Example: `col`.

   • Filter conditions support standard SQL WHERE statements. Only =, ! =, <, and >> operators are supported, and only data that meets the WHERE condition will be synchronized to the destination database. Example: `id` > 200.

   • Filter conditions support filtering based on time conditions. However, take note of the format requirements for time in SQL WHERE statements, which is yyyy-MM-dd HH:mm:ss or yyyy-MM-dd.

     For example, if you want to filter incremental data after December 31, 2022, enter `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 operator. For example, to filter data between December 31, 2021 and April 1, 2022, enter `init_date`>'20211231' and `init_date`<'20220401' .

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

4. Click Validate Syntax.

   

5. Click OK.

6. Complete subsequent project settings as prompted.