User Guide Getting Started Help Center Documentation Community Training
Looker
  
English
Français
Deutsch
日本語
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 on-the-fly 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 on-the-fly 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.

  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 list 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. SQL Runner also has a 65,535 character limit on queries, including white space.

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 also can 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.

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.

  1. Once 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.
  2. You can edit the visualization using the Edit menu.
  3. You can share visualizations created with SQL Runner by sharing the URL. Any customizations that you make using the visualization’s Edit menu will be saved and the link will not change.

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

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 ${view_name.field_name} or ${view_name.SQL_TABLE_NAME}. This can save time when constructing a query to troubleshoot a derived table, for example.

Looker resolves any LookML substitutions and then 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.

To run a query against your LookML model in SQL Runner:

  1. Click the Model tab.
  2. Select the model you want to query.
  3. Click in the SQL Query area and enter your SQL query using LookML fields.
  4. Optionally, double-click on a view in the view list to include the view in your query at the cursor location.
  5. To see the list of fields in a view, click on the view in the Views section. Optionally, you can double-click on a field in the field list to include it in your query at the cursor location.
  6. In the Prepared SQL Query area, you can view the resulting SQL query that is built after any LookML substitutions have been translated to SQL.
  7. Click Run to run the query against your model.
  8. 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.

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, test existing queries, or open a new Explore from the results. SQL Runner error highlighting helps test and debug queries.

Viewing a Field’s LookML from SQL Runner

From the field list in the Model tab, you can also see the LookML for a field. Hover over the field in the field list, and click the Looker icon to the right of the field name:

Looker opens up the LookML IDE, and loads the file where the field is defined.

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 rerun 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.

    When you click either Open in Browser or Download, Looker will rerun 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, or other location.

If your Looker admin has enabled the SQL Runner Vis Labs feature, you have other options in the column gear menu as well:

You can also manually move, pin and resize columns in the results table.

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:

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.

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

  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.

Creating Custom Fields While Exploring in SQL Runner

If you have access to the custom fields feature, you can use custom fields to visualize unmodeled fields in SQL Runner. As described in the previous section, click Explore from the gear menu. Then, in the Field Picker:

Exploring a Table Listed in SQL Runner

Use the Explore Table option in the Database tab 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. Click the Database tab.
  2. In SQL Runner, click the gear for a table and select Explore Table.
  3. Looker generates a temporary model with a view for the table, then displays the Explore.
  4. 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.)
  5. Looker automatically includes timeframes for any date fields.
  6. Looker also includes a count measure.

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.

Debugging Using SQL Runner

SQL Runner is also a useful tool for checking SQL errors in queries.

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.

  1. From the Explore, click the SQL tab from the Data bar.
  2. Click Open in SQL Runner to open the query in SQL Runner.

This copies the Explore’s generated SQL to SQL Runner. As shown above, SQL Runner highlights the location of errors in the SQL command and includes the position of the error in the error message. You can then make changes and re-run the query in SQL Runner until you have corrected the errors.

Using SQL Runner to Check Errors in Derived Tables

For information about using SQL Runner to check SQL errors in derived tables, see the Using SQL Runner to Test Derived Tables Looker Community article.

Top