Vertica Database Account
Overview
You can use this account type to connect all Snaps in the Vertica Snap Pack with Vertica database using JDBC.
Account settings
- Expression icon (
): Allows using
JavaScript syntax to access SnapLogic Expressions to set field values dynamically (if
enabled). If disabled, you can provide a static value. Learn more. - SnapGPT (
): Generates SnapLogic Expressions
based on natural language using SnapGPT. Learn
more. - Suggestion icon (
): Populates a
list of values dynamically based on your Snap configuration. You can select only one
attribute at a time using the icon. Type into the field if it supports a comma-separated
list of values. - Upload
: Uploads files. Learn more.
| Field/Field set | Description |
|---|---|
| Label
String |
Required. Specify a unique label for the account. Default value: N/A Example: Vertica Database Account |
| Account properties | |
| Hostname
String |
Required. Specify the server's address to connect to. Default value: N/A Example: vertica.example.com |
| Port Number
Integer |
Required. Specify the database server's port to connect to. Default value: 3306 Example: 5433 |
| Database name
String |
Required. Specify the database name to connect to. Default value: N/A Example: mydb |
| Username
String |
Specify the username that is allowed to connect to the database. The username will be used as the default username when retrieving connections. The username must be valid in order to set up the data source. Default value: N/A Example: dbuser |
| Password
String |
Specify the password used to connect to the data source. The password will be used as the default password when retrieving connections. The password must be valid in order to set up the data source. Default value: N/A Example: Encrypted |
| JDBC jars
String |
Select the JDBC driver to use. A different driver binary for a driver must have a different name; the same name cannot be reused for a different driver. If this property is left blank, a default JDBC driver will be loaded. Default value: shared/Vertica-6.1.0.jar Example: shared/Vertica-6.1.0.jar |
| JDBC Driver Class
String |
Required. Specify the JDBC driver class name to use. Default value: com.vertica.jdbc.Driver Example: com.vertica.jdbc.Driver |
| Advanced properties | |
| Auto commit
Checkbox |
Select this checkbox to commit each batch immediately after it is executed. If the Snap fails, only the batch being executed at that moment is rolled back. Deselect this checkbox to commit the Snap execution output only after all batches are executed. If the Snap fails, the entire transaction is rolled back, unless the Snap finds invalid input data before it sends the insert request to the server, and routes the error documents to the Error view. Default value: Selected |
| Batch size
Integer |
Required. Specify the number of statements to execute at a time. Note: Select queries are not batched.
Warning: Using a large batch size could use up the JDBC placeholder limit of 2100.
Default value: 50 Example: 100 |
| Fetch size
Integer |
Required. Specify the number of rows to fetch at a time when executing a query. Warning: Large values could cause the server to run out of memory.
Default value: 100 Example: 200 |
| Min pool size
Integer |
Required. Specify the minimum number of connections a pool will maintain at a time. Default value: 3 Example: 5 |
| Max pool size
Integer |
Required. Specify the maximum number of connections a pool will maintain at a time. Default value: 50 Example: 100 |
| Max life time
Integer |
Required. Specify the maximum lifetime of a connection in the pool in minutes. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed. Default value: 30 Example: 60 |
| Idle Timeout
Integer |
Required. Specify the maximum amount of time in minutes a connection is allowed to sit idle in the pool. A value of 0 indicates that idle connections are never removed from the pool. Default value: 5 Example: 10 |
| Checkout timeout
Integer |
Required. Specify the number of milliseconds to wait for a connection to be available when the pool is exhausted. Zero waits forever. An exception will be thrown after the wait time has expired. Important: If you provide 0, the Snap waits infinitely until the connection is available. Therefore, we recommend you not to specify 0 for Checkout Timeout.
Default value: 10000 Example: 15000 |
| URL properties | Use this field set to add custom URL properties to the JDBC connection string. |
| URL property name
|
Specify the name of the URL property. Default value: N/A Example: ssl |
| URL property value
|
Specify the value of the URL property. Default value: N/A Example: true |
Auto Commit with Execute Snaps
For a DB Execute Snap, assume that a stream of documents enters the input view of the Snap and the SQL statement property has JSON paths in the WHERE clause. If the number of documents are large, the Snap executes in more than one batches rather than executing one per each document. Each batch would contain a certain number of WHERE clause values. If Auto commit is turned on, a failure would only roll back the records in the current batch. If Auto commit is turned off, the entire operation would be rolled back. For a single execute statement (with no input view), the setting has no practical effect.