Support for ApsaraDB for OceanBase data synchronization capabilities - DataWorks

Supported versions

ApsaraDB for OceanBase Reader and ApsaraDB for OceanBase Writer support the following OceanBase versions for batch read and write operations:

OceanBase 2.x
OceanBase 3.x
OceanBase 4.x

The preceding versions all refer to ApsaraDB for OceanBase, the Alibaba Cloud managed service. OceanBase Community Edition (CE) is not supported. To synchronize data from a self-managed OceanBase Community Edition instance, first use Data Transmission Service (DTS) to migrate the data to ApsaraDB RDS for MySQL, and then use DataWorks to synchronize the data to AnalyticDB for MySQL.

Limitations

Batch read

ApsaraDB for OceanBase supports Oracle and MySQL tenant modes. When you configure the where clause for data filtering or function columns in the column parameter, ensure that the syntax complies with the SQL constraints of the corresponding tenant mode. Otherwise, the SQL statement may fail.
You can read data from a view.
During a batch read, do not modify the data being synchronized. This prevents data quality issues, such as data duplication or loss.
If you configure the data source for Read by Partition, the account used to access the data source requires system permissions.

Batch write

Note

The synchronization task requires at least insert into... permissions. Other permissions may be required depending on the statements you specify in the preSql and postSql parameters.

We recommend using the batch method to write data. This method sends a write request only when the number of accumulated rows reaches a predefined threshold.
ApsaraDB for OceanBase supports Oracle and MySQL tenant modes. When you configure the preSql and postSql parameters, ensure that the syntax complies with the SQL constraints of the corresponding tenant mode. Otherwise, the SQL statement may fail.

Real-time read

This feature supports only the OceanBase MySQL tenant mode.
To synchronize real-time data, you must enable the binlog feature. For more information, see Binlog-related operations (Alibaba Cloud instances), Binlog-related operations (OB Cloud instances).
Real-time full database synchronization tasks do not support data sources in connection string mode.
For real-time full database synchronization tasks, the database version must be V3.0 or later.
OceanBase is a distributed relational database that can integrate data from multiple physically distributed databases into a single logical database. However, real-time synchronization of OceanBase data to AnalyticDB for MySQL currently supports only data from a single physical database. Synchronizing data from a logical database is not supported.

Preparations before data synchronization

Before you synchronize data in DataWorks, prepare the ApsaraDB for OceanBase environment as described in this topic. This ensures that ApsaraDB for OceanBase data synchronization tasks can be properly configured and run in DataWorks. The following sections describe the required preparations.

Configure an allowlist

Add the VPC CIDR block of the Serverless resource group or exclusive resource group for Data Integration to the OceanBase allowlist. For more information, see Add allowlist entries.

Create an account and configure permissions

You must create a database account for subsequent operations. This account must have the required permissions on OceanBase. For more information, see Create an account and configure permissions.

Add a data source

Before you develop a synchronization task in DataWorks, you must add the required data source to DataWorks by following the instructions in Data source configuration. You can view parameter descriptions in the DataWorks console to understand the meanings of the parameters when you add a data source.

Develop data synchronization tasks

For information about the entry point for and the procedure of configuring a synchronization task, see the following configuration guides.

Single-table batch synchronization

Supported sources: All data source types supported by the Data Integration module
Configuration guide: Configure a batch synchronization task

Single-table real-time synchronization

Supported sources: Kafka
Configuration guide: Configure a real-time synchronization task

Full database real-time synchronization

Supported sources: MySQL
Configuration guide: Configure a real-time synchronization task

Appendix: Script demos and parameter descriptions

Configure a batch synchronization task by using the code editor

If you want to configure a batch synchronization task by using the code editor, you must configure the related parameters in the script based on the unified script format requirements. For more information, see Script mode configuration. The following information describes the parameters that you must configure for data sources when you configure a batch synchronization task by using the code editor.

Reader script demo

{
    "type": "job",
    "steps": [
        {
            "stepType": "apsaradb_for_OceanBase", // The plug-in name.
            "parameter": {
                "datasource": "", // The data source name.
                "where": "",
                "column": [ // The columns.
                    "id",
                    "name"
                ],
                "splitPk": ""
            },
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "stream",
            "parameter": {
                "print": false,
                "fieldDelimiter": ","
            },
            "name": "Writer",
            "category": "writer"
        }
    ],
    "version": "2.0",
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    },
    "setting": {
        "errorLimit": {
            "record": "0" // The error count.
        },
        "speed": {
            "throttle": true, // Specifies whether to enable throttling. A value of false indicates that throttling is disabled and the mbps parameter does not take effect. A value of true indicates that throttling is enabled.
            "concurrent": 1, // The concurrency.
            "mbps":"12" // The throttling rate. 1 mbps = 1 MB/s.
        }
    }
}

Reader script parameters

Parameter	Description	Required	Default value
datasource	If the DataWorks edition you use supports adding ApsaraDB for OceanBase data sources, you can reference an added ApsaraDB for OceanBase data source by its name. Two configuration methods are available: jdbcUrl and username.	Yes	N/A
jdbcUrl	The JDBC connection information of the destination database. Use a JSON array for the description. You can specify multiple connection addresses for a single database. If multiple addresses are configured, ApsaraDB for OceanBase Reader probes the connectivity of each IP address in sequence until a valid one is found. If all connections fail, ApsaraDB for OceanBase Reader reports an error. Note jdbcUrl must be included in the connection configuration unit. Based on the official ApsaraDB for OceanBase specification, jdbcUrl can include additional connection control information. For example, `jdbc:oceanbase://127.0.0.1:3306/database`. You must use either this parameter or username, but not both.	No	N/A
username	The username of the data source.	No	N/A
password	The password of the specified username for the data source.	No	N/A
table	The tables to be synchronized. Use a JSON array for the description. You can read data from multiple tables at the same time. When multiple tables are configured, ensure that all tables have the same schema. ApsaraDB for OceanBase Reader does not verify schema consistency across tables. Note table must be included in the connection configuration unit.	Yes	N/A
column	The columns to be synchronized from the configured tables. Use a JSON array to describe the column information. By default, all columns are used, for example, [*]. Column pruning is supported: you can export only specific columns. Column reordering is supported: you can export columns in an order different from the table schema. Constant configuration is supported. For example, `'123'`. Function columns are supported. For example, `date('now')`. column must explicitly specify the set of columns to be synchronized and cannot be left empty.	Yes	N/A
splitPk	If you specify splitPk when ApsaraDB for OceanBase Reader extracts data, data is split based on the column specified by splitPk. The data synchronization system then starts concurrent tasks to improve synchronization efficiency. We recommend that you set splitPk to the primary key of the table. The primary key is generally evenly distributed, which helps avoid data hotspots across shards. Currently, splitPk supports only integer-type splitting. String, floating-point, date, and other types are not supported. If you specify an unsupported type, ApsaraDB for OceanBase Reader reports an error. If splitPk is left empty, the system considers that you do not want to split a single table. In this case, a single channel is used for data extraction.	No	Empty
where	ApsaraDB for OceanBase Reader assembles an SQL statement based on the specified column, table, and where parameters, and then uses the assembled SQL statement to extract data. For example, during testing, you can set the where condition to limit 10. In actual business scenarios, you typically synchronize data generated on the current day by setting the where condition to `gmt_create>$bizdate`. The where condition can be used for incremental data synchronization. If the where condition is not configured or left empty, full table data is synchronized.	No	N/A
querySql	In some business scenarios, the where parameter may not be sufficient to describe the required filter conditions. You can use this parameter to define a custom filter SQL statement. When this parameter is configured, the data synchronization system ignores the tables, columns, and splitPk parameters, and uses the configured SQL statement to filter data. When you configure querySql, ApsaraDB for OceanBase Reader ignores the table, column, where, and splitPk parameters.	No	N/A
fetchSize	This parameter specifies the number of rows fetched per batch between the plug-in and the database server. This value determines the number of network interactions between the data synchronization system and the server, and can significantly improve data extraction performance. Note A fetchSize value that is too large (>2048) may cause an out-of-memory (OOM) error in the data synchronization process.	No	1,024

Writer script demo

{
    "type":"job",
    "version":"2.0", // The version number.
    "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"apsaradb_for_OceanBase", // The plug-in name.
            "parameter":{
                "datasource": "Data source name",
                "column": [ // The columns.
                    "id",
                    "name"
                ],
                "table": "apsaradb_for_OceanBase_table", // The table name.
                "preSql": [ // The SQL statements to execute before the data synchronization task runs.
                    "delete from @table where db_id = -1"
                ],
                "postSql": [ // The SQL statements to execute after the data synchronization task runs.
                    "update @table set db_modify_time = now() where db_id = 1"
                ],
                "obWriteMode": "insert",
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0" // The error count.
        },
        "speed":{
            "throttle":true, // Specifies whether to enable throttling. A value of false indicates that throttling is disabled and the mbps parameter does not take effect. A value of true indicates that throttling is enabled.
            "concurrent":1, // The concurrency.
            "mbps":"12" // The throttling rate. 1 mbps = 1 MB/s.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Writer script parameters

Parameter	Description	Required	Default value
datasource	If the DataWorks edition you use supports adding ApsaraDB for OceanBase data sources, you can reference an added ApsaraDB for OceanBase data source by its name. Two configuration methods are available: jdbcUrl and username.	No	N/A
jdbcUrl	The JDBC connection information of the destination database. jdbcUrl is included in the connection configuration unit. You can configure only one value for a single database. Scenarios where the same database has multiple primary nodes (dual-primary import) are not supported. The format of jdbcUrl is consistent with the official ApsaraDB for OceanBase specification, and can include additional connection parameters. For example, `jdbc:oceanbase://127.0.0.1:3306/database`.	Yes	N/A
username	The username of the data source.	Yes	N/A
password	The password of the specified username for the data source.	Yes	N/A
table	The name of the table to which data is written. Use a JSON array for the description. Note table must be included in the connection configuration unit.	Yes	N/A
column	The columns in the destination table to which data is written. Separate column names with commas (,). For example, `"column": ["id", "name", "age"]`. Note The column parameter must be specified and cannot be left empty.	Yes	N/A
obWriteMode	The mode used to write data to the destination table. This parameter is optional. insert: `insert into ...` When a primary key or unique index conflict occurs, the conflicting rows cannot be written. update: `... on duplicate key update ...` Used in MySQL tenant mode. Conflicting rows are updated upon conflict. merge: `merge into ... matched then update ...` Used in Oracle tenant mode. Conflicting rows are updated upon conflict.	No	insert
onClauseColumns	Note Used in Oracle tenant mode. This parameter is required when `obWriteMode` is set to `merge`. If this parameter is not configured, data is written using `insert`. Set this parameter to primary key columns or unique constraint columns. Separate multiple columns with commas (,). For example, `ID,C1`.	No	N/A
obUpdateColumns	Note This parameter takes effect when `obWriteMode` is set to `merge` or `update`. The columns to be updated when a write conflict occurs. Separate multiple columns with commas (,). For example, `c2,c3`.	No	All columns
preSql	The standard SQL statements to execute before data is written to the destination table. If you need to reference the table name in the SQL statements, use `@table` as a placeholder. The system replaces this variable with the actual table name at runtime.	No	N/A
postSql	The standard SQL statements to execute after data is written to the destination table.	No	N/A
batchSize	The number of records to submit in each batch. This value can significantly reduce the number of network interactions between the data synchronization system and the server, and improve overall throughput. Note A fetchSize value that is too large (>2048) may cause an out-of-memory (OOM) error in the data synchronization process.	No	1,024