ApsaraDB for OceanBase is a financial-grade distributed relational database that is developed by Alibaba Cloud and Ant Financial. This topic describes the parameters that are supported by ApsaraDB for OceanBase Writer and how to configure ApsaraDB for OceanBase Writer by using the codeless user interface (UI) and code editor.

Notice ApsaraDB for OceanBase Writer supports only exclusive resource groups for Data Integration, but not the shared resource group or custom resource groups for Data Integration. For more information, see Create and use an exclusive resource group for Data Integration and Create a custom resource group for Data Integration.

Background information

ApsaraDB for OceanBase supports automated and non-disruptive disaster recovery across cities based on the Five Data Centers Across Three Regions solution. ApsaraDB for OceanBase provides high availability for financial services based on conventional hardware. ApsaraDB for OceanBase is a database service developed by Alibaba Cloud. It provides the online scaling capability and 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 in 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.

ApsaraDB for OceanBase Writer obtains data from a reader and generates an SQL statement based on your configurations.

Limits

  • insert into: If no primary key conflict or unique index conflict occurs, data is directly written to the destination table. If a primary key conflict or unique index conflict occurs, data cannot be written to the 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 conflict occurs, the specified fields in the rows in the destination table are updated. In MySQL tenant mode, ApsaraDB for OceanBase supports both the insert into and insert into...ON DUPLICATE KEY UPDATE write modes.
  • ApsaraDB for OceanBase Writer can write data to tables only in the primary ApsaraDB for OceanBase database.
    Note A synchronization node that uses ApsaraDB for OceanBase Writer must have at least the permissions 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 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.
  • ApsaraDB for OceanBase Writer connects to an ApsaraDB for OceanBase database by using an OceanBase database driver. Make sure that the driver version is compatible with your ApsaraDB for OceanBase database. ApsaraDB for OceanBase Writer uses 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>

Parameters

Parameter Description Required Default value
datasource The name of the data source. It must be the same as the name of the added data source.

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

No No default value
jdbcUrl The Java Database Connectivity (JDBC) URL of the ApsaraDB for OceanBase database. This parameter is 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 that has multiple primary databases.
  • The format of the value specified for this parameter must comply with the official specifications of ApsaraDB for OceanBase. You can also specify the information of the attachment facility. Example: jdbc:mysql://127.0.0.1:3306/database.
Yes No default value
username The username that you use to connect to the database. Yes No default value
password The password that you use to connect to the database. Yes No default value
table The name of the table to which you want to write data. Specify the name in a JSON array.
Note The table parameter must be included in the connection parameter.
Yes No default value
column The names of the columns to which you want to write data. Separate the names with commas (,), such as "column": ["id", "name", "age"].
Note The column parameter cannot be left empty.
Yes No default value
writeMode The write mode. Valid values: insert into and insert into...ON DUPLICATE KEY UPDATE. Yes No default value
preSql The SQL statement that you want to execute before the synchronization node is run. Use @table to indicate the name of the destination table in the SQL statement. When you execute this SQL statement, @table is replaced by the name of the destination table. No No default value
postSql The SQL statement that you want to execute after the synchronization node is run. No No default value
batchSize The number of data records to write at a time. Set this parameter to an appropriate value based on your business requirements. This greatly reduces the interactions between Data Integration and the database and increases throughput.
Note If you set this parameter to a value greater than 2048, an out of memory (OOM) error may occur during data synchronization.
No 1,024

Configure ApsaraDB for OceanBase Writer by using the codeless UI

This method is not supported.

Configure ApsaraDB for OceanBase Writer by using the code editor

For more information about how to configure a synchronization node by using the code editor, see Create a sync node by using the code editor.

In the following code, a synchronization 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 name of the data source",
                "column": [// The names of the columns to which you want to write data. 
                    "id",
                    "name"
                ],
                "table": "apsaradb_for_OceanBase_table",// The name of the table to which you want to write data. 
                "preSql": [ // The SQL statement that you want to execute before the synchronization node is run. 
                    "delete from @table where db_id = -1"
                ],
                "postSql": [// The SQL statement that you want to execute after the synchronization 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":true,// Specifies whether to enable bandwidth throttling. The value false indicates that bandwidth throttling is disabled, and the value true indicates that bandwidth 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.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}