Vertica data source - DataWorks - Alibaba Cloud Documentation Center

Vertica is a column-oriented database that uses the massively parallel processing (MPP) architecture. DataWorks provides Vertica Reader and Vertica Writer for you to read data from and write data to Vertica data sources. This topic describes the capabilities of synchronizing data from or to Vertica data sources.

Supported Vertica versions

Vertica Reader connects to a Vertica database by using the Vertica Java Database Connectivity (JDBC) driver. You must make sure that the driver version is compatible with your Vertica database. The Vertica JDBC driver of the following version is used:

<dependency>
<groupId>com.vertica</groupId>
<artifactId>vertica-jdbc</artifactId>
<version>7.1.2</version>
</dependency>

Limits

Vertica data sources support only exclusive resource groups for Data Integration.
Vertica Writer does not support the writeMode parameter.
You can use only the code editor to configure a synchronization task.

Data type mappings

Common Vertica data types, including the data types of the integer, floating point, string, and time categories, are supported. However, the support for some advanced data types is limited.

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a data synchronization task, see the following sections. For information about the parameter settings, view the infotip of each parameter on the configuration tab of the task.

Add a data source

Before you configure a data synchronization task to synchronize data from or to a specific data source, you must add the data source to DataWorks. For more information, see Add and manage data sources.

Configure a batch synchronization task to synchronize data of a single table

For information about the configuration procedure, see Configure a batch synchronization task by using the code editor.
For information about all parameters that are configured and the code that is run when you use the code editor to configure a batch synchronization task, see Appendix: Code and parameters.

Appendix: Code and parameters

Appendix: Configure a batch synchronization task by using the code editor

If you use the code editor to configure a batch synchronization task, you must configure parameters for the reader and writer of the related data source based on the format requirements in the code editor. For more information about the format requirements, see Configure a batch synchronization task by using the code editor. The following information describes the configuration details of parameters for the reader and writer in the code editor.

Code for Vertica Reader

{
"type": "job",
"steps": [
{
"stepType": "vertica", // The plug-in name. 
"parameter": {
"datasource": "", // The name of the data source. 
"where": "",
"column": [ // The names of the columns. 
"id",
"name"
],
"splitPk": "id",
"connection": [
{
"table": [ // The name of the table. 
"table"
]
}
]
},
"name": "Reader",
"category": "reader"
},
{
"stepType": "stream",
"parameter": {
"print": false,
"fieldDelimiter": ","
},
"name": "Writer",
"category": "writer"
}
],
"version": "2.0",
"order": {
"hops": [
{
"from": "Reader",
"to": "Writer"
}
]
},
"setting": {
"errorLimit": {
"record": "0" // The maximum number of dirty data records allowed. 
},
"speed": {
"throttle": true, // Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that 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. Unit: MB/s. 
}
}
}

Parameters in code for Vertica Reader

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
table	The name of the table from which you want to read data. Vertica Reader can read data from multiple tables. Specify the table names in a JSON array. If you specify multiple tables, you must make sure that the tables have the same schema. Vertica Reader does not check whether the tables have the same schema. Note The table parameter must be included in the connection parameter.	Yes	No default value
column	The names of the columns from which you want to read data. Specify the names in a JSON array. The default value is [ * ], which indicates all the columns in the source table. You can select specific columns to read. The column order can be changed. This indicates that you can specify columns in an order different from the order specified by the schema of the source table. Constants are supported. The column parameter must explicitly specify all the columns from which you want to read data. This parameter cannot be left empty.	Yes	No default value
splitPk	The field that is used for data sharding when Vertica Reader reads data. If you specify this parameter, the source table is sharded based on the value of this parameter. Data Integration then runs parallel threads to read data. This improves data synchronization efficiency. We recommend that you set the splitPk parameter to the name of the primary key column of the table. Data can be evenly distributed to different shards based on the primary key column, instead of being intensively distributed only to specific shards. The splitPk parameter supports sharding only for data of integer data types. If you set this parameter to a field of an unsupported data type, such as a string, floating point, or date data type, Vertica Reader returns an error. If the splitPk parameter is left empty, Vertica Reader uses a single thread to read data from the source table.	No	No default value
where	The WHERE clause. Vertica Reader generates an SQL statement based on the settings of the table, column, and where parameters and uses the generated statement to read data. For example, when you perform a test, you can configure the where parameter to filter data. In actual business scenarios, you can set the where parameter to `gmt_create > $bizdate` to read the data that is generated on the current day. You can use the WHERE clause to read incremental data. If the where parameter is not provided or is left empty, Vertica Reader reads all data.	No	No default value
querySql	The SQL statement that is used for refined data filtering. If you configure this parameter, data is filtered based only on the value of this parameter. If you configure the querySql parameter, Vertica Reader ignores the settings of the table, column, and where parameters.	No	No default value
fetchSize	The number of data records to read at a time. This parameter determines the number of interactions between Data Integration and the database and affects read efficiency. 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

Code for Vertica Writer

{
"type":"job",
"version":"2.0",// The version number. 
"steps":[
{
"stepType":"stream",
"parameter":{},
"name":"Reader",
"category":"reader"
},
{
"stepType":"vertica", // The plug-in name. 
"parameter":{
"datasource": "The name of the data source",
"column": [// The names of the columns. 
"id",
"name"
],
"connection": [
{
"table": [// The name of the table. 
"vertica_table"
]
}
],
"preSql": [ // The SQL statement that you want to execute before the synchronization task is run. 
"delete from @table where db_id = -1"
],
"postSql": [// The SQL statement that you want to execute after the synchronization task 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 throttling. The value false indicates that throttling is disabled, and the value true indicates that 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. Unit: MB/s. 
}
},
"order":{
"hops":[
{
"from":"Reader",
"to":"Writer"
}
]
}
}

Parameters in code for Vertica Writer

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 value format of the jdbcUrl parameter must follow official Vertica specifications. You can also specify additional JDBC connection properties in the URL. 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 task 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 task 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 OOM error may occur during data synchronization.	No	1,024