Using SQL Runner

On this Page
Docs Menu
  • Explore
  • Develop
  • Administer
  • Setup
  • 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:

    • In the Develop menu, select SQL Runner.
    • From an Explore, click the SQL on the Data bar to see the SQL. Then click Open in SQL Runner to see the query in SQL Runner or click Explain in SQL Runner to open SQL Runner and request the database’s execution plan for the query.

    Getting Information about Your Database Connection

    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:

    Viewing and Editing SQL Queries From Explores

    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.

    Writing Your Own Queries

    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.

    Getting Insight Into Data By 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 several methods to create an on-the-fly Explore:

    • For a query using SQL Runner
    • For a database table using SQL Runner
    • For a BigQuery table using the Looker BigQuery Chrome extension, as described in this article.

    Exploring from a SQL Query

    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 Tables Using Pre-Built Queries

    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_events and the public 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.

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

    Describe Your Table

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

    Show Indexes for Your Table

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

    Show the First Ten Rows in Your Table

    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.

    Display the Row Count of Your Table

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

    See 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:

    See 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:

    SELECT id ,COUNT(*)
    FROM public.users
    GROUP BY 1
    ORDER BY 2 DESC
    LIMIT 10
    

    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.

    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.

    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:

    Sharing Queries and Using Queries to Create Derived Tables

    After viewing or creating a query in SQL Runner, you can:

    • share the query with a colleague
    • create a derived table from the query

    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:

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

    Downloading and Copying Query Results

    After obtaining query results in SQL Runner, you can:

    • download the results
    • copy a list of values from a field

    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.

    Debugging using SQL Runner

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

    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.

    Examining the Database’s 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.

    Still have questions?
    Go to Discourse - or - Email Support
    Top