ApsaraDB for OceanBase is a financial-grade distributed relational database that is developed by Alibaba Cloud and Ant Financial. This topic describes the limits and parameters of ApsaraDB for OceanBase Writer and how to configure it by using the code editor.

Notice ApsaraDB for OceanBase Writer supports only exclusive resource groups for Data Integration, but not the default resource group or custom resource groups. For more information, see Use exclusive resource groups for data integration and Add a custom resource group.

Background information

ApsaraDB for OceanBase implements automated and non-disruptive disaster recovery across cities based on the Five Data Centers Across Three Regions solution. It provides high availability for financial services based on conventional hardware. ApsaraDB for OceanBase is a Chinese database that provides the online scaling capability. It has undergone strict verification in terms of functionality, stability, scalability, and performance.

ApsaraDB for OceanBase Writer is designed for extract, transform, load (ETL) developers to import data from data warehouses to ApsaraDB for OceanBase databases. Users such as database administrators can use ApsaraDB for OceanBase Writer as a data migration tool.

ApsaraDB for OceanBase Writer obtains data from a Data Integration reader, and generates an INSERT INTO statement based on your configurations.

Limits

  • INSERT INTO: If a primary key conflict or unique index conflict occurs, data cannot be written to the conflicting rows. ApsaraDB for OceanBase in Oracle mode supports only the write mode of INSERT INTO.
  • ON DUPLICATE KEY UPDATE: If no primary key conflict or unique index conflict occurs, the action is the same as that of INSERT INTO. If a conflict occurs, specified fields in original rows are updated. ApsaraDB for OceanBase in MySQL mode supports both the write modes of INSERT INTO and ON DUPLICATE KEY UPDATE.
  • ApsaraDB for OceanBase Writer can write data to tables only in the primary ApsaraDB for OceanBase database.
    Note A sync node that uses ApsaraDB for OceanBase Writer must have at least the permission to execute the INSERT INTO statement. Whether other permissions are required depends on the SQL statements that you specify in the preSql and postSql parameters when you configure the node.
  • We recommend that you write data to the destination table in batch mode. In this mode, 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 modes. Make sure that the SQL statements that you specify in the preSql and postSql parameters comply with the SQL syntax constraints on the corresponding mode. Otherwise, the SQL statements may fail to be executed.
  • ApsaraDB for OceanBase Writer connects to an ApsaraDB for OceanBase database by using the OceanBase database driver. You must confirm the compatibility between the driver version and your ApsaraDB for OceanBase database. ApsaraDB for OceanBase Writer uses the following version of the OceanBase database driver:
    <dependency>
        <groupId>com.alipay.OceanBase</groupId>
        <artifactId>OceanBase-connector-java</artifactId>
        <version>3.1.0</version>
    </dependency>

Parameters

Parameter Description Required Default value
datasource The connection name. It must be the same as the name of the ApsaraDB for OceanBase connection that you added in DataWorks.

You can connect to the ApsaraDB for OceanBase database based on the settings of the jdbcUrl or username parameter.

No N/A
jdbcUrl The Java Database Connectivity (JDBC) URL of the ApsaraDB for OceanBase database. The jdbcUrl parameter must be included in the connection parameter.
  • You can configure only one JDBC URL for a database. ApsaraDB for OceanBase Writer cannot write data to a database with multiple primary databases.
  • The format must be in accordance with ApsaraDB for OceanBase official specifications. You can also specify the information of the attachment facility. Example: jdbc:mysql://127.0.0.1:3306/database.
Yes N/A
username The username that you can use to connect to the database. Yes N/A
password The password that you can use to connect to the database. Yes N/A
table The name of the destination table, which is described in a JSON array.
Note The table parameter must be included in the connection parameter.
Yes N/A
column The columns in the destination table to which data is written. Separate the columns with a comma (,), for example, "column":["id","name","age"].
Note The column parameter cannot be left empty.
Yes N/A
writeMode The write mode. Valid values: insert into and on duplicate key update. Yes N/A
preSql The SQL statement to execute before the sync node is run. Use @table to specify the name of the destination table in the SQL statement. When you execute this SQL statement, DataWorks replaces @table with the name of the destination table. No N/A
postSql The SQL statement to execute after the sync node is run. No N/A
batchSize The number of data records to write at a time. Set this parameter to a proper value based on your actual needs. This can greatly reduce the interactions between Data Integration and the ApsaraDB for OceanBase database on the network, and increase the throughput.
Note A value greater than 2048 may lead to the out of memory (OOM) error during the data synchronization process.
No 1024

Configure ApsaraDB for OceanBase Writer by using the codeless UI

The codeless user interface (UI) is not supported for ApsaraDB for OceanBase Writer.

Configure ApsaraDB for OceanBase Writer by using the code editor

You can configure ApsaraDB for OceanBase Writer by using the code editor. For more information, see Create a sync node by using the code editor.

The following example shows how to configure a sync node to write data to an ApsaraDB for OceanBase database:
{
    "type":"job",
    "version":"2.0",// The version number.
    "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"apsaradb_for_OceanBase",// The writer type.
            "parameter":{
                "datasource": "The connection name.",
                "column": [// The columns to which data is written.
                    "id",
                    "name"
                ],
                "table": "apsaradb_for_OceanBase_table",// The name of the destination table.
                "preSql": [ // The SQL statement to execute before the sync node is run.
                    "delete from @table where db_id = -1"
                ],
                "postSql": [// The SQL statement to execute after the sync node 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":false,// Specifies whether to enable bandwidth throttling. A value of false indicates that the bandwidth is not throttled. A value of true indicates that the bandwidth is throttled. The maximum transmission rate takes effect only if you set this parameter to true.
            "concurrent":1 // The maximum number of concurrent threads.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}