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

Notice Currently, ApsaraDB for OceanBase Writer does not support the default resource group. Use exclusive resource groups for data integration or custom resource groups. For more information, see Use exclusive resource groups for data integration and Add a custom resource group.

Background

ApsaraDB for OceanBase implements automated and non-disruptive disaster recovery across cities with the Five Data Centers in Three Regions solution. It provides high availability for financial services through conventional hardware. With the online scaling capability, ApsaraDB for OceanBase is a Chinese database that 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. ApsaraDB for OceanBase Writer can also be used as a data migration tool by users such as database administrators (DBAs).

ApsaraDB for OceanBase Writer obtains data from a Data Integration reader, and generates the 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 Writer in MySQL mode supports both the write modes of INSERT INTO and ON DUPLICATE KEY UPDATE.
  • Data can be written only to tables stored in the primary ApsaraDB for OceanBase database.
    Note A sync node that uses ApsaraDB for OceanBase Writer must have at least the permission to run the INSERT INTO statement. Whether other permissions are required depends on the SQL statements specified 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 Oracle and MySQL modes. When you configure the preSql and postSql parameters, make sure that the SQL statements specified in the parameters comply with the SQL syntax constraints on the corresponding mode. Otherwise, the SQL statements may fail to be run.
  • ApsaraDB for OceanBase Writer accesses an ApsaraDB for OceanBase database through the OceanBase driver. 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 identical to 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 and username parameters.

No None
jdbcUrl The Java Database Connectivity (JDBC) URL for connecting to the ApsaraDB for OceanBase database. You do not need to set this parameter because the system automatically obtains the value from 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 None
username The username for connecting to the database. Yes None
password The password for connecting to the database. Yes None
table The names of the destination tables, which are described in a JSON array.
Note You do not need to set this parameter because the system automatically obtains the value from the connection parameter.
Yes None
column The columns in the destination table to which data is written. Separate the columns with a comma (,). Example: "column": ["id", "name", "age"].
Note The column parameter cannot be left empty.
Yes None
writeMode The write mode. Valid values: insert into and on duplicate key update. Yes None
preSql The SQL statement to run before the sync node is run. Use @table to specify the name of the table to be modified in the SQL statement. When running this SQL statement, DataWorks replaces @table with the name of the target table. No None
postSql The SQL statement to run after the sync node is run. No None
batchSize The number of data records to write at a time. Setting this parameter can greatly reduce the interactions between Data Integration and the ApsaraDB for OceanBase database over the network, and increase the throughput.
Note A value larger 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

Currently, the codeless user interface (UI) is not supported for ApsaraDB for OceanBase Writer.

Configure ApsaraDB for OceanBase Writer by using the code editor

In the following code, a node is configured 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 run before the sync node is run.
                    "delete from @table where db_id = -1"
                ],
                "postSql": [// The SQL statement to run 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"
            }
        ]
    }
}