This topic describes how to use the data migration service to migrate data from OceanBase Database (including MySQL and Oracle compatibility modes) to DataHub.
Notice
If a data migration task remains inactive for an extended period (with a status of Failed, Paused, or Completed), it may become unrecoverable due to factors such as the retention period for incremental logs. The data migration service proactively releases tasks that have been inactive for more than 7 days to reclaim resources. We recommend that you configure alerts for your tasks and promptly handle any related exceptions.
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_tmpare 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.
TypeDescriptionValue rangeBIGINT 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
Cloud Vendor |
Source |
Destination |
|---|---|---|
| Alibaba Cloud | OceanBase MySQL Compatible (Transactional) | Cloud DataHub instance |
| Alibaba Cloud | OceanBase MySQL Compatible (Analytical) | Cloud DataHub instance |
| Alibaba Cloud | OceanBase MySQL Compatible (Flagship Edition) | Cloud DataHub instance |
| Alibaba Cloud | OceanBase Oracle Compatible (Transactional) | Cloud DataHub instance |
| Alibaba Cloud | OceanBase Oracle Compatible (Flagship Edition) | Cloud DataHub instance |
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 DatabaseDefault mapped-to data type in DataHubBIT 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 DatabaseDefault mapped-to data type in DataHubCHAR 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
OceanBase Database Oracle-compatible tenant |
Default data hub type |
|---|---|
| CHAR | STRING |
| NCHAR | STRING |
| VARCHAR2 | STRING |
| NVARCHAR2 | STRING |
| CLOB | STRING |
| BLOB | STRING (Base64-encoded) |
| NUMBER | DECIMAL |
| BINARY_FLOAT | DECIMAL |
| BINARY_DOUBLE | DECIMAL |
| DATE | STRING |
| TIMESTAMP | STRING |
| TIMESTAMP WITH TIME ZONE | STRING |
| TIMESTAMP WITH LOCAL TIME ZONE | STRING |
| INTERVAL YEAR TO MONTH | STRING |
| INTERVAL DAY TO SECOND | STRING |
| RAW | STRING (Base64-encoded) |
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.
Note
This section applies to topics of the Tuple type.
Name |
Type |
Meaning |
|---|---|---|
| oms_timestamp | STRING | The time when the change occurred. |
| oms_table_name | STRING | Name of the changed source table. |
| oms_database_name | STRING | Name of the changed source database. |
| oms_sequence | STRING | The timestamp when data is synchronized to the process memory consists of a time value and a five-digit incremental number. Clock rollback can lead to data inconsistency. |
| oms_record_type | STRING | The type of change, including UPDATE, INSERT, and DELETE. |
| oms_is_before | STRING | For UPDATE operations, Y indicates that this row contains the data before the change. |
| oms_is_after | STRING | For UPDATE operations, Y indicates that this record contains the data after the change. |
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.
Field Name |
Data type |
Description |
|---|---|---|
| dts_record_id | STRING | The record ID of an incremental log is its unique identifier. Typically, the record ID is auto-incrementing, but during disaster recovery migration, clock skew between different machines may cause inconsistencies. |
| dts_operation_flag | STRING | Operation type. Valid values:
|
| dts_db_name | STRING | The name of the database. In OceanBase Database, the format is tenant name.database name. |
| dts_table_name | STRING | The name of the table. |
| dts_utc_timestamp | STRING | The operation timestamp, which is the Binlog timestamp (in UTC time). |
| dts_before_flag | STRING | Whether the values of all columns are the same as before the update. Valid values: Y and N. |
| dts_after_flag | STRING | Whether the values of all columns are the updated values. Valid values: Y and N. |
Procedure
Create a data migration task.
Log in to the OceanBase Cloud console.
In the left-side navigation pane, click Data Services > Data Migration.
On the Data Migration page, click the Data Migration tab.
On the Data Migration tab, click Create Task in the upper-right corner.
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.
On the Configure Source & Target page, configure the parameters.
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.
ParameterDescriptionCloud 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. 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.
ParameterDescriptionCloud 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.
Click Test and Continue.
On the Select Type & Objects page, configure the parameters.
In the Migration Type section, select the migration type for the current data migration task.
ParameterDescriptionSchema 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.
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.
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.
Select the objects to be migrated on the left side of the selection area.
Click >.
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.
ParameterDescriptionCreate 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.
ActionDescriptionImport 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 WHEREconditions, 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
WHEREcondition 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.
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.
ParameterDescriptionRead 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.
ParameterDescriptionWrite 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
ParameterDescriptionSerialization 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 RuleDDL statement involves multiple tables (e.g.,RENAME TABLE)DDL statement cannot identify related tables (e.g.,DROP INDEX)DDL statement involves a single tableHash 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.
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.
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.