Configure and modify matching rules

Background information

When you create a data migration or data synchronization task, you must specify the objects to migrate or synchronize. Data Transmission Service (DTS) provides three methods for this: Specify Objects, Import Objects, and Matching Rules. The Matching Rules method lets you use wildcard rules to specify or modify objects. You can also configure object mapping logic between the source and destination. This string-matching feature simplifies the configuration process when you have many objects. New tables that match the rules are automatically synchronized to the destination through incremental DDL. For more information about incremental DDL for data migration, see Scope and limits of DDL synchronization. 

Limits

• DTS lets you configure multiple rules. Each rule must be on a single line and cannot contain leading or trailing spaces.

• The rules for objects to migrate or synchronize cannot be empty. The rules for objects to exclude can be empty.

• DTS does not support DDL changes during schema migration or full migration.

• When you use matching rules to select objects, DTS does not support table names that contain special characters, such as line breaks, spaces, or .|"'`()=;/&\*?[][!].

• DTS does not support using multiple matching rules to map different tables from the same source database to different destination databases. For example, a.a* = b.a* & a.b* = c.b*.

• Reverse incremental synchronization is not supported in database or table aggregation scenarios.

  Note

  DTS checks for aggregation scenarios only when you save or start a data migration or data synchronization task. If a new aggregation scenario occurs while the task is running, DTS does not block it. This may prevent reverse incremental synchronization from correctly identifying the database and table mappings, which can lead to data quality issues.

• DTS does not currently support the `CREATE DATABASE` DDL statement. If a new database name matches the migration or synchronization rules, you must manually create the database at the destination to continue data synchronization for the new database.

Notes

• After you configure the inclusion and exclusion rules, an object is selected if its source table name is matched by the inclusion rules but not by the exclusion rules.

  Note

  A set difference is a new set that contains all elements that are in the first set but not in the second set.

• After you enable DDL synchronization, if you use a DDL statement to create a new table or modify a table schema at the source, the DDL statement is synchronized to the destination if the table name is matched by the inclusion rules but not by the exclusion rules.

• In database and table aggregation scenarios:

  • You must use matching rules to map the relationship between the source and destination.

  • We recommend that you manually create the table schema in the destination. If you use Data Transmission Service to create the schema, be sure to skip the failed objects during the schema migration step.

  • If you select DDL Synchronization in an aggregation scenario, you risk accidental data deletion. For example, if multiple source databases or tables are aggregated into a single destination database or table and you delete one of the source databases or tables, the aggregated destination database or table might also be deleted.

  • When you create a data migration task, set the Processing Strategy When Destination Object Has Records parameter in the Full Migration section to Ignore.

    Note

    If you select Ignore, full validation pulls data in `IN` mode. This mode cannot validate scenarios that have extra data at the destination, and validation performance is slightly reduced.

• If a table has a renaming map, the renaming map takes precedence. For example, if both the a.b[0-3] and a.b[3-5]=a.c rules exist, the table a.b3 is renamed to a.c.

• When you execute a `RENAME TABLE` DDL statement, if the renamed table object is not covered by the original matching or exclusion rules, unexpected synchronization issues may occur. Proceed with caution.

Configure database-to-database matching rules

1. Create a data migration or data synchronization task and configure it up to the Select Migration Objects or Select Synchronization Objects step.

   For more information, see the documentation for the relevant data migration or data synchronization task.

   Note

   • The operations for selecting migration objects and synchronization objects are the same. This topic uses selecting migration objects as an example.

   • In data synchronization tasks, the following scenarios involve configuring database-to-database matching rules: OceanBase Database MySQL Compatible Mode to AnalyticDB for MySQL, RDS PostgreSQL instance to OceanBase Database Oracle Compatible Mode, and data synchronization between tenants of the same OceanBase Database.

2. In the Select Migration Objects section, select Matching Rules.

   

3. In the Select Migration Scope section, enter the Migration Object Rules and the optional Exclusion Object Rules. For more information about supported matching rules, see Wildcard rule description.

   Note

   If your configured rules contain spaces, the migration or synchronization objects may be specified incorrectly.

4. Click Validate.

   To view the matching results, click Preview Objects once the validation is complete. The wildcard migration object rules and exclusion object rules you enter apply to both tables and views. The Matching Results include Final Objects, New Objects, and Removed Objects.

   Object	Description
   Final Objects	The final migration objects that match the configured rules.
   New Objects	The migration objects added to the final matching results compared to the previous configuration.
   Removed Objects	The migration objects removed from the final matching results compared to the previous configuration.

   After you select the objects to migrate or synchronize using matching rules, you can set filter conditions.

   

   1. In the Matching Results > Final Objects panel, hover over the target table object.

   2. Click Settings.

   3. In the Settings dialog box, enter a standard SQL WHERE clause to configure row filtering, and then click Validate Syntax. For more information, see Filter data using SQL conditions.

   4. After the syntax is validated, click OK.

      You can also view column information for the migration object in the View Columns section.

5. Follow the prompts to complete the subsequent task configuration.

Modify database-to-database matching rules

Configure database-to-message-queue matching rules

When you synchronize data from an OceanBase database to DataHub, Kafka, or RocketMQ, you can use matching rules to select the objects to synchronize.

1. Create a data synchronization task and proceed with the configuration to the Select Synchronization Objects step.

   For more information, see the documentation for the relevant data synchronization task.

2. In the Select Synchronization Objects section, select Matching Rules.

   

3. Enter the Synchronization Object Rules and, optionally, the Exclusion Object Rules. For more information about the supported matching rules, see Wildcard rule description.

   The business logic for configuring matching rules differs for each type of data synchronization task.

   • If the destination is DataHub, you can select a Topic Type of Tuple or BLOB.

     • The Tuple type supports only existing topic names. It does not support wildcards or spaces. After you select a table, it is mapped one-to-one to a topic.

     • The BLOB type supports many-to-one or one-to-one mapping. Note that spaces are not supported.

       If you selected Schema Synchronization when you chose the synchronization type, you can enter the name of an existing topic or create a new one. You can use only one mapping method to create or select topics. If you did not select Schema Synchronization, you can enter only the names of existing topics.

   • If the destination is Kafka or RocketMQ, many-to-one or one-to-one mapping is supported. Note that spaces are not supported.

     If you selected Schema Synchronization when you chose the synchronization type, you can enter the name of an existing topic or create a new one. If you did not select Schema Synchronization, you can enter only the names of existing topics.

4. Click Validate.

   After the validation succeeds, click Preview Objects to view the matching results. The Matching Results include Final Objects, New Objects, and Removed Objects.

   After you select the objects to synchronize using matching rules, you can set filter conditions.

   

   1. In the Matching Results > Final Objects panel, hover over the target table object.

   2. Click Settings.

   3. The Settings dialog box lets you perform the following operations.

      • In the Row Filtering Condition text box, enter a standard SQL WHERE clause for row filtering. Then, click Validate Syntax. For more information, see Filter data using SQL conditions.

      • In the Sharding Column drop-down list, you can select one or more fields to use as sharding columns. This parameter is optional.

        When selecting sharding columns, select the primary key by default unless you have special requirements. If the primary key causes a load imbalance, select a unique identifier field with a more balanced load as the sharding column to avoid potential performance issues. The main functions of sharding columns are as follows:

        • Load balancing: When the destination supports concurrent writes, sharding columns are used to direct messages to specific threads.

        • Ordering: To address potential disorder from concurrent writes, DTS ensures that messages with the same sharding column value are processed in order. Ordering here refers to the change order, which is the execution order of DML statements on a column.

      • In the Select Columns section, choose the columns to synchronize. For more information, see Column filtering.

   4. Click OK.

5. Follow the prompts to complete the subsequent task configuration.

Modify database-to-message-queue matching rules

1. You can open the View Objects dialog box.

   1. Log on to the OceanBase console .

   2. In the left navigation pane, click Data Transmission > Data Synchronization.

   3. On the Data Synchronization page, click the name of the target task to view its details.

      

   4. In the upper-right corner of the page, click View Objects to view Synchronization Objects and Modification Records.

2. In the View Objects dialog box, click Modify Rules in the lower-right corner.

   

3. In the Modify Rules dialog box, you can modify the matching rules to add or remove synchronization objects.

   The business logic for configuring matching rules differs for each type of data synchronization task. For more information, see the "Configure database-to-message-queue matching rules" section.

4. Click Validate. After the validation is successful, click Preview Objects to view the matching results.

   You can hover over a new object and click Settings to set its filter conditions.

   

5. Click Next.

   • If you add objects, the system performs a precheck on the new objects.

   • If you remove objects, the page displays the specific objects to be removed.

6. After the precheck succeeds or you confirm that the objects for removal are correct, click Submit.

   After the modification starts, a record is generated on the View Objects > Modification Records tab. You can go to this tab to view the details of the record.

   

FAQ

• Insufficient permissions

  Pay attention to the permission settings of the source user. If the user has insufficient permissions, some objects are not displayed in the DTS frontend. This can prevent you from correctly configuring the matching rules. Therefore, you must add the objects for which permissions are not granted to the Exclusion Object Rules. This prevents the task from being interrupted because DTS cannot find the destination objects.

• DML filtering is not supported

  When DDL synchronization is disabled, you can still use matching rules to select objects. During incremental synchronization, if a new table matches the rules, the corresponding DDL statement is ignored. However, DTS continues to synchronize DML statements. This causes write failures to the destination object and interrupts the task. Therefore, you must create the new table at the destination or add the table to the blacklist.