Getting started with Looker

Best Practices

Documentation

Community

Training

Certification

Articles in this section

See more

Using SQL Runner to create derived tables

Avatar
Looker Support
  • Updated
Follow

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 create a derived table using SQL Runner and how to use SQL Runner to debug derived tables. See these other documentation pages for information on:

Creating a derived table from a query

There are 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 a 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 already in it) and open the new derived table’s view in the selected project. If you selected a project that uses the older 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.

To create a derived table from a SQL Runner query:

  1. Use SQL Runner to create a SQL query that you want to use for a derived table.
  2. Click on the gear menu and select Get Derived Table LookML.
  3. Copy the LookML that has been generated. Alternatively, click add it to your project, choose the project you would like to add the derived table to, and enter a name for the view in View Name.

  4. If you did not add the derived table to your project by clicking add it to your project, open your project and click + at the top of the project file list in the Looker IDE. Or you can click a folder’s menu to create the file inside the folder.

  5. Choose Create View.

  6. Looker displays an empty box at the top of your list of views. Type the name of the new view and press Enter. Looker supports the use of uppercase and lowercase characters, numbers, underscores, and Unicode characters in filenames. If the name of your view file includes characters that are not supported by Looker, those characters will be replaced with underscores.

  7. Looker opens the new view.

  8. Delete all the contents of the new view and paste the derived table LookML you copied previously.

Debugging using SQL Runner

SQL Runner is also a useful tool for checking SQL errors 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 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 the Using SQL Runner to test derived tables Looker Community topic.