If you come to Looker from a SQL background, you’re probably curious about how Looker generates SQL. Fundamentally, Looker is a tool that generates SQL queries and submits them against a database connection. Looker formulates SQL queries based on a LookML project that describes the relationship between tables and columns in the database. By understanding how Looker generates queries, you will better understand how your LookML code translates to efficient SQL queries.

Every LookML parameter controls some aspect of how Looker generates SQL, by altering the structure, content, or behavior of the query. This page describes the principles of how Looker generates SQL, but does not cover all LookML elements in detail. See the LookML reference documentation page for complete details.

Viewing the query

In a saved Look or an Explore, you can use the SQL tab in the Data section to see what Looker sends to the database to get the data. You also can use the links at the bottom to view your query in SQL Runner or see the database’s explain plan for the query. For more on SQL Runner, see the SQL Runner basics documentation page. For more information on optimizing a query using SQL Runner, see the How to optimize SQL with EXPLAIN Help Center article.

Canonical form of a Looker query

Looker’s SQL queries always take the following form.

The LookML project defines all the dimensions, measures, Explores, and views referenced in the formula above. Filter expressions are specified in Looker by the user to shape ad hoc queries. Filter expressions can also be declared directly in the LookML to apply to all queries.

Fundamental components of a Looker query

All Looker queries are represented by these fundamental parameters applied to a LookML project, as seen in the formula above.

Looker uses the following parameters to generate a complete SQL query:

• model: the name of the LookML model to target, which specifies the target database
• explore: the name of the Explore to query, which populates the SQL FROM clause
• Fields: the dimension and measure parameters to include in the query, which populate the SQL SELECT clause
• filter: Looker filter expressions to apply to zero or more fields, which populate the SQL WHERE and HAVING clauses
• Sort order: the field to sort by, and the sort order, which populates the SQL ORDER BY clause

These parameters are precisely the elements that a user specifies when building a query on the Looker Explore page. These same elements show up in all modes of executing queries with Looker: in the generated SQL, in the URL that represents the query, in the Looker API, and so on.

What about the views specified by the LEFT JOIN clauses? JOIN clauses are populated based on the structure of the LookML model, which specifies how views join to Explores. When constructing SQL queries, Looker includes JOIN clauses only when required. When users are building a query in Looker, they don’t have to specify how tables join together, because this information is encoded in the model— one of Looker’s most powerful benefits to business users.

An example query and the resulting SQL

Let’s build a query in Looker to demonstrate how the query gets generated according to the pattern above. Consider an e-commerce store with tables to track users and orders. The fields and table relationships are shown below.

Let’s find the number of orders (ORDERS Count) grouped by state (USERS State) and filtered by creation date of the order (ORDERS Created Date).

Below is the query result in the Looker Explore page.

Clicking the SQL tab shows the SQL generated and executed by Looker.

Note the similarity to the canonical formula stated above. Looker’s SQL exhibits some traits of machine-generated code (e.g. COALESCE(users.state,'') AS "_g1"), but always fits the formula.

Experiment with more queries in Looker to prove to yourself that the query structure is always the same.

Running raw SQL in Looker’s SQL Runner

Looker includes a feature called SQL Runner where you can run any SQL you like against the database connections you’ve set up in Looker.

Since every query generated by Looker results in a complete, functional SQL command, you can use the SQL Runner to investigate or play with the query.

Raw SQL queries executed in SQL Runner produce the same result set

If the SQL contains any errors, SQL Runner will highlight the location of the first error in the SQL command, and will include the position of the error in the error message.

Note: If your connection settings specifies a database time zone, Looker will issue a SET TIMEZONE upon connection, which may affect results.

Examining query components in the URL

After running a query in Looker, you can examine the expanded share URL to see the fundamental components of a Looker query. Begin by selecting Share from the Explore’s gear menu:

If you are starting from a Look, click the Look’s Explore From Here link to open the query in an Explore.

From there, the Share URLs window appears, which shows the expanded URL:

For example, the above query produces the following expanded share URL:

The URL provides sufficient information to recreate the query:

How Looker structures JOINs

In the query SQL above, notice that the orders Explore appears in the main FROM clause and the joined views appear in the LEFT JOIN clauses. Looker joins can be written in many different ways, which is explained in more detail on the Working with joins in LookML page.

SQL blocks specify custom SQL clauses

Not all elements of a Looker query are machine-generated. At some point the data model needs to provide specific detail for Looker to access the underlying tables and compute derived values. In LookML, SQL blocks are snippets of SQL code provided by the data modeler, which Looker uses to synthesize complete SQL expressions.

The most common SQL block parameter is sql, used in dimension and measure definitions. The sql parameter specifies a SQL clause to reference an underlying column or to perform an aggregate function. In general, all LookML parameters starting with sql_ expect a SQL expression of some form. For example: sql_always_where, sql_on, and sql_table_name. The LookML Reference provides details on each parameter.

Example SQL blocks for dimensions and measures

Below are a few examples of SQL blocks for dimensions and measures. The LookML substitution operator ($) makes these sql declarations appear deceptively unlike SQL. However, after substitution has occurred, the resulting string is pure SQL, which Looker injects into the SELECT clause of the query.

As shown in the last two dimensions above, SQL blocks can use functions that are supported by the underlying database (such as MySQL functions CONCAT and DATEDIFF in this case). The code you use in SQL blocks must match the SQL dialect used by the database.

Example SQL block for derived tables

Derived tables also use a SQL block to specify the query that derives the table. An example is below:

Example SQL block for filtering an Explore

The sql_always_where and sql_always_having LookML parameters let you restrict the data available to a query by injecting a SQL block to the SQL WHERE or HAVING clauses. In this example, the LookML substitution operator ${view_name.SQL_TABLE_NAME} is used to reference a derived table: