Migrate data from OceanBase Database to DataHub

This topic describes how to use the data migration service to migrate data from OceanBase Database (including MySQL and Oracle compatibility modes) to DataHub.

Prerequisites

• You have created a cluster and a tenant in the source OceanBase database. For more information, see Create an instance and Create a tenant.

• You have created a target database instance.

• You have created dedicated database users for data migration on both the source and target sides and granted them the required permissions. For more information, see User privileges.

Limitations

• Only users with the project role of Project Owner, Project Admin, or Data Services Admin can create a data migration task.

• When full migration is selected, data transfer only supports synchronizing tables with unique keys.

• Synchronizing DDL is only supported for topics of the BLOB type.

• The data migration service only supports migrating objects whose database, table, and column names contain ASCII characters and do not include special characters (including .|"'`()=;/& and line breaks).

• Table names to be synchronized and column names within them must not contain Chinese characters.

• During data migration, data transfer supports dropping a table and then creating a new one; that is, you can perform a DROP TABLE operation on a table already being synchronized, followed by a CREATE TABLE. Data transfer does not support creating a new table by renaming an existing one; that is, operations like RENAME TABLE a TO a_tmp are not supported.

Data transfer supports data synchronization for character sets configured as UTF-8 and GBK.

The following limitations apply to DataHub:

• DataHub officially limits the size of a single message based on the cloud environment, typically to 1 MB.

• Because messages are sent in batches, DataHub limits each batch to 4 MB.

• For official limitations and naming conventions of DataHub, see Limitations.

Considerations

• Clock skew between nodes, or between the client and the server, can lead to inaccurate latency reporting during incremental synchronization.

  For example, if a clock is ahead of standard time, the reported latency may be negative. If a clock is behind, it may cause a positive latency.

• In a data migration task where the source is an OceanBase database with DDL synchronization enabled, if a rename (RENAME) operation occurs on a table in the source database, it is recommended to restart the task to avoid data loss during incremental synchronization.

• When OceanBase Database is version V4.0.0 to V4.3.x (excluding V4.2.5 BP1) and incremental synchronization is selected, configure the STORED attribute for generated columns. Otherwise, the incremental logs will not contain information for the generated columns, which may cause data anomalies during incremental synchronization.

• When an updated row contains a LOB column:

  • If the LOB column is among the updated columns, do not rely on its value before the UPDATE or DELETE operation.

    Data types currently stored using LOB columns include JSON, GIS, XML, UDT (user-defined types), and various TEXT types such as LONGTEXT and MEDIUMTEXT.

  • If the LOB column is not among the updated columns, its value is NULL both before and after the UPDATE or DELETE operation.

• When creating a data migration task, if you configure only Incremental Synchronization, the local incremental logs in the source database must be retained for more than 48 hours.

  If you configure both Full Migration and Incremental Synchronization when creating the data migration task, the local incremental logs in the source database must be retained for at least 7 days. Otherwise, the data migration task may fail or cause data inconsistency between the source and target databases due to the inability to obtain incremental logs.

• When synchronizing incremental data from an OceanBase database to DataHub, the initialization schema for the table structure is also synchronized to the DataHub schema. The following table describes the data types supported by DataHub, which apply only to Tuple-type topics.

  Type	Description	Value range
  BIGINT	8-byte signed integer	-9223372036854775807 ~ 9223372036854775807
  DOUBLE	8-byte double-precision floating-point number	-1.0 _10^308 ~ 1.0 _10^308
  BOOLEAN	Boolean type	True/False
  BOOLEAN	Boolean type	true/false
  BOOLEAN	Boolean type	0/1
  TIMESTAMP	Timestamp type	Timestamp type representing time down to microseconds
  STRING	String, supports only UTF-8 encoding	A single STRING column supports up to 2 MB
  INTEGER	4-byte integer	-2147483648 ~ 2147483647
  FLOAT	4-byte single-precision floating-point number	-3.40292347_10^38 ~ 3.40292347_10^38
  DECIMAL	Numeric type	- 10^38 +1 ~ 10^38 - 1

Supported source and target instance types

Data type mappings

Currently, only the following data types are supported for migration to DataHub: INTEGER, BIGINT, TIMESTAMP, FLOAT, DOUBLE, DECIMAL, STRING, and BOOLEAN.

• If you create a topic of another type when selecting the method to create a new topic for mapping, data migration will fail.

• The default mapping rules provided in the table below are the most appropriate. Changing the mapping may result in an error.

Data type mappings between a MySQL-compatible tenant of OceanBase Database and DataHub

When migrating data from a MySQL-compatible tenant of OceanBase Database to DataHub, you can select the Topic type as BLOB or Tuple. The following table describes the data type mappings for DataHub (Tuple type) when the serialization format is set to Default or DTSCompatible. For details on the data type mappings for DataHub (BLOB type), see Data formats.

• When migrating data from a MySQL-compatible tenant of OceanBase Database to DataHub (Tuple type) and setting the serialization format to Default, the data type mappings are as follows.

  Data type in a MySQL-compatible tenant of OceanBase Database	Default mapped-to data type in DataHub
  BIT	STRING (Base64-encoded)
  CHAR	STRING
  BINARY	STRING (Base64-encoded)
  VARBINARY	STRING (Base64-encoded)
  INT	BIGINT
  TINYTEXT	STRING
  SMALLINT	BIGINT
  MEDIUMINT	BIGINT
  BIGINT	DECIMAL (because the maximum unsigned value exceeds the maximum LONG value in Java)
  FLOAT	DECIMAL
  DOUBLE	DECIMAL
  DECIMAL	DECIMAL
  DATE	STRING
  TIME	STRING
  YEAR	BIGINT
  DATETIME	STRING
  TIMESTAMP	TIMESTAMP (displayed with millisecond precision)
  VARCHAR	STRING
  TINYBLOB	STRING (Base64-encoded)
  TINYTEXT	STRING
  BLOB	STRING (Base64-encoded)
  TEXT	STRING
  MEDIUMBLOB	STRING (Base64-encoded)
  MEDIUMTEXT	STRING
  LONGBLOB	STRING (Base64-encoded)
  LONGTEXT	STRING

• When migrating data from a MySQL-compatible tenant of OceanBase Database to DataHub (Tuple type) and setting the serialization format to DTSCompatible, the data type mappings are as follows.

  Data type in a MySQL-compatible tenant of OceanBase Database	Default mapped-to data type in DataHub
  CHAR	STRING
  VARCHAR	STRING
  BINARY	STRING
  VARBINARY	STRING
  BIT(1): The values of BIT are 0 and 1.	BOOLEAN When the value of a BIT column is 0, the corresponding BOOLEAN value in DataHub is false. When the value of a BIT column is 1, the corresponding BOOLEAN value in DataHub is true.
  BIT(n)	STRING (in hexadecimal). Currently, the number of bits of a BIT data type may not be aligned with that of a database. For example, for bit(10), if the data is 0, it is displayed as 000 in DTS, but as 00 during data transmission.
  TINYINT	BIGINT
  SMALLINT	BIGINT
  MEDIUMINT	BIGINT
  INT	BIGINT
  BIGINT	BIGINT. In DataHub, BIGINT is a signed type with a range of -9223372036854775807 to 9223372036854775807. If data exceeding 9223372036854775807 (which is the maximum value of an unsigned BIGINT type in the source database) exists, data overflow may occur in DataHub, leading to data inconsistency.
  FLOAT	DOUBLE. It has 7 significant digits. Inaccuracies may occur when the data exceeds 7 digits.
  DOUBLE	DOUBLE. It has 16 significant digits. Inaccuracies may occur when the data exceeds 16 digits.
  DECIMAL	DECIMAL
  DATE	TIMESTAMP (converted using the +08:00 time zone, with microsecond precision)
  TIME	STRING
  YEAR	STRING
  DATETIME	TIMESTAMP (converted using the +08:00 time zone, with microsecond precision)
  TIMESTAMP	TIMESTAMP (with microsecond precision)
  TINYTEXT	STRING
  MEDIUMTEXT	STRING
  TEXT	STRING
  LONGTEXT	STRING
  TINYBLOB	STRING (in hexadecimal)
  MEDIUMBLOB	STRING (in hexadecimal)
  BLOB	STRING (in hexadecimal)
  LONGBLOB	STRING (in hexadecimal)

Data type mapping between an Oracle-compatible tenant of OceanBase Database and DataHub

Additional properties

If you create a topic manually, add the following properties to the schema in DataHub before starting a data synchronization task. If Data Transmission automatically creates the topic and synchronizes the structure, it will add these properties automatically.

When the source is a MySQL-compatible tenant of OceanBase Database and you select the Topic type as Tuple on the Select Synchronization Objects page, and then select Serialization Method as DTSCompatible on the Synchronization Options > Advanced Options page, the column names will be prefixed with dts_. For example, if the column name columnName in the source MySQL-compatible tenant of OceanBase Database is c1, it will be synchronized to dts_c1 in the target DataHub. The field conversion is described below.

Procedure

1. Create a data migration task.

   1. Log in to the OceanBase Cloud console.

   2. In the left-side navigation pane, click Data Services > Data Migration.

   3. On the Data Migration page, click the Data Migration tab.

   4. On the Data Migration tab, click Create Task in the upper-right corner.

2. In the Edit Task Name text box, enter a custom name for the migration task.

   We recommend using letters and numbers. The name must not contain spaces and cannot exceed 64 characters.

3. On the Configure Source & Target page, configure the parameters.

   1. In the Source section, configure the parameters.

      If you need to reference an existing data source, click Quick Fill next to Source and select the target data source from the drop-down list. After selection, the configurations in the source section will be automatically populated. If you want to save the current configuration as a new data source, click the Save icon in the upper-right corner of the Source section.

      You can also click Manage Data Source in the Quick Fill drop-down list to go to the Data Source page to view and manage data sources. This page provides unified management for different types of data sources. For more information, see the Data sources topic.

      Parameter	Description
      Cloud Vendor	Currently supports Alibaba Cloud.
      Region	Select the region where the source database is located.
      Database Type	Select the source database type as OceanBase MySQL Compatible or OceanBase Oracle Compatible based on your actual situation.
      Instance Type	Select the corresponding channel-supported Cluster Instance (Transactional), Cluster Instance (Analytical), or Cluster Instance (Flagship).
      Instance	The ID or name of the instance where the OceanBase database is located. You can view the ID or name of the target instance on the Cluster List page. Note When the cloud vendor is Alibaba Cloud, cross-account authorization for Alibaba Cloud root account instances is also supported. For details, see Alibaba Cloud Account Authorization.
      Tenant	The ID or name of the OceanBase database tenant. You can expand the target instance on the Instances page to view the ID or name of the target tenant under that instance.
      Account	The username of the OceanBase database user used for data migration.
      Password	The password of the database user.

   2. In the Target section, configure the parameters.

      If you need to reference an existing data source, click Quick Fill next to Target and select the target data source from the drop-down list. After selection, the configurations in the target section will be automatically populated. If you want to save the current configuration as a new data source, click the Save icon in the upper-right corner of the Target section.

      You can also click Manage Data Source in the Quick Fill drop-down list to go to the Data Source page to view and manage data sources. This page provides unified management for different types of data sources. For more information, see the Data sources topic.

      Parameter	Description
      Cloud Vendor	Currently supports selecting Alibaba Cloud.
      Region	Select the region where the target database is located.
      Database Type	Select DataHub.
      Instance Type	Currently supports selecting Cloud instance.
      Connection Type	Includes Direct Private Network Connection and Public IP. You must first add the data source IP address displayed on the page to the allowlist of the cloud database security group to ensure connectivity. For details, see Add access control for Alibaba Cloud DataHub instances in Add an Alibaba Cloud instance to the allowlist. Note The data source IP addresses to be added to the allowlist are displayed on the page only after you have selected the regions for the source and target.
      Project	The name of the DataHub instance.
      AccessKey	The AccessKey ID created in the Alibaba Cloud account management console, used for authentication.
      SecretKey	The AccessKey secret created in the Alibaba Cloud Account Management Console, used for authentication.

4. Click Test and Continue.

   On the Select Type & Objects page, configure the parameters.

   1. In the Migration Type section, select the migration type for the current data migration task.

      Parameter	Description
      Schema Migration	Creates a topic.
      Full Migration	After a full migration task starts, the data migration service migrates the existing data from source database tables to the corresponding topic in the target database.
      Incremental Synchronization	After an incremental synchronization task starts, data migration synchronizes changed data (inserts, modifications, or deletions) from the source database to the corresponding topic in the target database. Incremental Synchronization includes DML Synchronization and DDL Synchronization, which you can configure as needed. For details, see Configure DML/DDL synchronization.
      Topic Type	You can select Tuple or Blob. Tuple: Supports strongly schema-based structured data, similar to database record data, where each record contains multiple columns. Blob: Untyped unstructured data, supporting only writing binary data as a record. Data is transmitted after being encoded in BASE64.

   2. In the Select Migration Objects section, configure the method for selecting migration objects.

      You can select migration objects using either Specify Objects or Match by Rule.

   3. In the Select Migration Scope section, select the objects to be migrated.

      • When you select Specify Objects, data migration supports Table-level and Database-level. Database table migration allows you to select one or more tables from a database as migration objects, while full database migration allows you to select an entire database as migration objects. If you select database table migration for a database, that database cannot be used for full database migration. Similarly, if you select full database migration for a database, that database cannot be used for database table migration.

        Note

        When the topic type is Tuple, only database table migration is supported.

        When migrating data from OceanBase Database to DataHub, multi-table-to-multi-topic migration is supported.

        1. Select the objects to be migrated on the left side of the selection area.

        2. Click >.

        3. According to your business needs, select a mapping method and click OK.

           If Schema Synchronization is not selected when choosing the synchronization type, only Existing Topics is available. If Schema Synchronization is selected, only one mapping method is available for creating or selecting a topic.

           For example, if schema synchronization is selected and you use both the "Create Topic" and "Select Existing Topic" mapping methods, or change the topic name via renaming, the precheck will report an error due to option conflict.

           Parameter	Description
           Create Topic	Enter the name for the new topic in the text box. It must be 128 characters or less, contain letters, numbers, or underscores (_), and start with a letter.
           Replace Topic	Data transmission provides the capability to query DataHub topics. You can click Select Topic and search for the required topic in the Existing Topics drop-down list.
           Batch Generate Topic	The rule for batch generating topics is Topic_${Database Name}_${Table Name}.

           Notice

           If you choose "Create Topic" or "Batch Generate Topic", after schema synchronization succeeds, the newly created topic will be visible on the DataHub side. By default, it has 2 shards, a data expiration time of 7 days, and this cannot be modified.

        After adding, confirm the migration objects in the selected boxes.

        Action	Description
        Import Objects	In the right-side list of the selected region, click Import Object in the upper-right corner. For more information, see Import migration objects.
        Change Topic	Data migration supports replacing target objects with a different topic. For more information, see Change topic.
        Settings	Data migration supports filtering rows using WHERE conditions, and you can select sharding columns and columns to synchronize. In the Settings dialog box, you can perform the following operations. In the text box of the Row Filters section, enter a WHERE condition to filter rows. For more information, see Filter data by SQL conditions. In the Sharding Columns drop-down list, select the target sharding column. You can select multiple fields as sharding columns. This parameter is optional. When selecting sharding columns, if there are no special circumstances, it is sufficient to select the primary key by default. If there is load imbalance on the primary key, select a unique identifier field with relatively balanced load as the sharding column to avoid potential performance issues. The main purposes of sharding columns are as follows: Load balancing: When concurrent writes are possible at the target, sharding columns help determine the specific thread used for sending messages. Orderliness: To address the potential disorder caused by concurrent writes, data migration ensures that messages received by users are ordered when the values of the sharding columns are the same. Here, order refers to the sequence of changes (the execution order of DML operations on a column). In the Select Columns section, select the columns to synchronize. For more information, see Column filtering.
        Remove/Clear All	Data migration supports removing one or more temporarily selected objects to the target during data mapping. Remove a single migration object In the right-side list of the selection area, click the Remove icon next to the target object to remove it. Remove all migration objects In the right-side list of the selection area, click Clear All in the upper-right corner. In the dialog box, click OK to remove all migration objects.

      • Select Match by Rule. For details, see Configure database-to-database matching rules.

5. Click Next. On the Migration Options page, configure the parameters for incremental synchronization.

   • Full migration

     The following parameters are displayed only if you have selected Full Migration in the Select Type & Objects step.

     Parameter	Description
     Read Concurrency Configuration	This parameter specifies the number of concurrent reads from the source during full migration. The maximum limit is 512. A high concurrency may cause excessive load on the source and affect business operations.
     Write Concurrency Configuration	This parameter specifies the number of concurrent writes to the target during full migration. The maximum limit is 512. A high concurrency may cause excessive load on the target and affect business operations.
     Limit Full Migration Rate	You can decide whether to enable the full migration rate limit based on your actual needs. If enabled, set the Source Read RPS (the maximum number of rows that can be read from the source per second during full migration), Source Read BPS (the maximum amount of traffic that can be read from the source per second during full migration), Target Write RPS (the maximum number of rows that can be written to the target per second during full migration), and Target Write BPS (the maximum amount of traffic that can be written to the target per second during full migration). Note The RPS and BPS set here are only for rate limiting. The actual achievable performance during full migration is subject to factors such as the source, target, and instance specifications.

   • Incremental synchronization

     The following parameters are displayed only if you have selected Incremental Synchronization in the Select Type & Objects step.

     Parameter	Description
     Write Concurrency Configuration	This parameter specifies the number of concurrent writes to the target during incremental synchronization. The maximum limit is 512. A high concurrency may cause excessive load on the target and affect business operations.
     Limit Incremental Migration Rate	You can decide whether to enable the incremental synchronization rate limit based on your actual needs. If enabled, set the Target Write RPS (the maximum number of data rows that can be synchronized to the target per second during incremental synchronization) and Target Write BPS (the maximum amount of traffic that can be synchronized to the target per second during incremental synchronization). Note The RPS and BPS set here are only for rate limiting. The actual achievable performance during incremental synchronization is subject to factors such as the source, target, and instance specifications.
     Incremental Synchronization Start Timestamp	If Full Migration was selected as the migration type, this parameter is not displayed. If Full Migration was not selected but Incremental Synchronization was selected, specify a point in time after which data should be migrated. The default is the current system time. For details, see Set an incremental synchronization timestamp.

   • Advanced options

     Parameter	Description
     Serialization Method	Controls the message format for synchronizing data to DataHub. Currently supported formats include Default, Canal, DataWorks (V2.0), SharePlex, DefaultExtendColumnType, Debezium, DebeziumFlatten, DebeziumSmt, and DTSCompatible. For details, see Data format description. Note This parameter is displayed only on the Select Synchronization Objects page when the topic type is set to BLOB, or when the source is an OceanBase Database MySQL-compatible tenant and the topic type is set to Tuple. Currently, only OceanBase Database MySQL-compatible tenants support Debezium, DebeziumFlatten, DebeziumSmt, and DTSCompatible. When the source is an OceanBase Database MySQL-compatible tenant and the topic type is set to Tuple on the Select Synchronization Objects page, the serialization method supports Default and DTSCompatible.
     Partitioning Rule	The rule for synchronizing source data to a DataHub Topic. Currently supported values are Hash and Table. We recommend that you select Table to ensure that downstream consumers do not encounter inconsistencies between DDL and DML consumption. Hash indicates that data migration uses a specific hash algorithm to select a partition of the DataHub Topic based on the primary key value or sharding column value. Table indicates that data migration delivers all data from a table to the same partition, using the table name as the hash key. Note If Synchronize DDL is selected on the Select Synchronization Type page, the partitioning rule only supports Table.

     The following table describes how DDL statements are delivered in different scenarios.

     Partitioning Rule	DDL statement involves multiple tables (e.g., RENAME TABLE)	DDL statement cannot identify related tables (e.g., DROP INDEX)	DDL statement involves a single table
     Hash	The DDL statement is delivered to all partitions of the Topic where the related tables are located. For example, if a DDL statement involves tables A, B, and C, and A is in Topic 1, B is in Topic 2, and C is not in this task, the DDL statement is delivered to all partitions under Topic 1 and Topic 2.	The DDL statement is delivered to all partitions of all Topics in this task. For example, if a DDL statement cannot be recognized by data migration, and this task has three Topics, the DDL statement is delivered to all partitions of these three Topics.	The DDL statement is delivered to all partitions under the Topic to which the table belongs.
     Table	The DDL statement is delivered to the partition where the hash value of the corresponding table name is located, under the Topic where the related table is located. For example, if a DDL statement involves tables A, B, and C, and A is in Topic 1, B is in Topic 2, and C is not in this task, the DDL statement is delivered to the partitions where the hash values of the related tables are located, under Topic 1 and Topic 2.	The DDL statement is delivered to all partitions of all Topics in this task. For example, if a DDL statement cannot be recognized by data migration, and this task has three Topics, the DDL statement is delivered to all partitions of these three Topics.	The DDL statement is hashed based on the Table Name and delivered to a specific partition within the Topic to which the table belongs.

6. Click Pre-check to have the system perform a precheck on the data migration task.

   During the Pre-check phase, data migration checks whether the database user's read/write permissions, network connectivity, and other requirements are met. The data migration task can only be started after all checks pass. If the precheck reports an error:

   • You can troubleshoot and resolve the issue, then re-execute the precheck until it succeeds.

   • You can also click Skip in the Actions column of the failed precheck item. A dialog box will pop up, explaining the specific impact of skipping this operation. After confirming that you can skip it, click OK in the dialog box.

7. After the precheck passes, click Purchase to go to the Purchase Data Migration Instance page for purchase.

   After a successful purchase, you can start the data migration task. For more information, see Purchase a data migration instance. If you do not need to purchase a data migration instance immediately, click Save to go to the details page of the data migration task. You can purchase it manually later as needed.

   You can click Configure Validation Task in the upper-right corner of the data migration details page to compare the data differences between the source and target databases. For more information, see Create a data verification task.

   The data migration service supports modifying migration objects during the execution of a data migration task. For more information, see View and modify migration objects. After a data migration task is started, it is executed in sequence according to the selected migration type. For more information, see the "View migration details" section in View the details of a data migration task.