Share this article:

Vertica Integration with Looker: 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 Looker: Latest Versions Tested

This document is based on the results of our testing of Looker 6.14.13 on-premises and Looker 6.14.17 SaaS with Vertica Server 9.2.1.

Software Version
Partner Product

Looker 6.14.13 on-premises

Looker 6.14.17 SaaS

Looker On-Premises Platform

CentOS Linux release 7.4.1708 (Core)

Vertica Client

JDBC 9.1.1 bundled with dialect Vertica 7.1+ in Looker

Vertica Server Vertica Database 9.2.1
Vertica Server On-Premises Platform CentOS Linux release 7.4.1708 (Core)

Looker Overview

Looker is a business intelligence tool that facilitates exploring, modeling, and analyzing data. Looker is a web-based application and integrates with GitHub for version control.

Looker uses a live connection to your Vertica database using JDBC. The data remains in Vertica and is not copied to the looker instance.

For details, visit the Looker website.

Get Started With Looker

Looker can be deployed on Linux operating systems in the cloud or on-premises. Most Looker deployments are hosted and 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 a Demo.
  3. Provide your contact information to request a demo or free trial of Looker.
  4. Click Submit.

Note

Looker releases a new version approximately each month. If you need to upgrade your on-premises deployment 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 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.1+. The Vertica 6 dialect allows users to connect to Vertica Server 6.x or later. The Vertica 7.1+ dialect allows users to connect to Vertica 7.1.x or later. If you need to connect to Vertica Server version 6.0 or 7.0, use Vertica 6 dialect.

Vertica 7.1+ dialect is bundled with Vertica’s JDBC driver version 9.1.1, which is both backwards and forward compatible. Backward compatibility to earlier versions of Vertica Server starts with version 7.1.

Note  

  • If you are using a more recent version of Vertica Server (later than 9.1.1) and you wish to enable specific features introduced with that version, you should request Looker Support to install the latest Vertica JDBC driver on your Looker instance.
  • For more information, see Client Driver and Server Version Compatibility in the Vertica documentation.

Connect Looker to Vertica

Follow these steps to create a connection to Vertica:

  1. Open Looker in your browser using your Looker URL. For example:

    https://<my_looker_instance>:9999
  2. On the left panel, click Admin > Connection > New Connection.
  3. Enter the connection information for the following fields:

    • Name: Identify the connection to your Vertica database.
    • Dialect: From the drop-down list, select either Vertica 6 or Vertica 7.1+. Select Vertica 6 to connect to Vertica Server 6.x or later; select Vertica 7.1+ to connect to Vertica Server 7.1.x or later.
    • Host:Port: Vertica server name or IP address and port. The default port is 5433.

    • Database: Your Vertica database name
    • Username: Enter the user name provided by the database administrator.
    • Password (optional): Enter the password provided by the database administrator.
    • Schema (optional): Contains the tables you want to explore in Looker.
    • Persistent Derived Tables (optional): Check the box to specify a database connection to store Persistent Derived Tables. When checked, specify the connection information: Host, Port, Database, Username, Password, Schema and Temp Database. The Temp Database is a dedicated schema in the database to store Persistent Derived Tables.

      Note

      Vertica does not recommend using Looker’s Persistent Derived Tables (PDT). Instead, use Looker’s Ephemeral Derived Tables (EDT). If the performance of the Ephemeral Derived Tables becomes too slow over time, use Vertica tuning techniques to optimize the query before considering creating a Persistent Derived Table.

    • Temp Database: This option is available only when the Persistent Derived Tables option is checked.

    • Additional Params (optional): Use this field to enable additional database settings. For more information, see Set Additional Parameters in this document.
    • PDT And Datagroup Maintenance Schedule (optional): Provide a Cron expression for Looker to check how often it should check for data groups and Persistent Derived Tables to be regenerated or dropped. Default is */5****, which means to check every 5 minutes.
    • Max Connections (optional): Connection pool size. Set to the default value of 30.
    • Connection Pool Timeout (optional): Time in seconds after which a connection request times out. Default value is 120.
    • SQL Runner Precache (optional): Check this box to pre-cache tables for faster lookup. Selected by default.
    • Database Time Zone (optional): Specify the time zone used by your Vertica database.
    • Query Time Zone (optional): Set the time zone for your queries. This option is only available in an on-premises deployment of Looker.

  4. Click Test These Settings to verify the connection to Vertica.
  5. Click Add 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 using 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:

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

Known Limitations

  • The Vertica 7.1+ dialect does not connect to Vertica Server 7.0.x and earlier versions. The connection fails and returns the following error:

    Cannot connect: connection refused: Java::JavaSql::SQLNonTransientConnectionException: [Vertica][VJDBC](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, or VARBINARY data types. You must use explicit conversion to display BINARY values or you will receive an invalid byte sequence error.
  • Looker SQL Runner does not support YearMonthInterval, HourSecondInterval, HourMinuteInterval, and DaySecondInterval data types.

  • Looker displays NULL Boolean values as “∅”.
  • Looker displays NULL numeric values as “∅”.
  • Looker truncates the milliseconds for TimestampTz, TimeTz, TimeStamp and Time data types.
  • Looker does not support Timezone values for TimeTz and TimestampTz data types in an on-premises deployment.
  • Looker rounds off large integer values to 16 digits of precision. For example: 9223372036854775807 is displayed as 9223372036854776000.
  • Looker truncates and rounds decimal values to 2 digits of scale. For example: 79228162514264.337593543950335 is displayed as 79228162514264.34.

For More Information

Share this article: