Share this article:

Vertica Integration with Looker: Connection Guide

 

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. but they may not have been tested. This document provides guidance using the latest versions of Vertica and Looker as of June, 2016.

Looker Overview

Looker is a business intelligence tool that facilitates exploring, modeling, and analyzing data stored in your Vertica database. Looker is a web-based application and integrates with GitHub for version control of your projects. Looker uses a JDBC connection to connect live to Vertica and is deployed on Linux operating systems either in the cloud or on premises.

This document is based on the results of testing Looker 3.34.15 with Vertica 7.1.x and 7.2.x on Linux in the cloud and on premises.  

Get Started With Looker

Looker can be deployed in the cloud or on premises. Most Looker deployments occur within the cloud and are managed by Looker. For more information about deployment management, see the Looker documentation.

To request a trial and demo of Looker, follow these steps:

  1. Navigate to the Looker website.
  2. Click Request Demo.
  3. Provide your contact information to request a demo or free trial of Looker.
  4. Click Request Demo.

Note Looker releases a new version approximately each month. If you need to upgrade to a new version of Looker, contact the Looker Support team for assistance.

Vertica Client Drivers

Looker connects to Vertica using the Vertica JDBC client driver. This driver is built into Looker and is not packaged as part of the Looker application. You do not need to download and install the Vertica JDBC client driver.

Looker provides two Vertica dialects: Vertica 6 and Vertica 7+. The Vertica 6 dialect allows users to connect to Vertica server 6.x or later. The Vertica 7+ dialect allows users to connect to Vertica 7.1.x or later. Note that the Vertica 7+ dialect does not support a connection to Vertica server 7.0.x. If you are using Vertica 7.0.x, connect to Vertica using the Vertica 6 dialect. If you need to leverage features introduced with Vertica 7.0, such as internal load balancing, then either upgrade your Vertica server version to 7.1.x or later, or request that Looker Support installs a compatible Vertica JDBC driver.

For more information, see Client Driver and Server Version Compatibility in the Vertica documentation. For updates about Vertica, see the release notes documentation.

Connect Looker to Vertica

Follow these steps to create a connection to Vertica:

  1. Open Looker on your browser using your Looker URL. For example: https://<my_looker_instance>:9999
  2. On the left panel, click Admin > Connection > New Database Connection.
  3. Enter the connection information for the following fields:
      • Name: Identify the connection to your Vertica database
      • Dialect: Select Vertica 6 for Vertica server 6.x or later or select Vertica 7+ for Vertica server 7.1.x or later from the drop-down menu.
      • Host: Vertica server name or IP address.
      • Vertica Port: The default is 5433.
      • Database: Your Vertica database name.
      • Username and Password: Enter the username and password provided by the database administrator.
      • Schema: Contains the tables you want to explore in Looker.
      • Temp Database (optional): This is a separate schema that stores persistent derived tables. Do not use Looker’s persistent derived tables.
      • Max Connections (optional): Connection pool size. Set to default value of 30.
      • Connection pool timeout (optional): Use default value of 120.
      • Database time zone (optional): Specify the time zone for your Vertica database.
      • Query time zone (optional): Set the time zone for your queries.

connection.png

  1. Optionally, configure additional database settings in the Additional Params field. For more information, see Set Additional Parameters in this document.
  2. Click Test These Settings to verify the connection to Vertica.
  3. Click Update Connection to save the connection.

Set Additional Parameters

When you create a connection to Vertica, you can enable additional database settings, such as the following:

  • Enable connection load balancing using the JDBC connection parameter ConnectionLoadBalance=1.
  • Assign a label to identify Looker’s session, use the JDBC connection parameter Label = <my_label>.

You can pass several parameters one after another, separated by an ampersand, as shown in the following graphic:

additionalparameters.png

For more information, see Additional JDBC Connection Settings in the Vertica documentation.

Known Limitations

  • The Vertica 7+ dialect does not connect to Vertica server 7.0.x. The connection fails and returns the following error:

Cannot connect: connection refused: java::javaSql:: SQLNonTransientConnectionException”[Vertica][V][JDBC](5273)FATAL:Unsupported frontend protocol 3.5:server supports 3.0 to 3.4

If you encounter this error, connect using the Vertica 6 dialect or upgrade to a later version of Vertica.

  • Looker does not support BINARY, LONGVARBINARY, and VARBINARY data types. You must use explicit conversion to display BINARY values or you will receive an invalid byte sequence error.
  • Looker does not support the Vertica YearMonthInterval, HourSecondInterval, HourMinuteInterval, and DaySecondInterval.
  • Looker displays null Boolean values as “No.”
  • Looker truncates long strings to 10,001 characters.
  • Looker does not display digits to the right of a decimal point. For example 1234.567 is displayed as 1234.
  • Looker displays NULL numeric values as 0.
  • Looker displays NAN, INFINITY, and –INFINITY values as NULL “∅”.

For More Information

For More Information About… See…

Looker

http://www.looker.com/product

Looker and Vertica

https://my.vertica.com/Using-Looker-to-Leverage-Vertica-Capabilities-ORC-Read...

Looker QuickStart

https://saas.hpe.com/marketplace/haven/hpe-vertica-quickstart-looker

Looker and Vertica Demo

https://verticademo.looker.com/spaces/8

Vertica Community Edition

https://my.vertica.com/community/

Vertica Documentation

https://my.vertica.com/documentation/vertica/

Big Data and Analytics Community

https://my.vertica.com/big-data-analytics-community-content/

Share this article: