Share this article:

Vertica Integration with Mode: Connection Guide

 

Applies to Vertica 7.2.x and earlier 

 

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, other versions may not have been tested. This document provides guidance using the latest versions of Vertica and Mode as of June, 2016.  

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 on top of the results, and share insights across the organization. Mode is a single platform that provides query, reporting, and collaboration tools.

This document is based on results from testing Mode with Vertica 7.2.3.

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 7.1 driver.

Get Started with Mode

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

Connect Mode to Vertica

Follow the steps below to connect Mode to your Vertica database and begin exploring your data. 

  1. Sign in to Mode here: https://modeanalytics.com/signin
  2. Click the blue icon in the upper right corner and click Connect a Database. The Connect a Private Database screen appears.

 connectadatabase.png

    1. Click Database on a Private Network.
    2. Click Vertica.

VerticaMode.PNG

  1. If you already have a client machine running a bridge connector, select a bridge. You can also install a new bridge by clicking connect a new bridge. In the following example there are two client machines already running a bridge:

ModeBridge.png

  1. After selecting an existing bridge, the credentials screen appears. Enter your database connection information to connect to Vertica using this bridge.
  2. If you have not installed a bridge connector before, the installation screen appears. Select the version of your client operating system to download the bridge connector.

ModeBridgeInstall.png

  1. Follow the Bridge Connector Setup Wizard instructions.
  2. After installing the bridge connector, the bridge connects to Mode. The credentials screen appears. Enter the information for the following fields:
      • 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 and Password: The username and password provided by the database administrator
  1. If your database does not support transport encryption or you cannot enable it, disable encryption between Mode and your Vertica database. To disable, click turn off transport encryption.

ModeCredentials.PNG

  1. Click Connect.

After creating a connection, a new data source should be listed in the data sources for your organization.

To verify your connection:  

  1. Click the blue icon in the upper=right corner and click Settings. The Account Settings screen appears.
  2. Click Data Sources. The Data Sources screen appears. Your new data source should be listed here.

Bridge Connection from Mode to Vertica

How the Bridge Connection Works

Mode uses a bridge connector to connect to your Vertica database in the cloud or in a private network. Currently, a direct connection without a bridge is not available. 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 version 7.1 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 restart the bridge service, run the bridge command with –replace. The following example includes soft return characters for readibility:

& 'C:\Program Files\Mode Analytics\Bridge Connector\mode-bridge-config.exe' -init 'M2E1ZjBlZGI4MDR
jOmYwN2E1OTI3Nzk4NjgyOTZjNzJjNjgyZEBodHRwczovL21vZGVhbmFseXRpY3MuY29
tL2FwaS9ocGV2ZXJ0aWNhL2JyaWRnZXMvc2V0dXA=' –replace


For example:

ModeBridgeRestart.png

 

Vertica on premises supports transport encryption, but it is disabled by default. To configure transport encryption or SSL connections on your Vertica database, follow the instructions in TLS/SSL Client Authentication.

Mode Logs

If you have installed the bridge connector on a Windows machine, the bridge log is located here:

C:\Program Files\Mode Analytics\Bridge Connector\bridge.log

To look at the product log on Windows, use the Windows Event Log.

About Mode Queries and Behavior

Mode does not use connection pooling to send queries to your Vertica database. Each query creates a new connection, which is closed when the query ends.

Mode labels queries with a JSON blob similar to the one below:

{ "user": "@Name (Name Lastname)",
"url" : "http://modeanalytics.com/modeanalytics/reports/"}

You can use these labels to identify the queries that Mode generates.

When writing a query in the Mode query editor, use aliases for the columns in the SQL statements.

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 Configuring SSL for JDBC Clients in the Vertica documentation.
  2. Disable transport encryption in Mode. On the credentials screen, below Connect, click turn off transport encryption, as shown in the graphic below. This disables the need for SSL between the bridge server and the database.

 modeturnoffencryption.png

Error: Connection to a Vertica version prior to 7.1 is not supported

Could not verify connection: java.sql.SQLNonTransientConnectionException: [Vertica]
[VJDBC](5273) FATAL: Unsupported frontend protocol 3.5: server supports 3.0 to 3.4

Solutions:

  1. Upgrade to Vertica version 7.1 or 7.2.
  2. Contact Mode Analytics support to let them know you are using a Vertica version prior to 7.1.

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 the SSL Overview in the Vertica documentation.
  2. Turn SSL security off in Mode. On the credentials screen, below Connect, click “turn off transport encryption”. This disables the need for SSL between the bridge server and the database.

Data Type Limitations

  • Mode does not display milliseconds for Timestamp and Time datatypes.
  • Mode does not display time zone offset for TimestampT and TimeTz.
  • Mode does not support interval data types.
  • The maximum number of digits for numeric values is 16.
  • Mode displays some numeric values in scientific notation. For example:
    • 0335 is displayed as 7.922816251426434e+24
    • -79228162514264337593543950335 is displayed as 922816251426434e+28
  • Mode truncates digits to the right of the decimal point. For example:
    • 9228162514264337593543950335 is displayed as 7.9228162514264335

Share this article: