Configure matching rules for migration or synchronization objects

This topic describes the background information, limitations, and configuration methods of matching rules for migration/synchronization objects, and provides examples and frequently asked questions about matching rules in the scenario of database to database.

Background information

When you create a data migration or synchronization task, you need to specify the migration or synchronization objects. OMS provides three methods for you to choose from: specified objects, imported objects, and matching rules. The matching rules method allows you to write wildcard rules to specify the objects for migration or synchronization. It also supports configuring object mappings between the source and target. With simple and efficient string matching capabilities, it can significantly reduce the configuration difficulty when a large number of objects are to be migrated or synchronized. Additionally, new tables that match the specified rules can be automatically synchronized to the target through incremental DDL operations. For more information about incremental DDL operations, see Supported DDL operations and limits.

Database-to-database wildcard rules

OMS supports the following database-to-database wildcard rules and provides the descriptions.

The wildcard rules are subject to the following requirements:

• The names of the databases and tables at the target cannot both be wildcard expressions. For example, kd_test*.person*=kd_test*.person*.

• If the source and target databases are both wildcard expressions, the database-level expressions must be identical, indicating database migration.

• If the source and target tables are both wildcard expressions, the table-level expressions must be identical, indicating table migration.

• If the name of the target database is a wildcard expression, the name of the source database must also be a wildcard expression.

• If the name of the target table is a wildcard expression, the name of the source table must also be a wildcard expression.

Wildcard rules for mapping databases to message queues

OMS supports the following wildcard rules for mapping databases to a message queue:

Limitations

• OMS allows you to enter multiple rules, but each rule must be entered on a single line without leading or trailing spaces.

• Migration or synchronization object rules cannot be empty, but exclusion object rules can be empty.

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

• OMS does not support selecting tables whose names contain special characters (spaces, line breaks, or .|"'`()=;/&*?[][!]) as migration or synchronization objects by using multiple matching rules.

• OMS does not support mapping tables in different databases in the target to tables in different databases in the source based on multiple matching rules. For example, a.a* = b.a* & a.b* = c.b*.

• In the multi-table aggregation scenario, reverse increment is not supported.

  Note

  OMS only judges whether there is a aggregation scenario based on the existence of the existing databases and tables when saving or starting a data migration/data synchronization task. If the multi-table aggregation scenario newly emerges during the task execution, it will not be intercepted by OMS. This may result in the reverse increment being unable to correctly identify the database-table mapping relationship, leading to data quality issues.

• OMS does not support the CREATE DATABASE DDL statement. If the name of a new database is included in the migration or synchronization rules, you need to manually create the database in the target before you can synchronize data in the new database.

Considerations

• After you configure the migration/synchronization object rules and exclusion object rules, if the source table name is in the difference set between the migration/synchronization object rules and exclusion object rules, the related object can be selected.

  Note

  The difference set between two sets refers to a new set that contains all elements in the first set but not in the second set.

• After the DDL synchronization feature is enabled, if you create a new table or modify the schema of a table by using DDL statements in the source, the related DDL statement can be synchronized from the source to the target only if the table name is in the difference set between the migration/synchronization object rules and exclusion object rules.

• In the multi-table aggregation scenarios:

  • We recommend that you configure the mappings between the source and target databases based on matching rules.

  • We recommend that you manually create schemas in the target database. If you use OMS to create schemas, make sure to skip failed objects in the schema migration step.

  • If you select DDL synchronization for multi-table aggregation, there is a risk of accidental deletion. For example, multiple databases or tables in the source are aggregated to a single database or table in the target. In this case, if you delete a database or table in the source, the corresponding aggregated database or table in the target may also be deleted.

  • When you create a data migration task, set Full Migration > Handle Non-empty Tables in Target Database to Ignore.

    Note

    If you select Ignore, full verification will use IN mode to fetch data. This mode cannot verify whether the target contains data not in the source, and the verification performance will be degraded.

• If a table is mapped to a new name, the renaming mapping takes precedence. For example, if both the rules a.b[0-3] and a.b[3-5]=a.c exist, the table a.b3 will be renamed to a.c.

• When the RENAME TABLE DDL statement is being executed, if the renamed table object is not in the original matching rules or exclusion rules, it may lead to unexpected synchronization. Proceed with caution.

Configure database-to-database wildcard rules matching rules

1. Create a data migration task and configure the task until the Select Migration Objects step.

   For more information, see Data migration tasks of the corresponding type.

2. In the Select Migration Objects section, select Match by Rule.

3. Enter the Object Migration Rules and Object Exclusion Rules (optional). For more information about supported matching rules, see Wildcard rules.

   Note

   If the rules you configured contain spaces, it may cause errors in the migration objects.

   When you migrate data from an Oracle database to the Oracle compatible mode of OceanBase Database:

   • If you selected Schema Migration in the Migration Type step, you can select sequences, types, tables, views, stored procedures, functions, packages, and synonyms in one or more source databases as migration objects.

     Notice

     If you selected only Schema Migration in the Migration Type step, you must select at least one migration object. If you select other migration types except Schema Migration, you must select at least one table object.

     The following objects will be migrated only if you selected Schema Migration in the Migration Type step. The migration timing for different types of objects is as follows:

     • Tables, views, stored procedures, functions, synonyms, types, and packages will be automatically migrated during schema migration.

     • The migration timing for indexes and triggers can be set in the migration options.

     • Sequences, constraints, and foreign keys will be migrated during forward switching.

   • If you did not select Schema Migration in the Migration Type step, you can select only tables in one or more databases as migration objects.

   • PL objects such as views, functions, and stored procedures cannot be renamed or set with row filter conditions.

4. Click Verify.

   If you want to view the matching results, click Preview Objects after the validation is successful. The wildcard migration object rules and exclude object rules that you configured will be applied to tables and views. Matching Results shows the final objects, new objects, and reduced objects.

   Object	Description
   Final Objects	The migration objects that are finally matched by the configured matching rules.
   New Objects	The migration objects added in the final match results compared with the previous configuration.
   Reduced Objects	The migration objects reduced in the final match results compared with the previous configuration.

   After you use matching rules to select the migration objects, you can set filter conditions.

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

   2. Click the displayed Settings.

   3. In the Settings dialog box, enter the WHERE clause of a standard SQL statement to configure row filters. After you configure the filter conditions, click Validate Syntax. For more information, see Filter data by using SQL conditions.

   4. If the syntax is valid, click OK.

      You can also view the column information of the migration object in the View Column section.

5. Complete subsequent task configurations as prompted.

Scenario examples

• Schema migration

  Migrate all tables starting with "test" in databases starting with "jenkins_api" from the source to the target, without changing the database or table names. The following figure shows the configuration of matching rules.

  

• Database and table renaming

  Migrate all tables starting with "test" in the jenkins_my2dh_one database to the target, and rename the jenkins_my2dh_one database to jenkins_my2dh_one_new without changing the table names. The following figure shows the configuration of matching rules.

  

• Multi-table aggregation

  Aggregate all tables starting with "order" in databases starting with "jenkins_api" to the order table in the jenkins_api_all database. The following figure shows the configuration of matching rules.

  

• Configure exclusion rules

  Configure rules to exclude the migration of historical tables and log tables in the jenkins_api_mysql56 database. Historical tables start with "history_", and log tables end with "log". The following figure shows the configuration of matching rules.

  

Configure matching rules for mapping databases to message queues

When you synchronize data to DataHub, Kafka, or RocketMQ, you can configure matching rules to specify the objects to synchronize.

1. Create a data synchronization task and configure the task settings to the Select Synchronization Objects step.

   For more information, see Data synchronization tasks.

2. In the Select Synchronization Objects section, select Match by Rule.

   

3. Enter Object Synchronization Rules and Object Exclusion Rules (optional). For more information about supported matching rules, see Wildcard rules.

   The business logic for configuring matching rules varies for the three types of data synchronization tasks.

   • If the target is a DataHub instance, you can select a topic of the Tuple or BLOB type.

     • When you select Tuple as the topic type, you can only enter existing topic names, and cannot use wildcards or spaces. After you select a table, it will be mapped to a topic on a one-to-one basis.

     • When you select BLOB as the topic type, you can use one-to-many or one-to-one mapping. However, you cannot use spaces.

       If you select Schema synchronization, you can enter existing topic names or create new topics. You can only choose one mapping method to create or select topics. If you do not select Schema synchronization, you can only enter existing topic names.

   • If the target is a Kafka or RocketMQ instance, you can use one-to-many or one-to-one mapping. However, you cannot use spaces.

     If you select Schema synchronization, you can enter existing topic names or create new topics. If you do not select Schema synchronization, you can only enter existing topic names.

4. Click Verify.

   After the configuration is verified, you can click Preview Objects to view the matching results. The Matching Results section shows the final objects, new objects, and reduced objects.

   After you specify the matching rules, you can filter objects and specify the sharding columns.

   

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

   2. Click the displayed Settings icon.

   3. In the Settings dialog box, you can perform the following operations.

      • When you synchronize an object from an OceanBase database, enter a standard SQL WHERE clause in the text box in the Row Filters section to specify the row filter condition. After you configure the row filter condition, click Validate Syntax. For more information, see Filter data by using SQL conditions.

      • Select the target sharding column from the Sharding Columns drop-down list. You can select multiple fields as the sharding column. This parameter is optional.

        By default, you can select the primary key. If the primary key is unevenly loaded, you can select a uniquely identified field with relatively balanced load as the sharding column to avoid potential performance issues. The sharding column serves the following purposes:

        • Load balancing: In the case of concurrent writes in the target, the sharding column identifies the specific thread that is used to send messages.

        • Order: To avoid the disorder that can be caused by concurrent writes, OMS ensures that messages received by users are in order when the values in the sharding column are the same. Here, order refers to the change order (the execution order of DML operations on a column).

   4. Click OK.

FAQ

• Insufficient privileges

  Check the permissions of the source user. If the user does not have sufficient permissions, OMS may fail to display some objects, which prevents you from correctly configuring the matching rules. In this case, you need to add the objects that cannot be displayed to Object Exclusion Rules to prevent the interruption of data migration or synchronization tasks because OMS cannot find the corresponding objects.

• Filtering out DML statements is not supported

  If you do not enable DDL synchronization, OMS allows you to select objects based on matching rules. During incremental synchronization, if a new table matches the rules, OMS will ignore the corresponding DDL statements but continue to synchronize the DML statements. This will cause the failure to write data to the target table, which can interrupt data migration or synchronization tasks. In this case, you can create a new table in the target or add the table to the blocklist.