Using SQL Runner

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 on-the-fly Explore from a SQL query, run pre-written 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.

Navigating to SQL Runner

If you have the permissions to see LookML and to use SQL Runner, you can navigate to SQL Runner in two ways:

Running Queries in SQL Runner

To run a query on your database, you can write the SQL query from scratch, or you can use an Explore to create the query. You can also use the history to re-run 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 so the query should be in valid SQL for your database dialect. For example, each dialect has slightly different SQL functions with specific parameters that should be passed to the function.

  1. Click in the SQL Query area and type your SQL command.
  2. Optionally, double-click on a table name or field from the Field Picker to include it in your query at the cursor location.
  3. Click Run to run the query against your database.
  4. View the results in the Results area. SQL Runner will load up to 5000 rows of the query’s result set. For SQL dialects that support streaming, you can download the results to see the entire result set.

Some SQL programs will let you run multiple queries in a series. However, you can only run one query at a time in SQL Runner.

When you have a query that you like, you can then 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 can also use an Explore to create a query, then get the SQL command for that query to use in SQL Runner:

  1. From an Explore, click the SQL tab from the Data bar.
  2. Select the text of the SQL query and copy it into SQL Runner, or
  3. Click Open in SQL Runner or Explain in SQL Runner to open the query in SQL Runner.

Once a query is added to the SQL Query area of SQL Runner, you can click Run to query the database. Alternatively, you can edit the query and then run the new query.

SQL Runner History

You can also see a recent history of all queries you have run in the SQL Runner.

To see your history, click the History tab at the top of the navigation pane. SQL Runner displays all of the queries run on the database connection. Red indicates a query that did not run due to an error.

Click on a query in the history to populate that query into SQL Runner, then click Run to re-run the query:

Sorting Your Query

In your query results, you can click on any column header to sort the results by the values in that column. Click the column header a second time to reverse the order of the sort. In addition, you can sort by multiple columns by holding down shift, then clicking on the column headers in the order you would like them sorted.

Sharing Queries

You can share a query in SQL Runner with another user with SQL Runner access. To share a query, simply copy the URL in the URL bar:

Downloading Results

Once you have run your SQL query, you can download the results in a variety of formats.

  1. Write a query in the SQL Query box. (You do not need to run the query in SQL Runner at this point.)
  2. Select Download from the gear menu in the upper right.
  3. Select the file format of the download (text file, CSV, JSON, etc.).
  4. Click Open in Browser to see the results in a new browser window, or click Download to download the results to a file on your computer.

    Note: When you click either Open in Browser or Download, Looker will re-run the query and then perform the download.

For SQL dialects that support streaming, the SQL Runner Download option will download the entire results set. For SQL dialects that do not support streaming, the SQL Runner Download option will download only the rows of the query that are shown in the Results section (up to 5000 rows).

Copying Column Values

You can copy column values from the Results section in SQL Runner. Click on a column’s gear menu to copy the values to your clipboard. From there you can paste the column values into a text file, Excel document, etc.

Creating a Derived Table From a Query

There several ways to create a derived table from a query in SQL Runner.

Adding to a LookML Project

SQL Runner is a great place to test the SQL for your derived tables before adding them to your model. You can even get a query from a different tool, test it in SQL Runner, and then add it to your project.

  1. Use SQL Runner to create SQL query that you want to use for a derived table.
  2. Click Add to Project from the gear menu in the upper right.
  3. Select the project you want to add this derived table to.
  4. Enter a view name for the derived table.
  5. Click Add to add the query as a derived table in your project.
  6. Looker will switch to Development Mode (if not yet in development mode) and open the new derived table’s view in the selected project. If you selected a project that uses YAML-based LookML, Looker will provide the derived table in YAML-based LookML. If you selected a project that uses New LookML, Looker will provide the derived table in New LookML.

Getting the LookML for a Derived Table

Another way to create a derived table from your SQL Runner query is to use the Get Derived Table LookML option from the SQL Runner gear menu. Just like with the Add to Project option, Looker will provide the LookML needed to make your SQL query into a derived table. From there, you can copy the LookML to paste into your project yourself, which is useful if you want to replace an existing derived table. Even easier — you can simply click the add it to your project link. Looker will prompt you for the project and view name and add it to your project.

  1. Use SQL Runner to create SQL query that you want to use for a derived table.
  2. Click Get Derived Table LookML from the gear menu in the upper right.
  3. Typically, Looker provides only the New LookML syntax. If the YAML-based LookML legacy feature is ON, the Get Derived Table LookML dialog will provide tabs for both New LookML and for YAML-based LookML (Old LookML). Select the tab to get the LookML version you need for your project.
  4. Copy the LookML from this window and manually paste it into your project, or click the add it to your project link to add the LookML to your project using the same procedure as above.

Modifying Database Schema and Data

In addition to running queries on your database, SQL Runner enables you to execute Data Definition Language (DDL) statements on your database. You can use SQL Runner to make schema changes (such as create, drop, and alter) and data changes (such as insert, update, and delete). SQL dialects have varying support for DDL commands, so see the documentation for your database to find out which DDL statements are supported.

Looker does not control authorization of which SQL statements a user is allowed to run on your database. Looker users with the use_sql_runner permission are given access to SQL Runner, but SQL Runner does not gate which commands the user can execute. If your database administrator wants to prevent SQL Runner users from modifying the database schema, the admin must do this by configuring user permissions for the database itself.

To execute a DDL statement on your database in SQL Runner:

  1. Enter the DDL statement in the in the SQL Query box. (See the documentation for your database dialect for the support and syntax of DDL statements.)
  2. Click Run to execute the statement.
  3. Verify in the Results box that the statement was successfully executed.

You can run a follow-up query to further verify that the DDL statement was successful. In the example above, we added a user “Erin Looker-Docs” to the database. We can run a SELECT query to verify that the user was added properly:

Examining an Execution Plan Using EXPLAIN

In addition to running SQL queries against your database, you can use SQL Runner to run an EXPLAIN function for a query. The EXPLAIN function, which is supported by most all SQL dialects, returns the database’s execution plan for a query.

  1. From an Explore, run a query and click the SQL tab of the Data area to view the query’s SQL command.
  2. Under the SQL command, click the Explain in SQL Runner to load the query into SQL Runner within an EXPLAIN function.
  3. Click Run to execute the EXPLAIN function.
  4. View the output of the EXPLAIN function.

The exact information and format of the EXPLAIN response will depend on your specific dialect, so you should see the documentation for your dialect for specifics.

In the MySQL example above, the EXPLAIN function returns a list of the steps taken by the database to complete the query. This may be useful for queries that seem slow to execute, since you may find that your database is scanning an entire table in a query, when perhaps the table could use an index to improve performance.

For a step-by-step example of using Explain in SQL Runner to optimize SQL, see this article.

Creating an On-the-fly Explore

From SQL Runner you can get fast insight into the data by creating an on-the-fly Explore for a SQL query or database table. You can use the Looker Explore to select fields, add filters, visualize the results, and create SQL queries.

There are two ways to open an on-the-fly Explore from SQL Runner:

BigQuery users can also create an on-the-fly Explore for a SQL query or database table using the Looker BigQuery Chrome extension, as described in this article.

Exploring from SQL Runner Query Results

SQL Runner enables you to open an Explore from a SQL query. This creates a temporary Explore from the query written in the SQL Runner. That lets you test what is returned by the query, as well as visualize the results. This can be used for any query but is especially useful for testing queries you plan to use for derived tables.

  1. Use SQL Runner to create the SQL query that you want to use.
  2. Click Explore from the gear menu in the upper right. This will take you to a new Explore, where you can explore the SQL query as if it were a saved table in your model.
  3. You can copy the URL to this Explore for sharing.
  4. If you want to add this query as a derived table in your project directly from here, click Add View to Project.

Exploring a Table Listed in SQL Runner

Use the Explore Table option to create an on-the-fly Explore for any table in the connection. This lets you use Looker on a table before you’ve modeled it, exploring the table just like a LookML view.

Once you open an Explore for the table, you can decide whether to add the table to your project. You can also use the Explore’s SQL tab to see the SQL queries that Looker sends to the database, and then use the Open in SQL Runner button to bring the query back into SQL Runner.

  1. In SQL Runner, click the gear for a table and select Explore Table.
  2. Looker generates a temporary model with a view for the table, then displays the Explore.
  3. Looker provides a dimension field for each column in the table. (This is the same way that Looker generates a model at the start of a project.)
  4. Looker automatically includes timeframes for any date fields.
  5. Looker also includes a count measure.

Note that when using the Explore Table option, there is no LookML file associated with the Explore — it is just an on-the-fly view of the table.

Getting Information about Your Database

SQL Runner has a bunch of tools to give you insight into your database.

Getting Database Connection Information

When you choose a connection in SQL Runner, Looker displays the database dialect for that connection at the right of the SQL QUERY banner. If you navigated to SQL Runner by choosing Open in SQL Runner or Explain in SQL Runner, then Looker pre-selects the appropriate connection for you and displays the connection’s database dialect.

Click the connection gear menu to get more options for the database connection:

Use the Refresh Schemas & Tables option to re-populate the SQL Runner left navigation pane with the schemas and tables in the database.

Use the Show Processes option to display information about queries and processes currently running on the connection:

Searching Your Database

When the SQL Runner Schema Search feature is enabled in Looker Labs, SQL Runner displays a search box under the Connection list. The search finds the names of all schemas, tables, and table columns in your connection that contain the string in the search box:

Click on one of the search results to navigate to that item in SQL Runner:

Getting Table Information

By default, SQL Runner pre-loads all table information when you select a connection and a schema. For connections that have many tables or very large tables, an admin can disable this behavior by deselecting the SQL Runner Precache option in the Connections page.

SQL Runner’s left hand navigation panel enables you to navigate the schemas and tables in your connections. Select a connection and a schema to see all the tables in that schema. In the example below, thelook and the demo_db schema are selected.

  1. Select a table in the schema to see the columns in that table.
  2. Hover over a column name to see the type of data in that column (integer, in this case).

Each column name also has an icon to represent the data type:

SQL Runner has some pre-written queries to help you understand your data. In order to use these queries, click the gear that appears next to the name of a table or table column and select the desired query. Looker generates the SQL automatically in the SQL Query section, and the query will be run.

Note: The available queries will vary by database dialect.

Describing Your Table

Use the Describe option to display the column names in the underlying table as well as their data types:

Showing Indexes

Use the Show Indexes option to get information about how the table was indexed:

Showing the First Ten Rows

Use the Select 10 option to return the first ten rows in the table. This is a good way to get a sense of what the data actually looks like.

Displaying the Row Count

Use the Count option to have the database run a simple count(*) to get the total row count of the table.

Seeing the Most Common Values for a Table Column

Use the Most Common Values option from the table column gear menu to run a query to list the most common values for that table column, along with a count of the number of times that value is found in the column:

Seeing the Count of Different Values in a Table Column

Use the Approximate Count Distinct option from the table column gear menu to retrieve an approximate count of the number of distinct values found in the column:

Editing the Pre-built SQL Queries

You can edit any SQL query in the SQL Query area, including the pre-set SQL queries chosen from the table and field gear menus.

For example, you can use the SQL Runner Count query to load in a basic count command for a database, then edit the SQL query. So if you think the id column in the public.users table could be a primary key, you can validate that there are no duplicate values by editing the count query like this:

FROM public.users

Since the query is sorted by the count before limiting the results to 10 rows, the results will include the highest count values. As you can see below, the count for each id value is 1, so id is likely the primary key in this table. However, this query only specifies the maximum count of existing rows in the table so, when possible, be sure to have the primary_key specified at the database level as well.

Debugging using SQL Runner

SQL Runner is also a useful tool for checking SQL errors in queries or in the definition of a derived table.

SQL Runner Error Highlighting

SQL Runner highlights the location of errors in the SQL command and includes the position of the error in the error message:

The position information provided will vary depending on the database dialect. For example, MySQL provides the line number that contains the error, while Redshift provides the character position of the error. Other database dialects might have one of these or other behaviors.

SQL Runner also highlights the location of the first syntax error in the SQL command by underlining it in red and marking the row with an “x”. Hover over the “x” to see more information on the error. After you fix that issue, click Run to see if there are any more errors in the query.

Using SQL Runner to Check Errors in Explores

If you run into SQL syntax errors in an Explore, you can use SQL Runner to determine the location of the error and the type of error, such as spelling mistakes or missing commands. For more information, see this article.

Using SQL Runner to Test Derived Tables

If you see an error coming from a derived table, you can determine the cause of the error by copying the SQL statement into SQL Runner and testing different parts of the SQL to narrow down the location of the error. For more information, see this article.

Supported Database Dialects for SQL Runner Features

Looker’s ability to provide SQL Runner features depends on the database dialect’s functionality. The following list shows which dialects support SQL Runner features in the most recent Looker release: