Vertica is a column-oriented database that uses the massively parallel processing (MPP) architecture. Vertica Writer allows you to write data to tables that are stored in Vertica databases. This topic describes how Vertica Writer works, the parameters that Vertica Writer supports, and how to configure it by using the code editor.

Notice Vertica 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.

How it works

Vertica Writer connects to a remote Vertica database by using Java Database Connectivity (JDBC), and executes an INSERT INTO statement to write data to the Vertica database. Internally, data is submitted in batches to the Vertica database.

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

Vertica Writer obtains data from a Data Integration reader, and generates the INSERT INTO statement based on your configurations.
  • INSERT INTO: If a primary key conflict or unique index conflict occurs, data cannot be written to the conflicting rows.
  • Data can be written to tables that are stored only in the primary Vertica database.
    Note A sync node that uses Vertica 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.
  • Vertica Writer does not support the writeMode parameter.
  • Vertica Writer connects to a Vertica database by using the Vertica JDBC driver. You must confirm the compatibility between the driver version and your Vertica database. Vertica Writer uses the following version of the Vertica JDBC driver:
    <dependency>
        <groupId>com.vertica</groupId>
        <artifactId>vertica-jdbc</artifactId>
        <version>7.1.2</version>
    </dependency>

Parameters

Parameter Description Required Default value
datasource The connection name. It must be the same as the name of the added connection. You can add connections in the code editor. Yes N/A
jdbcUrl The JDBC URL of the Vertica database. The jdbcUrl parameter must be included in the connection parameter.
  • You can configure only one JDBC URL for a database. Vertica Writer cannot write data to a database with multiple primary databases.
  • The format must be in accordance with the official specifications of Vertica. You can also specify the information of the attachment facility. Example: jdbc:vertica://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 commas (,), for example, "column":["id","name","age"]. 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 Vertica database on the network, and increase the throughput. However, an excessively large value may lead to the out of memory (OOM) error during the data synchronization process. No 1024

Configure Vertica Writer by using the codeless UI

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

Configure Vertica Writer by using the code editor

The following example shows how to configure a sync node to write data to a Vertica database. For more information, see Create a sync node by using the code editor.
{
    "type":"job",
    "version":"2.0", // The version number.
     "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"vertica", // The writer type.
             "parameter":{
                "datasource": " ", // The connection name.
                "username": "",
                "password": "",
                "column": [ // The columns to which data is written.
                     "id",
                    "name"
                ],
                "connection": [
                    {
                        "table": [ // The name of the destination table.
                             "vertica_table"
                        ],
                        "jdbcUrl": "jdbc:vertica://ip:port/database"
                    }
                ],
                "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"
                ]
            },
            "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 when you set this parameter to true.
             "concurrent":1 // The maximum number of concurrent threads.
         }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}