Before you create a connection to Vertica, create a new database user and schema that will be exclusive for your Looker applications. Looker has the option to create persistent derived tables to improve performance and needs read and write permissions into a separate schema to store these derived tables permanently and read-only privileges to other schemas in the Vertica database. This is optional but recommended.
More information here: http://www.looker.com/docs/setup-and-management/database-config.
This is an example to create a user and schema for Looker:
CREATE USER looker Identified BY 'mypassword';
CREATE SCHEMA looker_scratch;
GRANT CREATE ON SCHEMA looker_scratch to looker;
Create a connection to Vertica
In Looker, configure a DB connection via the interface. On the left panel, click Admin> Connections> New Database connection. More info here: http://www.looker.com/docs/setup-and-management/connecting-to-db
Enter the connection information in the required fields:
- Name: Give a name to the connection. This name will appear in the reference to the connection in the LookML model
- Dialect: Select Vertica from the drop down of dialects
- Host: Vertica server name or IP
- Port: by default 5433
- Database: Vertica’s database name
- Username and Password: Enter the user and password created in section 4.1 Database configuration
- Schema: The schema that contains the tables that you want to explore in Looker.
- Temp Database: It 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: Optional. Connection pool size. Use the default value
- Connection pool timeout: Optional. Use default value
- Database time zone: Time zone your Vertica database stores dates and times in, for example: UTC. Optional
- Query time zone: time zone you want your queries to display. For example: US Eastern (America – New York). Optional
- Additional Params: Optional. Use this option 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=. You can pass several parameters one after the other using & as shown below:
For a complete list of available JDBC connection parameters see Vertica’s documentation: http://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/ConnectingToHPVertica/ClientJDBC/JDBCConnectionProperties.htm
Click on “Test These settings” to verify the connection to Vertica is successful. Info for troubleshooting here: http://www.looker.com/docs/setup-and-management/connecting-to-db/testing-db-connectivity
Click Update Connection to save the connection.
Looker’s ability to provide some features depends on the database dialect’s ability to support that functionality.
New in releases 5.6 and up, the Looker SQL Runner shows database views in the sidebar.
In the current Looker release, Vertica 6 supports the following Looker features:
In the current Looker release, Vertica 7.1+ supports the following Looker features:
HPE Vertica Integration with Looker: Connection Guide