JDBC: How does Looker connect to databases? (Community)

View the original community article here

Last tested: Jan 13, 2021
 

What is JDBC?

Java Database Connectivity (JDBC) is an application program interface (API) specification for connecting programs written in Java to the data in popular databases. The API lets you encode access request statements in Structured Query Language (SQL) that are then passed to the program that manages the database. It returns the results through a similar interface.

What is a JDBC Driver?

A JDBC driver is a small piece of software that allows JDBC to connect to and communicate with different databases. At a high level, it determines how Looker connects to and interfaces with databases through the JDBC API. In almost all cases, Looker neither owns nor maintains drivers, however we are responsible for developing and testing around them to ensure they work with the product. The drivers we use for database connections are baked into the application's .jar file - additionally users hosting their own instances can use custom JDBC drivers, however we don't support the practice.

What happens if my dialect is not supported by Looker?

There is an important point to make here: Looker manages generating correct SQL, while the driver just manages connecting with and submitting API calls to the DB. So if a dialect is not supported by Looker, simply adding a custom driver for the dialect will likely not work. Many dialects are quite similar, so it's possible one could "trick" Looker into generating approximately-correct SQL by selecting a similar dialect in the connections panel, but this is neither recommended nor supported.

Sources:

• https://www.progress.com/faqs/datadirect-jdbc-faqs/how-does-jdbc-work
• http://www.theserverside.com/definition/Java-Database-Connectivity-JDBC
• http://www.theserverside.com/definition/JDBC-driver
• https://community.looker.com/general-looker-administration-35/obscure-jdbc-driver-connections-for-looker-1130

The JDBC String

When the JDBC driver initiates a connection, it creates a URL with all the necessary parameters it thinks it needs to connect to the database. These generally include the host, port, database or warehouse, and additional dialect-specific parameters. We refer to this URL as the JDBC String, and what that string looks like can often give us quite a few clues about what the driver thinks it's supposed to be doing.

How does this translate to Looker? JDBC strings generally have a common pattern, as illustrated here, with the pattern being something like jdbc:<driver_symbol>://<host_url>:<port>/<dialect-specific&additional_parameters> . Given this common pattern, Looker accepts input from the /Admin/Connections panel, and feeds those values to the JDBC driver, which creates the connection string.

Taking each of those sets of parameters one by one:

• driver_symbol = Determined by which dialect is selected in Looker. We use the same driver for multiple distinct dialects
• host_url = The URL or IP of the database, as input into Looker. This address must be publicly reachable over the open internet. You may use localhost in this field, if using an SSH Tunnel, or the database is on the same host as the Looker application.
• port = The database side port available to accept JDBC requests. This port must be open over the open internet. Will be the Looker side port listed in the tunnel script, if using an SSH Tunnel.
• dialect-specific&additional_parameters = A set of varied and impossible to comprehensively list parameters, some of which correspond to the JDBC driver itself and others of which are passed on to the DB. Looker's Additional Params will go here, as will many other inputs from the connections panel.

>> Key Takeaway: The first step of any investigation about "why can't I connect to my database" should be to verify that each of the parameters input into the connections panel are correct.

For more information about the connections panel, refer to dialect specific Looker documentation.