All Products
Search

This Product

    :Use SQL conditions to filter data

    Document Center

    :Use SQL conditions to filter data

    Last Updated:Mar 27, 2023

    This topic describes the application scenarios, limits, and procedure of data filtering by using SQL conditions after you select the synchronization objects when you create a data synchronization project.

    Scenarios

    When you create a data synchronization project, you can specify SQL conditions to filter data. Only the data that meets the filter conditions will be synchronized 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.

    Limits

    • An SQL condition filters only fields of the current table. Cross-table filtering is not supported.

    • SQL conditions are supported in full synchronization and incremental synchronization.

    Procedure

    1. Create a data migration project and configure it to the Select Synchronization Objects step.

      For more information, see the topic about creating a data synchronization project between the corresponding data sources in the Data synchronization chapter.

    2. Select the synchronization object, and then move the pointer over the target object in the Destination Objects list on the right of the Specify Synchronization Scope section.

    3. Click Settings.

      image

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

      image

      Syntax of the SQL WHERE clause:

      • Enclose column names with escape characters (`). Example: `col`. The column name enclosed with escape characters (`) is a string that matches the actual column name. Case conversion and other processing are not performed on these column names.

      • 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 need to filter data within a specified period, use the `and` or `or` statement. 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').

    5. Click Validate Syntax.

      image

    6. Click OK.

    7. Complete subsequent project settings as prompted.