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 modify you 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:
- SQL Runner basics
- Using SQL Runner to create queries and Explores
- Using SQL Runner to create derived tables
Modifying Database Schema and Data
In addition to running queries on your database, the Database tab in SQL Runner enables you to 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_runnerpermission 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:
- Click the Database tab.
- 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.)
- Click Run to execute the statement.
- 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
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.
- From an Explore, run a query and click the SQL tab of the Data area to view the query’s SQL command.
- Under the SQL command, click the Explain in SQL Runner to load the query into SQL Runner within an
- Click Run to execute the
- View the output of the
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 Community topic.
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:
Searching Your Database
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:
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 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 and the
demo_db schema are selected.
- Select a table in the schema to see the columns in that table.
- 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:
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.
Describing Your Table
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:
Showing the First Ten Rows
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.
Displaying the Row Count
Use the Count option to have the database run a simple
count(*) to get the total row count of the table.
Seeing 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:
Seeing 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 Prebuilt SQL Queries
You can edit any SQL query in the SQL 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:
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.