Vertica Integration with Mode: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

Vertica and Mode: Latest Versions Tested

Mode Overview

Mode is a web-based business intelligence solution for SQL-based data exploration and collaboration. Mode enables analysts to write SQL, create dashboards, and share insights across the organization.

Mode connects to your Vertica database in the cloud or on premises using a proprietary bridge connector. You do not need to install the Vertica client. By default, Mode comes with the Vertica JDBC driver.

Get Started with Mode

To get started with Mode Analytics, create an account here, https://modeanalytics.com/signup.

Connecting Mode to Vertica

To connect Mode to your Vertica database and begin exploring your data

1. Sign into Mode here, https://modeanalytics.com/signin
2. Click the arrow next to your account in the upper left corner.
   
   
3. From the menu, click Connect a Database. The Connect a database screen appears.

 

4. In the Connect a database screen, select Vertica from the database drop-down list.



5. If you have not installed a Bridge Connector before, the Install the Bridge connector screen appears. From the client machine where Bridge connector will be installed, perform the following two steps:
   

• Choose your Operating System: Select the operating system of the client machine where Bridge Connector will be installed.

• Install: Follow the instructions displayed for your platform.

  

6. After installing and configuring the Bridge Connector, the credentials screen appears and indicates the Bridge Connector that will be used to connect to Vertica. Enter your connection information:

• Display name: The name you will use to refer to this connection.
• Host: Vertica server name or IP address.
• Port: The default port is 5433.
• Database name: Your Vertica database name.
• Username: The username to connect to Vertica.
• Password: The password to connect to Vertica.

• Custom Trusted SSL CA certificate: Your Vertica database public SSL certificate for SSL verification. This information is optional.

• Preferred JDBC Driver: Select the recommended JDBC version from the drop-down list.

• Use transport encryption for this connection (recommended): Use TLS/SSL to secure the connection between the Bridge Connector and your Vertica database. This option is recommended.

  

11. Click Connect.

After creating the connection, the new connection will be listed under Workspace settings > Manage connections, and you can begin exploring your data.

Bridge Connection from Mode to Vertica

How the Bridge Connection Works

Mode uses a bridge connector to connect to your Vertica database hosted in the cloud or in a private network. Mode’s bridge connector is an L4 Proxy that makes a single long-lived TCP connection to Mode.

When connecting to Vertica from Mode for the first time, you must install the bridge connector on a client machine that is always up and running. This machine does not need to be the one you are using to access Mode via the browser. The machine running the bridge must allow connections to outbound ports 8444 and 443.

You do not need to install a Vertica driver. The Vertica JDBC driver is run internally by Mode in a service known as Data Source connector.

Connections between the bridge connector and your database are encrypted to keep data secure. Verify that your Vertica database has SSL configured and does not provide self-signed certificates (certificates that were signed by an unknown Certificate Authority). By default, SSL is not configured in your Vertica database.

To configure transport encryption or SSL connections on your Vertica database, follow the instructions in TLS Overview.

Troubleshooting

You may see the following error messages when you create a connection to Vertica using Mode’s bridge connector.

Error: SSL connections are not configured in your Vertica database.

Could not verify connection: java.sql.SQLException: [Vertica][VJDBC](100063) SSL negotiation failed.

Solutions:

1. Enable SSL authentication in Vertica. To enable SSL, follow the instructions for TLS Overview Clients in the Vertica documentation.
2. Disable transport encryption in Mode. On the credentials screen, uncheck Use transport encryption for this connection. This disables the need for SSL between the bridge server and the database.

Error: The Vertica instance you connected to provided a certificate that was signed by an unknown Certificate Authority. Vertica is using self-signed certificates.

Could not verify connection: java.sql.SQLRecoverableException: [Vertica][VJDBC]
(100024) IOException while communicating with server: javax.net.ssl.SSLHandshakeException: 
sun.security.validator.ValidatorException: PKIX path building failed: 
sun.security.provider.certpath.SunCertPathBuilderException: 
unable to find valid certification path to requested target.

Solutions:

1. Verify that your Vertica database uses non-self-signed SSL certificates. For more information, see Generating TLS Certificates and Keys in the Vertica documentation.
2. Turn SSL security off in Mode. On the credentials screen, uncheck Use transport encryption for this connection. This disables the need for SSL between the bridge server and the database.

Known Limitations

• For TIMESTAMP, TIMESTAMPTZ, TIME, and TIMETZ data types, milliseconds are not displayed.
• For TIMESTAMPTZ and TIMETZ data types, time zone offset is not displayed.

• For NUMERIC data type, precision of up to 17 digits and scale of 4 digits is displayed above which values are rounded off.

• For FLOAT data type, precision of up to 15 digits and scale of 4 digits is displayed above which values are rounded off.

For More Information

• Mode website
• Vertica Community Edition
• Vertica User Community
• Vertica Documentation