DataWorks:ApsaraDB for OceanBase data source

Supported ApsaraDB for OceanBase versions

Batch data read and write

ApsaraDB for OceanBase Reader and ApsaraDB for OceanBase Writer connect to ApsaraDB for OceanBase databases by using an OceanBase database driver. Make sure that the driver version is compatible with the version of your ApsaraDB for OceanBase database. ApsaraDB for OceanBase Reader and ApsaraDB for OceanBase Writer use the OceanBase database driver of the following version:

<dependency>
    <groupId>com.alipay.OceanBase</groupId>
    <artifactId>OceanBase-connector-java</artifactId>
    <version>3.1.0</version>
</dependency>

Limits

Batch data read

• ApsaraDB for OceanBase supports the Oracle and MySQL tenant modes. Make sure that the WHERE clause and the columns that you specify in the column parameter comply with the SQL syntax constraints that Oracle or MySQL supports. Otherwise, the SQL statement may fail to be executed.

• Data of views can be read.

• When you use a batch synchronization task to read data from an ApsaraDB for OceanBase data source, do not modify the data that is being synchronized. Otherwise, data quality issues, such as data duplication or data loss, may occur.

Batch data write

• INSERT INTO: If a primary key conflict or unique index conflict occurs, data cannot be written to conflicting rows. In Oracle tenant mode, ApsaraDB for OceanBase supports only the INSERT INTO write mode.

• INSERT INTO...ON DUPLICATE KEY UPDATE: If no primary key conflict or unique index conflict occurs, data is processed in the same way as that in INSERT INTO mode. If a primary key conflict or unique index conflict occurs, the specified fields in the rows in the destination table are updated. In MySQL tenant mode, ApsaraDB for OceanBase supports the INSERT INTO and INSERT INTO...ON DUPLICATE KEY UPDATE write modes.

• Data can be written to tables stored only in the primary database.

  Note

  A synchronization task that uses ApsaraDB for OceanBase Writer must have at least the permissions to execute INSERT INTO. Whether other permissions are required depends on the SQL statements that you specify in the preSql and postSql parameters when you configure the task.

• We recommend that you write data to the destination table in batches. ApsaraDB for OceanBase Writer submits a write request when the number of rows reaches a specific threshold.

• ApsaraDB for OceanBase supports the Oracle and MySQL tenant modes. Make sure that the SQL statements that you specify in the preSql and postSql parameters comply with the related SQL syntax. Otherwise, the SQL statements may fail to be executed.

Real-time data read

ApsaraDB for OceanBase is a distributed relational database that can integrate data distributed in multiple physical databases into a unified logical database. However, you can synchronize data of only one physical AnalyticDB for MySQL to an AnalyticDB for MySQL cluster in real time.

Prepare an ApsaraDB for OceanBase environment before data synchronization

Before you use DataWorks to synchronize data from or to an ApsaraDB for OceanBase data source, you must prepare an ApsaraDB for OceanBase environment. This ensures that a data synchronization task can be configured and can synchronize data from or to the ApsaraDB for OceanBase data source as expected. The following information describes how to prepare an ApsaraDB for OceanBase environment for data synchronization from or to an ApsaraDB for OceanBase data source.

Preparation 1: Configure an IP address whitelist

Add the elastic IP address (EIP) of the exclusive resource group for Data Integration and the CIDR block of the vSwitch with which the exclusive resource group for Data Integration is associated to the whitelist of the ApsaraDB for OceanBase data source. To view and add the EIP and CIDR block to the whitelist, perform the following steps:

1. View and record the EIP and CIDR block of the exclusive resource group for Data Integration.

   1. Log on to the DataWorks console.

   2. In the left-side navigation pane, click Resource Groups.

   3. On the Exclusive Resource Groups tab, find the exclusive resource group for Data Integration and click View Information in the Actions column.

   4. In the Exclusive Resource Groups dialog box, view and record the values of the EIPAddress and CIDR Blocks parameters.

   5. On the Exclusive Resource Groups tab, find the exclusive resource group for Data Integration and click Network Settings in the Actions column.

   6. On the VPC Binding tab of the page that appears, view and record the CIDR block of the vSwitch with which the exclusive resource group for Data Integration is associated.

2. Add the EIP of the exclusive resource group for Data Integration and the CIDR block of the vSwitch with which the exclusive resource group for Data Integration is associated to the whitelist of the ApsaraDB for OceanBase data source. For more information, see Configure a whitelist.

Preparation 2: Prepare an account that has the required permissions

You must create an account to log on to the database of the ApsaraDB for OceanBase cluster. You must grant the required permissions to the account. For more information, see Create an account.

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a data synchronization task, see the following sections. For information about the parameter settings, view the infotip of each parameter on the configuration tab of the task.

Add a data source

Before you configure a data synchronization task to synchronize data from or to a specific data source, you must add the data source to DataWorks. For more information, see Add and manage data sources.

Configure a batch synchronization task to synchronize data of a single table

• For more information about the configuration procedure, see Configure a batch synchronization task by using the codeless UI and Configure a batch synchronization task by using the code editor.

• For information about all parameters that are configured and the code that is run when you use the code editor to configure a batch synchronization task, see Appendix: Code and parameters.

Configure a real-time synchronization task to synchronize data of a database

For more information about the configuration procedure, see Configure a real-time synchronization task in DataStudio.

Configure synchronization settings to implement (real-time) synchronization of full and incremental data in a single table or a database

For more information about the configuration procedure, see Configure a synchronization task in Data Integration.

Appendix: Code and parameters

Appendix: Configure a batch synchronization task by using the code editor

If you use the code editor to configure a batch synchronization task, you must configure parameters for the reader and writer of the related data source based on the format requirements in the code editor. For more information about the format requirements, see Configure a batch synchronization task by using the code editor. The following information describes the configuration details of parameters for the reader and writer in the code editor.

Code for ApsaraDB for OceanBase Reader

{
    "type": "job",
    "steps": [
        {
            "stepType": "apsaradb_for_OceanBase", // The plug-in name.
            "parameter": {
                "datasource": "", // The name of the data source.
                "where": "",
                "column": [ // The names of 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 maximum number of dirty data records allowed.
        },
        "speed": {
            "throttle": true, // Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent": 1, // The maximum number of parallel threads.
            "mbps":"12"// The maximum transmission rate. Unit: MB/s. 
        }
    }
}

Parameters in code for ApsaraDB for OceanBase Reader

Code for ApsaraDB for OceanBase Writer

{
    "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": "The name of the data source",
                "column": [// The names of the columns. 
                    "id",
                    "name"
                ],
                "table": "apsaradb_for_OceanBase_table",// The name of the table. 
                "preSql": [ // The SQL statement that you want to execute before the synchronization task is run. 
                    "delete from @table where db_id = -1"
                ],
                "postSql": [// The SQL statement that you want to execute after the synchronization task is run. 
                    "update @table set db_modify_time = now() where db_id = 1"
                ],
                "writeMode": "insert",
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed. 
        },
        "speed":{
            "throttle":true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent":1, // The maximum number of parallel threads. 
            "mbps":"12"// The maximum transmission rate. Unit: MB/s. 
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Parameters in code for ApsaraDB for OceanBase Writer