Filter objects by using SQL conditions

This topic describes the scenarios, limitations, and procedure for filtering objects by using SQL conditions when you create a data validation task.

Scenarios

When you create a data validation task, you can set an SQL filter condition to filter objects. After you set the filter condition, only objects that meet the filter condition are validated. This feature is suitable for various scenarios such as regular data validation, splitting data tables, filtering historical data, and filtering dirty data.

Limitations

• When you filter objects by using SQL conditions, you can only filter fields of the current table. Cross-table filtering is not supported.

• The scope of SQL condition-based object filtering is full validation.

Procedure

1. Configure the data validation task to the Select Type & Objects step's Select Objects section.

   For more information about how to configure a data validation task, see Create a validation task.

2. After you select the validation object, click the Row Filtering icon that appears next to the target table object in the right-side list.

3. In the Settings dialog box, enter the WHERE clause of a standard SQL statement to configure row filtering.

   The syntax of the SQL WHERE statement is described as follows:

   • Column names are case-sensitive. You must enter the correct column names and add backticks () to them. For example, \col`.

   • The filter condition supports only standard SQL WHERE statements with the =, !=, <, and > operators. Only data that meets the WHERE condition is migrated to the destination database. For example, `id` > 200.

   • The filter condition supports filtering based on time conditions. However, note the format requirements for dates and times in the SQL WHERE statement (yyyy-MM-dd HH:mm:ss or yyyy-MM-dd). For example, to filter incremental data after December 31, 2022, enter `date_now` > '2022-12-31' or `date_now` > '2022-12-31 00:00:00'.

   • To filter data within a specific period, use the and or or statement. For example, to filter data from December 31, 2021, to April 1, 2022, enter `init_date` > '2021-12-31' and `init_date` < '2022-04-01'.

   • Use single quotation marks (') in the filter condition. For example, `address` in ('BEIJING','HANGZHOU').

4. Click Validate Syntax to confirm that the syntax verification passes.

5. After you verify that the columns are correct, click OK.

6. Complete the remaining task configurations as prompted.