Using SQL Runner to create queries and Explores

SQL Runner provides a way to directly access your database and leverage that access in a variety of ways. Using SQL Runner, you can easily navigate the tables in your schema, use an ad hoc Explore from a SQL query, run prewritten descriptive queries on your data, see your SQL Runner history, download results, share queries, add to a LookML Project as a derived table, and perform other useful tasks.

This page describes how to run queries in SQL Runner, create ad hoc Explores, and how to use SQL Runner to debug queries. See these other documentation pages for information on:

Running queries in SQL Runner

To run a query on your database, you can write the SQL query from scratch, use an Explore to create the query, or run a query against a LookML model. You can also use the history to rerun a previous query.

Writing a SQL query from scratch

You can use SQL Runner to write and run your own SQL queries against your database. Looker passes your query to your database just as you have written it, so be sure that the syntax of your SQL query is valid for your database dialect. For example, each dialect has slightly different SQL functions with specific parameters that should be passed to the function.

Enter a SQL query in the field below the Query bar.
Optionally, select a table name or field from the field list to include it in your query at the cursor location.
Select Run to run the query against your database.
View the results in the Results area. SQL Runner will load up to 5,000 rows of the query's result set. For SQL dialects that support streaming, you can download the results to see the entire result set.

After running a query, you can add the query to a project, get the LookML for a derived table, or share the query.

You can also use SQL Runner to play with new queries or test existing queries. SQL Runner error highlighting helps test and debug queries.

Using an Explore to create a SQL query

You also can use an Explore to create a query, and then get the SQL command for that query to use in SQL Runner:

From an Explore, select the SQL tab from the Data bar.
The SQL query will appear under the Data bar.
Select Open in SQL Runner or Explain in SQL Runner to open the query in SQL Runner.

Once a query is added to the field below the Query bar, you can select Run to query the database. Alternatively, you can edit the query and then run the new query.

Note: Database connection access in Looker is model-driven. A user's ability to see and use a database connection in both Explores and SQL Runner is determined by the LookML models that their assigned roles grant them access to. If a user's role grants them access to a model set that uses a broader-permission connection, that connection automatically appears as an available option in SQL Runner.

Because of this model-driven behavior, it's not possible to hide a connection from SQL Runner while keeping it available for Explores for the same user if that user's role grants them access to both. As a workaround, implement separate roles for users who require only restricted SQL Runner access and don't need access to Explores that are built on the broader-permission connection, or instruct users to manually select the restricted connection in SQL Runner.

Creating visualizations with SQL Runner

If your Looker admin has enabled the SQL Runner Vis Labs feature, you can create visualizations directly in SQL Runner.

With SQL Runner Vis enabled, the SQL Runner panels are reorganized. The visualization panel appears at the top, the results panel is in the middle, and the query panel appears at the bottom.

After a SQL query is created and run, you can open the Visualization tab to view the visualization and choose a visualization type, just as you would do on the Explore page.
You can edit the visualization by using the Settings menu.
You can share visualizations that were created with SQL Runner by sharing the URL. Any customizations that you make by using the visualization's Settings menu will be saved and the link won't change.

There are some things to keep in mind about how SQL Runner visualizations work:

The results table and visualization interpret any numeric field as a measure.
The full field name is always used in the results table and visualization. Therefore, the Show Full Field Name option in the Settings menu is inactive.
To use custom fields, table calculations, pivots, column totals, and subtotals, explore from your SQL Runner query.
Static map (regions) visualizations are not supported by SQL Runner visualizations, however maps that use latitude and longitude data (map and static map (points) visualizations) are supported.
Timeline visualizations are not supported by SQL Runner visualizations.

Pivoting dimensions

You can edit the query results to pivot by one or more dimensions in SQL Runner visualizations. To pivot a field:

Select a column's gear menu in the Results area to expose column options.
Select Pivot Column.

The pivoted results display in the SQL Runner visualization.

To unpivot results, select the pivoted column's gear menu and select Unpivot Column.

Changing the field type

When displaying your query's results and visualization, SQL Runner automatically interprets any numeric field as a measure and any non-numeric field as a dimension. You can override the default field type and convert a dimension to a measure – or vice versa – by following these steps:

Select a column's gear menu in the Results area to expose column options.
Select Convert to Dimension or Convert to Measure to change the field type.

The visualization will display the new field type.

Running a query against a LookML model

You can use SQL Runner to write and run SQL queries against a LookML model, instead of directly against your database. When constructing your query against a model, you can use LookML substitution operators, such as