|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
Looker 7.10.21 On-Premises
Looker 7.10.14 SaaS
|Looker On-Premises Platform||
CentOS Linux release 7.4.1708 (Core)
JDBC 9.1.1 bundled with dialect Vertica 7.1+ in Looker
|Vertica Server||Vertica Database 10.0|
|Vertica Server On-Premises Platform||CentOS Linux release 7.4.1708 (Core)|
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.
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:
- Navigate to the Looker website.
- Click Request a demo.
- Provide your contact information to request a demo or free trial of Looker.
- Click Submit.
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.
- 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.
Connecting Vertica to Looker
Follow these steps to create a connection to Vertica:
Open Looker in your browser using your Looker URL. For example:
- On the toolbar menu, click Admin > Connection > New Connection.
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): 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: Host, Port, Database, Username, Password, Schema and Temp Database. The Temp Database is a dedicated schema in the database to store Persistent Derived Tables.
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.
- Click Test These Settings to verify the connection to Vertica.
- Click Add Connection to save the connection.
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
- 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.
Looker's Vertica 7.1+ dialect does not connect to Vertica Server 7.0.x and earlier versions
Issue: Looker has implemented two dialect options to connect to Vertica. 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:
Java::JavaSql::SQLNonTransientConnectionException: [Vertica][VJDBC] (5273) FATAL: Unsupported frontend protocol 3.5: server supports 3.0 to 3.4
Solution: Connect using the Vertica 6 dialect or upgrade to a more recent version of Vertica.
- BINARY, LONGVARBINARY, and VARBINARY data types are not supported in LookML projects. You must use explicit conversion to display BINARY values or you will receive an invalid byte sequence error.
INTERVAL HOUR TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH data types are not supported by Looker SQL Runner.
For TIME and TIMETZ data types, milliseconds are supported up to 3 digits in SQL runner viewer. In a LookML project, milliseconds are truncated in both default viewer and downloaded reports.
- For TIMESTAMP and TIMESTAMPTZ data types, milliseconds are truncated in both default viewer and in downloaded reports for LookML project.
- TIMETZ and TIMESTAMPTZ data types, time zone offset is not supported in an on-premises deployment.
DECIMAL data type supports up to 16 digits of precision in Looker SaaS deployment and 17 digits of precision in an on-premises deployment.