This topic describes the data types and parameters that are supported by DB Writer and how to configure DB Writer by using the code editor.

Notice DB 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

DB Writer writes data to tables stored in primary databases. DB Writer obtains data from a reader, connects to a remote database by using Java Database Connectivity (JDBC), and then executes an INSERT INTO statement to write data to the database. DB Writer is commonly used to write data to relational databases. To enable DB Writer to write data to a relational database, you must register the driver for the relational database.

DB Writer is designed for extract, transform, load (ETL) developers to import data in data warehouses to relational databases. DB Writer can also be used as a data migration tool by users such as database administrators.

DB Writer supports most data types of common relational databases, such as numeric and string data types. Make sure that the data types of your database are supported.

Parameters

Parameter Description Required Default value
jdbcUrl The JDBC URL of the destination database. The format of the JDBC URL that you specify must follow the official specifications of the database. You can also specify the information of the attachment facility. The format varies based on the database type. Data Integration selects the most suitable driver based on the format and uses this driver to write data to the destination database.
  • Format for DM databases: jdbc:dm://IP address:Port number/database
  • Format for Db2 databases: jdbc:db2://IP address:Port number/database
  • Format for PPAS databases: jdbc:edb://IP address:Port number/database
 Yes No default value
username The username that is used to connect to the destination database. Yes No default value
password The password that is used to connect to the destination database. Yes No default value
table The name of the table to which you want to write data. If the table uses the default schema for the destination database, the value of this parameter consists of only the name of the table. If the table uses a custom schema, the value of this parameter consists of two parts: the name of the custom schema and the name of the table. Specify the two parts in the Schema name.Table name format. Yes No default value
column The names of the columns to which you want to write data. Separate the columns with commas (,).
Note We recommend that you do not leave this parameter empty.
Yes No default value
preSql The SQL statement that you want to execute before the synchronization node is run. For example, you can set this parameter to the SQL statement that is used to delete outdated data. You can execute only one SQL statement in a transaction.
Note If you specify multiple SQL statements, the statements are not executed in the same transaction.
No No default value
postSql The SQL statement that you want to execute after the synchronization node is run. For example, you can set this parameter to the SQL statement that is used to add a timestamp. You can execute only one SQL statement in a transaction.
Note If you specify multiple SQL statements, the statements are not executed in the same transaction.
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 destination database and increases throughput. If you set this parameter to an excessively large value, an out of memory (OOM) error may occur during data synchronization. No 1024

Configure DB Writer by using the code editor

In the following code, a synchronization node is configured to write data to a database. 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. 
{
    "type": "job",
    "steps": [
        {
            "stepType": "oracle",
            "parameter": {
                "datasource": "aaa",
                "column": [
                    "PROD_ID",
                  "name"
                ],
                "where": "",
                "splitPk": "",
                "encoding": "UTF-8",
                "table": "PENGXI.SALES"
            },
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "rdbms",
            "parameter": {
                "connection": [
                    {
                        "jdbcUrl": "jdbc:dm://ip:port/database",
                        "table": [
                            "table"
                        ]
                    }
                ],
                "username": "username",
                "password": "password",
                "table": "table",
                "column": [
                   "id",
                  "name"
                ],
                "preSql": [
                    "delete from XXX;"
                ]
            },
            "name": "Writer",
            "category": "writer"
        }
    ],
    "version": "2.0",
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    },
    "setting": {
        "errorLimit": {
            "record": ""
        },
        "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":2, // The maximum number of parallel threads. 
            "mbps":"12"// The maximum transmission rate.
        }
    }
}
To enable DB Writer to support a new type of database, perform the following steps:
  1. Go to the directory of DB Writer, which is ${DATAX_HOME}/plugin/writer/DB Writer. In the directory, ${DATAX_HOME} indicates the main directory of Data Integration.
  2. Open the plugin.json file in the directory of DB Writer and add the driver of the database to the drivers array in the file. During the running of a synchronization node, DB Writer automatically selects the most suitable driver to connect to the database. 
    {
    "name": "DB Writer",
    "class": "com.alibaba.datax.plugin.reader.DB Writer.DB Writer",
    "description": "useScene: prod. mechanism: Jdbc connection using the database, execute select sql, retrieve data from the ResultSet. warn: The more you know about the database, the less problems you encounter.",
    "developer": "alibaba",
    "drivers": [
        "dm.jdbc.driver.DmDriver",
        "com.ibm.db2.jcc.DB2Driver",
        "com.sybase.jdbc3.jdbc.SybDriver",
        "com.edb.Driver"
    ]
}
  3. Add the package of the driver to the libs directory in the directory of DB Writer. 
    $tree
.
|-- libs
|   |-- Dm7JdbcDriver16.jar
|   |-- commons-collections-3.0.jar
|   |-- commons-io-2.4.jar
|   |-- commons-lang3-3.3.2.jar
|   |-- commons-math3-3.1.1.jar
|   |-- datax-common-0.0.1-SNAPSHOT.jar
|   |-- datax-service-face-1.0.23-20160120.024328-1.jar
|   |-- db2jcc4.jar
|   |-- druid-1.0.15.jar
|   |-- edb-jdbc16.jar
|   |-- fastjson-1.1.46.sec01.jar
|   |-- guava-r05.jar
|   |-- hamcrest-core-1.3.jar
|   |-- jconn3-1.0.0-SNAPSHOT.jar
|   |-- logback-classic-1.0.13.jar
|   |-- logback-core-1.0.13.jar
|   |-- plugin-rdbms-util-0.0.1-SNAPSHOT.jar
|   `-- slf4j-api-1.7.10.jar
|-- plugin.json
|-- plugin_job_template.json
`-- DB Writer-0.0.1-SNAPSHOT.jar