Skip to main content
Feedback

Vertica connection

Vertica on Data Integration combines the advanced analytics capabilities of Vertica's data warehouse with the data integration and orchestration capabilities of Data Integration platform. This integration enables you to efficiently load, transform, and analyze data from multiple sources within Vertica, providing real-time insights and informed decision-making. With Data Integration features, organizations can streamline their data pipelines and leverage Vertica's speed and scalability to derive actionable intelligence from their data.

Vertica compatibility versions

River modesVersions supportedSupport for managing schema drift
Multi-Tables (Standard Extraction)All VersionsYes

Creating a Data Integration database user

To extract data, it is recommended to create a new user in the database for Data Integration:

CREATE USER [<Data_Integration Username>] IDENTIFIED BY '<myPassword>'

Permit the new user created to access the database:

GRANT USAGE ON SCHEMA '<database>' to [<Data_Integration Username>];

Additional permissions can be found here. We only require a grant of SELECT privileges, so GRANT VIEW is sufficient.

You can also explicitly give permission to one of your schemas: GRANT SELECT ON SCHEMA <database>.<schema> to [<Data_Integration Username>];

GRANT SELECT ON SCHEMA '<database>.<schema>' to [<Data_Integration Username>];

Connecting to Vertica in Data Integration

To connect to your Vertica, you must set up your database credentials.

Define your Vertica connection

  1. In Data Integration 's menu, Go to Connections >Create Connection, or create your new connection in the source, pressing Create New Connection in the Rivers wizard.
  2. In the pop-out window, define your credential details: It is advised to whitelist Data Integration IPs on your Database Firewall. Whitelist our IPs
  3. Define your Host, Port, Database, Username, and Password.

Connecting from Vertica

Using SSH Tunneling to connect your on-premise database server with Data Integration.

Using a hybrid method of data processing, Data Integration can connect to on-prem servers. We recommend the use of an SSH Tunneling Method.

To use the SSH Tunneling via Data Integration, configure your SSH Tunnel server in your network. This server will have the ability to get SSH connections from Data Integration IPs and connect to your database server via the internal IP or address. Data Integration connects to that SSH server using a private key.

After you've configured SSH tunneling, you can set it up in the Data Integration Connection: Under the SSH Options section, in the Vertica connection pop-up, you can define the following:

  • SSH Hostname
  • SSH Port (in most of the connection, that should be 22)
  • The Username Data Integration should connect via SSH to the server
  • Password (optional)
  • SSH Pem Key (Recommended)
  • And if you have also a Pem Password for that key file.
On this Page