The Connections page lists all of the database connections that you have configured for Looker use.
The table of connections that appears on the page shows basic information about the connections you’ve defined, as well as the status of those connections:
|Name||The name of the connection, which you define, and is used in the
|Host : Port||The host path where your database is located, and the port that Looker will use to connect to that host|
|Username||The username that Looker will use to connect to the database|
|Database||The name of the database that Looker will query when using this connection|
|SSL||Whether or not you are using SSL encryption to protect the data traveling between Looker and your database (please note there are other secure options that are not SSL)|
|Type||The SQL dialect of the database connection|
|Actions||Actions you can take for a connection|
Each connection includes a list of status checks to tell you whether or not Looker can successfully use the database connection. Potential issues are shown in yellow, while errors are shown in red.
Two common checks are cause for confusion:
- Can find temp schema
- Can use persistent derived tables
These checks do not need to pass in order for Looker to function, however, you will need them to pass in order to use persistent derived tables, which are a very valuable modeling feature.
To add a connection, simply click the New Database Connection button in the upper left of the page.
The fields that you will need to fill in change depending upon the SQL Dialect that you choose. However, there are a set of fields that apply to almost everyone:
The name of the connection as you want to refer to it. You should not use spaces. This value does not need to match anything in your database, it is just a label that you assign. You’ll use it in the
connection parameter of your LookML model.
The SQL dialect that matches your connection. It’s important to choose the correct value so that you are presented with the proper connection options, and so Looker can translate your LookML into SQL properly.
Host : Port
The hostname of your database host, and the port that Looker should use to connect to your database host.
To set the value of the host or port to an user attribute, click the person icon to the right of the host or port field, then choose the user attribute from the dropdown menu. Please note that this will disable persistent derived tables on this connection!
The name of the database on your host. For example, you might have a hostname of
my-instance.us-east-1.redshift.amazonaws.com on which there is a database called
sales_info. You would enter
sales_info in this field. If you have multiple databases on the same host, you may need to create multiple connections to use them (with the exception of MySQL, in which the word “database” means something a little bit different than most SQL dialects).
To set the value of the database to an user attribute, click the person icon to the right of the database field, then choose the user attribute from the dropdown menu. Please note that this will disable persistent derived tables on this connection!
The username that Looker should use to connect to your database. You should configure the user ahead of time according to the instructions in our database configuration instructions.
To set the value of the username to an user attribute, click the person icon to the right of the username field, then choose the user attribute from the dropdown menu. Please note that this will disable persistent derived tables on this connection!
The password that Looker should use to connect to your database. You should configure the password ahead of time according to the instructions in our database configuration instructions.
To set the value of the password to an user attribute, click the Change Password button, then click the person icon to the right of the password field. Finally, choose the user attribute from the dropdown menu. Please note that this will disable persistent derived tables on this connection!
Persistent Derived Tables
Check this box to enable persistent derived tables. Doing so will reveal the Temp Database field. Looker displays this option only if the database dialect you chose supports using PDTs.
Though labeled “temp database”, you’ll enter either the database name or schema name - as appropriate for your SQL dialect - that Looker should use to create persistent derived tables. You should configure this database or schema ahead of time according to the instructions in our database configuration instructions.
PDT Maintenance Schedule
This setting accepts a cron expression that indicates when Looker should check persistent derived tables (that are based on
sql_trigger_value) to be regenerated or dropped. The default value of
*/5 * * * * means “check every 5 minutes”.
For additional details, read our Discourse article about this feature.
Choose whether or not you want to use SSL encryption to protect data as it passes between Looker and your database. SSL is only one option that can be used to protect your data; other secure options are described here.
Here you can set the maximum number of connections that Looker can establish with your database. For the most part, you are setting the number of simultaneous queries that Looker can run against your database. You should set this value carefully. If it is too low, queries may be slow for your users as they wait for other user’s queries to return. If it is too high, you may overwhelm your database. The default value (which varies depending upon your SQL dialect) is typically a reasonable starting point.
Most databases also have their own settings for the maximum number of connections they will accept. You should check to see that Looker is set equal to or lower than your database’s configuration.
Connection Pool Timeout
If your users do request more connections than the Max Connections setting, the requests will wait for others to finish before they are executed. The maximum amount of time that a request will wait is configured here. You should set this value carefully. If it is too low, users may find their queries get cancelled because there isn’t enough time for other user’s queries to finish. If it is too high, large numbers of queries may build up causing users to wait for a very long time. The default value is typically a reasonable starting point.
The timezone in which your database stores time information.
There are several other time zone settings in Looker which have other impacts:
- Application Time Zone: This is the time zone that Looker uses when users schedule emails or dashboards, described here.
- Query Time Zone: You set this when you create a database connection, but only if you have not enabled user specific time zones. It indicates the time zone that should be displayed to your users when they pull time data in Looker (i.e. Looker converts from Database Time Zone into Query Time Zone).
- User Specific Time Zones: If you enable this feature, users can all set their own timezone. Looker will then convert from the Database Time Zone to each user’s time zone when they pull time data in Looker. This setting is described here.
You can include additional JDBC parameters for your queries here, if needed.
If you want to use an user attribute in a JDBC parameter, you can use liquid templating. The syntax is
To edit an existing connection, click the Edit button to the far right of a connection. You’ll be brought to same page that you use to create a connection (described above), but with the relevant information already filled out. Make any changes you need to, then click Update Connection.
To edit an existing connection, click the Edit button to the far right of a connection. You’ll be given the opportunity to confirm the deletion, but once you do so, it cannot be undone. Accidentally deleting a connection will disable any queries that use it, however, as long you re-create a new connection with the same name, functionality will be restored.
Other Connection Options
You’ll notice there is also a gear dropdown menu to the far right of a connection. It offers the following options:
This option brings you to a Looker explore page that allows you to create Looker reports based on the metadata of your connection. Although this option begins with the schema name, table name, and column count selected, you can manipulate it just like any other Looker report.
This option brings you to a Looker explore page that allows you to create Looker reports based on the metadata of your connection. Although this option begins with the schema name, catalog name, table count, and column count selected, you can manipulate it just like any other Looker report.
Show PDT Event Log
This option brings you to a Looker explore page that allows you to create Looker reports based on the derived table activity for this connection. The available fields are described in more detail on our PDTs page.
This option brings you to Looker’s SQL Runner, with the proper connection and schema already selected.
This option brings you to a list of basic, automatically generated Explore options for your conneciton. These are not your based on your customized data models, but they can allow some quick reporting on the raw data in your connection’s tables. This is typically only useful for getting an idea of table contents before modeling, rather than for actual data analysis purposes.