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

Software	Version
Partner Product	Looker 22.2.24 On-Premises Looker 22.2.17 SaaS
Looker On-Premises Platform	Red Hat Enterprise Linux Server release 7.9 (Maipo)
Vertica Client	JDBC 9.1.1 bundled with Vertica dialect in Looker
Vertica Server	Vertica Analytic Database 11.1
Vertica Server On-Premises Platform	Red Hat Enterprise Linux Server release 7.9 (Maipo)

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.

Installing 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.

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.

Vertica 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.

Connecting Vertica to Looker

Follow these steps to create a connection to Vertica for both SaaS and On-premises deployment:

1. Open Looker in your browser using your Looker URL.

2. In the left pane of the home screen, click Admin.
3. Expand Database > Connections > Add Connection.

4. Enter the connection information for the following fields:

   • Name: Name of the Vertica connection.
   • Dialect: From the drop-down list, select Vertica.

   • Remote 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): Default schema name for tables you want to explore in Looker when no schema name is defined on querying tables.

   • Persistent Derived Tables (optional): Check the box to specify a database connection to store Persistent Derived Tables. When checked, specify the connection information: Remote 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.

   • Additional Params (optional): Use this field to enable additional database settings. For more information, see Setting 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): In SQL Runner, when selecting a connection and a schema and before selecting a table, Looker caches metadata information such as column names. This flag is selected by default.

   • Fetch Information Schema For SQL Writing: Looker caches schema information in the catalog to use for SQL optimization. This flag is not 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.

   • Test as User: This feature is obsolete.

   

5. Click Test These Settings to verify the connection to Vertica.
6. Click Add Connection to save the connection.

Setting Additional Parameters

After you create a connection to Vertica, you can enable the following additional database settings:

• 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 image:

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

Known Limitations

• For BINARY, LONGVARBINARY, and VARBINARY data types, an invalid byte sequence error is displayed in LookML projects.

• INTERVAL HOUR TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH data types are not displayed correctly by Looker SQL Runner.

• For TIME and TIMETZ data types, milliseconds are displayed up to 3 digits in SQL Runner. In a LookML project, for TIME, TIMETZ, TIMESTAMP and TIMESTAMPTZ data types, milliseconds are truncated in both default viewer and downloaded reports.

• DECIMAL data type is displayed up to 16 digits of precision.
• For TIMETZ and TIMESTAMPTZ data types, time zone offset is not displayed correctly in an on-premises deployment.

For More Information

• Looker website
• Looker Documentation
• Vertica Integration with Looker: Tips and Techniques
• Vertica QuickStart for Vertica
• Vertica Community Edition
• Vertica User Community
• Vertica Documentation