Vertica is a column-oriented database that uses the massively parallel processing (MPP) architecture. Vertica Writer writes data to tables that are stored in Vertica databases. This topic describes the working principles and parameters that are supported by Vertica Writer and how to configure Vertica Writer by using the codeless user interface (UI) and code editor.

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

How it works

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

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

Vertica Writer obtains data from a reader and generates an SQL 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 primary Vertica databases.
    Note A synchronization node that uses Vertica 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.
  • Vertica Writer does not support the writeMode parameter.
  • Vertica Writer connects to a Vertica database by using a Vertica JDBC driver. Make sure that the driver version is compatible with your Vertica database. Vertica Writer uses the Vertica JDBC driver of the following version:
    <dependency>
        <groupId>com.vertica</groupId>
        <artifactId>vertica-jdbc</artifactId>
        <version>7.1.2</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 add data sources by using the code editor. Yes No default value
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 that has multiple primary databases.
  • The format of the value specified for this parameter must comply 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 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"]. 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 Vertica 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 1,024

Configure Vertica Writer by using the codeless UI

This method is not supported.

Configure Vertica Writer by using the code editor

In the following code, a synchronization node is configured to write data to a Vertica 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",
    "version":"2.0",// The version number. 
    "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"vertica",// The writer type. 
            "parameter":{
                "datasource": "The name of the data source",
                "username": "",
                "password": "",
                "column": [// The names of the columns to which you want to write data. 
                    "id",
                    "name"
                ],
                "connection": [
                    {
                        "table": [// The name of the table to which you want to write data. 
                            "vertica_table"
                        ],
                        "jdbcUrl": "jdbc:vertica://ip:port/database"
                    }
                ],
                "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"
                ]
            },
            "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"
            }
        ]
    }
}