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 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:
- SQL Runner basics
- Using SQL Runner to create queries and Explores
- Managing database functions with SQL Runner
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.
- Use SQL Runner to create a SQL query that you want to use for a derived table.
- Click Add to Project from the gear menu in the upper right.
- Select the project you want to add this derived table to.
- Enter a view name for the derived table.
- Click Add to add the query as a derived table in your project.
- 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 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.
- Use SQL Runner to create SQL query that you want to use for a derived table.
- Click Get Derived Table LookML from the gear menu in the upper right.
- 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.
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 this Community topic.