Before you create a connection to Vertica, create a new database user and schema that is exclusive for your Looker applications. The Looker user needs read and write permissions into a separate schema to store PDTs and read-only privileges to other schemas in the Vertica database. This is optional but recommended.
The following is an example of creating a user and schema for Looker:
CREATE USER looker Identified BY 'mypassword'; CREATE SCHEMA looker_scratch; GRANT CREATE ON SCHEMA looker_scratch to looker;
Creating a connection to Vertica
In Looker, configure a database connection via the interface. From the Admin section, select Connections, and then click Add Connection. See the Connecting Looker to your database documentation page for more information.
Enter the connection information in the required fields:
- Name: Give a name to the connection. This is how the LookML model will reference the connection.
- Dialect: Select Vertica from the drop-down of dialects.
- Host: Enter the Vertica server name or IP.
- Port: The default is 5433.
- Database: Enter Vertica's database name.
- Username and Password: Enter the username and password of the user that will connect to Looker.
- Schema: Enter the schema that contains the tables that you want to explore in Looker.
- Temp Database: This is the scratch schema where you want Looker to create any temporal derived tables to improve performance. It is optional but recommended, and should be created beforehand.
- Max Connections: This is the optional connection pool size. Use the default value.
- Connection Pool Timeout: This is optional. Use the default value.
- Database Time Zone: The time zone your Vertica database uses to store dates and times. For example, UTC. This is optional.
- Query Time Zone: The time zone you want your queries to display. For example, US Eastern (America – New York). This is optional.
- Additional Params: This is optional. Use this field to enable additional database settings. For example, to enable Vertica's native load balancing, use the JDBC connection parameter
ConnectionLoadBalance=1. To assign a label to identify Looker's sessions, use the JDBC connection parameter
Label=<mylabel>. You can pass several parameters one after the other using
&, as shown on this page. For a complete list of available JDBC connection parameters, see Vertica's documentation.
Click on Test These Settings to verify the connection to Vertica is successful. The Testing database connectivity documentation page has troubleshooting information.
Click Update Connection to save the connection.
For Looker to support some features, your database dialect must also support them. In the latest release of Looker, Vertica supports the following Looker features:
After you have completed the database connection, configure authentication options.