Getting started with Looker

Best Practices

Documentation

Community

Training

Certification

Articles in this section

See more

Managing database functions with SQL Runner

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 modify your database schema and data using SQL Runner, view your database’s execution plan for a query with the EXAMINE statement, and how to use SQL Runner to get information about your database. See these other documentation pages for information on:

Modifying database schema and data

In addition to running queries on your database, the Database tab in SQL Runner lets you execute Data Definition Language (DDL) and Data Manipulation Language (DML) 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 and DML statements, so see the documentation for your database to find out which 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 or DML statement on your database in SQL Runner:

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

You can run a follow-up query to further verify that the 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 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 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 Help Center article.

Getting information about your database

The Database tab in 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 preselects 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 repopulate 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:

BigQuery-specific gear menu options

When you choose a BigQuery connection that supports multiple databases, Looker displays dialect-specific options in the gear menu:

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

Use the Search public projects option to search for public datasets that are not visible in the information schema.

The gear menu toggles between the Search public projects option and the Show available projects option.

Click Show available projects from the gear menu to revert the display back to connection-specific BigQuery projects and tables in the left navigation pane:

Searching your database

SQL Runner displays a search box under the selected Schema. The search browses the names of all tables, and table columns that contain the string in the search box. In the following figure, ‘airport_name’ is a column and ‘airport_remarks’ is a table.

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

BigQuery-specific search options

When you select a BigQuery connection that supports multiple databases, you must also select a BigQuery project from the Project selector to expose the associated dataset and tables:

When the project is selected, you can proceed with selecting a dataset or searching for a schema:

Getting table information

By default, SQL Runner preloads 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 lets you navigate the schemas and tables in your connections. Select a connection and a schema to see all the tables in that schema.

SQL Runner has some prewritten 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.

The available queries will vary by database dialect.

Table information

Looker displays the following options when you click the gear next to a table name:

  • Use the Describe option to display the column names in the underlying table as well as their data types.
  • Use the Show Indexes option to get information about how the table was indexed.
  • 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.
  • Use the Count option to have the database run a simple count(*) to get the total row count of the table.

Column information

Looker displays the following options when you click the gear next to a column name:

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

Getting column data type information

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:

Editing the prebuilt SQL queries

You can edit any SQL query in the Query area, including the preset 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.

Related articles