Open SQL Interface

The Looker LookML semantic modeling layer enables a data analyst to define dimensions, aggregates, calculations, and data relationships in a SQL database. LookML models provide code reusability and Git integration. A well-structured LookML model empowers users to do their own self-service data exploration and reporting.

The LookML model is the foundation of any data requested from Looker, whether that request comes from Looker's Explore interface in the Looker UI, an embedded visualization in your company portal or another third-party application, or a custom application that was developed with the Looker API. The Open SQL Interface provides access to the LookML models to any third-party application that supports Java Database Connectivity (JDBC). Applications can connect to a LookML model as if it were a database, allowing users to take advantage of all the work that was done by their data analysts in the LookML model, while using whatever tools they are most comfortable with.

How the Open SQL Interface surfaces LookML project elements

To understand how the Open SQL Interface surfaces the elements of a LookML project, it's important to understand how LookML projects are structured.

A LookML project is a collection of files that describe the objects, database connections, and user interface elements that are used to carry out SQL queries in Looker (see LookML terms and concepts for more information). The following LookML project concepts are related to the Open SQL Interface:

  • A LookML model specifies a database connection and one or more Explores. The Open SQL Interface surfaces models as database schemas.
  • An Explore is a logical grouping of one or more views and the join relationships between those views. The Open SQL Interface surfaces Explores as database tables.
  • A view defines a collection of fields (both dimensions and measures). A view is generally based on a table in your database or a derived table. Views can contain the columns from the underlying database table as well as any custom dimensions or measures that your end users may require. The Open SQL Interface surfaces the combination of a view name and a field name as a database column name. For example, the id dimension in the order_items view is surfaced by Open SQL Interface as a database column called order_items.id.

A Looker Explore can define join relationships between several views. Because it is possible that one view might have a field with the same name as a field in a different view, the Open SQL Interface includes both the view name and the field name when referencing a column. Therefore, use this format to reference a column name when sending queries to the Open SQL Interface:

`<view_name>.<field_name>`

As an example, if there was an Explore named order_items that joins a view called customer with a view called product and both of these views had an id dimension, you would refer to the two id fields as `customer.id` and `product.id`, respectively. To use the fully-qualified name with the Explore name as well, you would refer to the two fields as `order_items`.`customer.id` and `order_items`.`product.id`. (See Use backticks around database identifiers for information on where to put the backticks when referring to database identifiers.)

Setting up the Open SQL Interface

To use the Open SQL Interface, perform the following steps:

  1. Verify that the requirements are satisfied.
  2. Enable Open SQL Interface on your Looker instance.
  3. Download the Open SQL Interface JDBC driver file.

The following sections describe these steps.

Requirements

The following components are required to use the Open SQL Interface:

Enable Open SQL Interface on your Looker instance

Enable the Open SQL Interface on your instance by performing the following steps:

Download the Open SQL Interface JDBC driver

The Looker Open SQL Interface JDBC driver is called avatica-<release_number>-looker.jar. Download the latest version from GitHub at https://github.com/looker-open-source/calcite-avatica/releases.

The JDBC driver expects the following URL format:

jdbc:looker:url=https://your Looker instance URL

For example:

jdbc:looker:url=https://myInstance.cloud.looker.com

The JDBC driver class is:

org.apache.calcite.avatica.remote.looker.LookerDriver

Authenticating to the Open SQL Interface

The Open SQL Interface supports three methods for authentication:

OAuth

JDBC clients that support OAuth can be configured to use a Looker instance's OAuth server. Follow the steps to configure OAuth authentication:

  1. Use the API Explorer extension to register the JDBC OAuth client with your Looker instance so the Looker instance can recognize OAuth requests. See Registering an OAuth client application for instructions.
  2. Sign in to Looker with OAuth to request an access token. See Performing user login using OAuth for an example.
  3. Use a Properties object to pass the OAuth credentials when opening the JDBC connection to Open SQL Interface.

The following is an example using DriverManager#getConnection(<String>, <Properties>`):

String access_token = getAccessToken() //uses the Looker OAuth flow to get a token
String URL = "jdbc:looker:url=https://myInstance.cloud.looker.com"
Properties info = new Properties( );
info.put("token", access_token);
Connection conn = DriverManager.getConnection(URL, info);

Generating an access token using API keys

Instead of using the standard OAuth flow for generating an access token, you can follow these steps to use the Looker API to generate an access token that can be passed to the Open SQL Interface JDBC driver:

  1. Generate API keys for your Looker user as described on the Admin settings - Users page.

  2. Use the login API endpoint for your Looker instance. The response includes an access token in the format: Authorization: token <access_token>. Here's an example curl command to make this request:

      curl -k -d "client_id=<client_id>&client_secret=<client_secret>" https://<looker_host>/login\

  3. Pass the <access_token> value of the response as the token in the Properties object to pass the OAuth credentials when opening the JDBC connection to Open SQL Interface.

API keys

You can also use API keys to authenticate in place of a username and password. API keys are considered less secure than OAuth and may only be available during the preview of the Open SQL Interface. See API keys for information on creating API keys for your Looker instance.

Use the Client ID portion of the Looker API key as the username. Use the Client Secret portion for the password.

Running queries with the Open SQL Interface

Note the following guidelines when running queries with the Open SQL Interface:

LookML limitations

Note the following LookML limitations when sending queries to the Open SQL Interface:

SQL limitations

Note the following SQL limitations when sending queries to the Open SQL Interface:

Use backticks around database identifiers

When sending queries to Open SQL Interface, use backticks around schema, table, and column identifiers. Here is how to specify database elements using backticks with Looker terms:

  • schema: `<model_name>`
  • table: `<explore_name>`

  • column: `<view_name>.<field_name>`

Here is an example SELECT statement format using these elements:

SELECT `view.field`
  FROM `model`.`explore`
  LIMIT 10;

Specify LookML measures with AGGREGATE()

Database tables typically contain only dimensions, data that describes a single attribute about a row in the table. LookML projects, however, can define both dimensions and measures. A measure is an aggregation of data across multiple rows, such as SUM, AVG, MIN or MAX. (Other types of measures are supported as well, see the Measure types page for the full list of supported LookML measure types.)

With the Open SQL Interface, you must designate any LookML measures that are included in a query by wrapping the measure (including backticks) in the special function AGGREGATE(). For example, use this to specify the count measure from the orders view:

AGGREGATE(`orders.count`)

You must wrap LookML measures in the AGGREGATE() function whether the measure is in a SELECT clause, a HAVING clause, or an ORDER BY clause.

If you are not sure whether a field is a LookML measure, you can use the DatabaseMetaData.getColumns method to access metadata for the LookML project. The IS_GENERATEDCOLUMN column will indicate YES for any LookML measures, and NO for LookML dimensions. See the Accessing database metadata section for more information.

Example

Here is an example query using both dimensions and measures. This query retrieves the state and city dimensions from the customers view and the total amount measure from the orders view. Both of these views are joined into the orders Explore in the ecommerce model. For the cities that have more than 10 orders, this query response shows the top 5 cities by order amount:

SELECT `customers.state`, `customers.city`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

Specify filter-only fields and parameters with JSON_OBJECT

Open SQL Interface supports parameters and filter-only fields.

When running queries with Open SQL Interface, you can apply parameters and filter-only fields to the query by including a JSON_OBJECT constructor call with the following format:

JSON_OBJECT(
    '<view>.<parameter name>', '<parameter value>',
    '<view>.<filter name>', '<Looker filter expression>'
)

The JSON object can contain zero or more filter key-value pairs and zero or more parameter key-value pairs.

  • The key in the JSON_OBJECT constructor must be the name of a filter-only field or parameter.
  • For filter-only fields, the value for each key must be a Looker string filter expression.
  • For parameters, the value for each key must be a plain value that is defined in the parameter definition.

See the following sections for examples of using parameters and filter-only fields with Open SQL Interface.

Parameter example

As an example for using a parameter with Open SQL Interface, if the customers view had a parameter defined in Looker as follows:

parameter: segment {
  type: string
  allowed_value: {
    label: "Small (less than 500)"
    value: "small_customers"
  }
  allowed_value: {
    label: "Larger (greater than 10,000)"
    value: "large_customers"
  }
  allowed_value: {
    label: "Medium customers (Between 500 and 10,000)"
    value: "medium_customers"
  }
}

You could send this query to Open SQL Interface to apply the segment parameter value of medium_customers to the query:

SELECT `customers.segment_size`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'customers.segment', 'medium_customers'
))
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

Open SQL Interface will pass this parameter value to the query in Looker, and Looker will apply the medium_customers value to any fields in the Explore that are configured to use the segment parameter. See the parameter documentation for information on how parameters work in Looker.

Filter-only field example

You can use a filter field with Open SQL Interface. For example, if a products view had a dimension and a filter-only field defined in Looker as follows:

filter: brand_select {
  type: string
  }

dimension: brand_comparitor {
  sql:
    CASE
      WHEN {% condition brand_select %} ${products.brand_name} {% endcondition %}
      THEN ${products.brand_name}
      ELSE "All Other Brands"
    END ;;
    }

You could use the brand_select filter with Open SQL Interface by sending a query such as the following:

SELECT `products.brand_comparator`, `products.number_of_brands`,
  AGGREGATE(`products.total_revenue`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'products.brand_select', '%Santa Cruz%'
))
GROUP BY `products.brand_comparator`
ORDER BY 3 DESC LIMIT 5;

Open SQL Interface will apply the Looker string filter expression %Santa Cruz% to the query in Looker. See the filter documentation for information on how filter-only fields work in Looker.

Accessing database metadata

The Open SQL Interface supports a subset of the standard JDBC DatabaseMetaData interface, which is used to obtain information about the underlying database. You can use the following methods of the DatabaseMetaData interface to get information about your LookML model:

DatabaseMetadata.getSchemas

The following table describes how a LookML model relates to the standard database structures in the response of the DatabaseMetadata.getSchemas interface method.

getSchemas response column Description
TABLE_SCHEM LookML model name
TABLE_CATALOG (null)

DatabaseMetadata.getTables

The following table describes how a LookML model relates to the database structures in the response of the DatabaseMetaData.getTables interface method. The response includes standard JDBC metadata as well as Looker-specific metadata:

getTables response column Description
JDBC standard metadata
TABLE_CAT (null)
TABLE_SCHEM LookML model name
TABLE_NAME LookML Explore name
TABLE_TYPE Always returns the value TABLE_TYPE
Looker-specific metadata
DESCRIPTION Explore description
LABEL Explore label
TAGS Explore tags

DatabaseMetadata.getColumns

The following table describes how a LookML model relates to the database structures in the response of the DatabaseMetaData.getColumns interface method. The response includes standard JDBC metadata as well as Looker-specific metadata:

getColumns response column Description
JDBC standard metadata
TABLE_CAT (null)
TABLE_SCHEM LookML model name
TABLE_NAME LookML Explore name
COLUMN_NAME LookML field name in `<view_name>.<field_name>` format. For example, `orders.amount`.
DATA_TYPE The java.sql.Types code of the column. For example, Looker yesno fields are SQL type code 16 (BOOLEAN).
ORDINAL_POSITION The 1-based ordinal of the field within the Explore (mixing dimensions and measures together alphabetically by view name, then field name)
IS_NULLABLE Always returns the value YES
IS_GENERATEDCOLUMN YES for measures, NO for dimensions
Looker-specific metadata
DIMENSION_GROUP Name of the dimension group if the field is part of a dimension group. If the field is not part of a dimension group, this will be null.
DRILL_FIELDS List of drill fields set for the dimension or measure, if any
FIELD_ALIAS Alias for the field, if any
FIELD_CATEGORY Whether the field is a dimension or measure
FIELD_DESCRIPTION Field description
FIELD_GROUP_VARIANT If the field is presented under a field group label, the FIELD_GROUP_VARIANT will specify the shorter name of the field that is displayed under the group label.
FIELD_LABEL Field label
FIELD_NAME Name of the dimension or measure
HIDDEN Whether the field is hidden from the field picker in Explores (TRUE), or if the field is visible in the field picker in Explores (FALSE).
LOOKER_TYPE LookML field type for the dimension or measure
REQUIRES_REFRESH_ON_SORT Whether the SQL query must be refreshed in order to re-sort the field's values (TRUE), or if the field's values can be re-sorted without requiring a refresh of the SQL query (FALSE).
SORTABLE Whether the field can be sorted (TRUE) or cannot be sorted (FALSE)
TAGS Field tags
USE_STRICT_VALUE_FORMAT Whether the field uses strict value format (TRUE) or not (FALSE)
VALUE_FORMAT Value format string for the field
VIEW_LABEL View label for the field
VIEW_NAME Name of the view in which the field is defined in the LookML project

Identifying Open SQL Interface queries in the Looker UI

Looker admins can use the Looker UI to identify which queries originated from the Open SQL Interface:

  • In the Queries admin page, queries from the Open SQL Interface have a Source value of "Sql Interface". The User value will show the name of the Looker user who ran the query.

  • In the System Activity History Explore, queries from the Open SQL Interface have a Source value of "sql_interface". The User Email value will show the email address of the Looker user who ran the query. You can go directly to the History Explore filtered on "sql_interface" by inserting your Looker instance address to the beginning of this URL:

    https://your Looker instance URL/explore/system__activity/history?fields=history.source,history.completed_date&f[history.source]=sql_interface

Feedback for Open SQL Interface

Reach out to looker-sql-interface@google.com if you have any questions or feature requests for Open SQL Interface.