Query tasks

Preview

This feature is in Public Preview.

Run your first query

When you log in to Databricks SQL for the first time, an onboarding panel appears to help you get started quickly. If you are not an administrator, running your first query is the first onboarding task.

Note

If you have dismissed the onboarding panel, you can run this query by following the steps in Create a query later in this article.

1. If you don’t see the onboarding panel, look for Tasks Completed in the sidebar, and click it.

   

2. In the onboarding panel, click Run your first query. The SQL editor appears, loaded with the following query:

   -- We've given you a sample query below to help get started.
   -- Just hit "Run" to run the query and get your results.
   SELECT
     concat(pickup_zip, '-', dropoff_zip) as route,
     AVG(fare_amount) as average_fare
   FROM
     `samples`.`nyctaxi`.`trips`
   GROUP BY
     1
   ORDER BY
     2 DESC
   LIMIT 1000
   

   This query runs against the nyctaxi database in the SAMPLES catalog, which is readable in all workspaces.

3. Click Run. After a few seconds, the query results are shown below the query.

   

View the list of saved queries

Click Queries in the sidebar. The Queries list displays.

By default, queries are sorted in reverse chronological order by the Created By column. You can reorder the list by clicking the column headings.

Filter the list of saved queries

You can filter the list of queries by the list of queries you have created (My Queries), by favorites, and by tags.

Create a query

1. Do one of the following:

   • Click SQL Editor in the sidebar.

     

   • Click Create in the sidebar and select Query.

   • Click Queries in the sidebar and click the + New Query button.

   The SQL editor displays.

2. If not selected, select the data sources tab:

   

3. Click the icon and select a SQL endpoint. To filter the list, enter text in the text box.

   

   The first time you create a query the list of available SQL endpoints displays in alphabetical order. The next time you create a query, the last used SQL endpoint is selected.

   The icon next to the SQL endpoint indicates the status:

   • Running
   • Starting
   • Stopped

   Note

   If there are no SQL endpoints, contact your Databricks SQL administrator.

Browse data objects

If you have metadata read permission, the schema browser displays the available databases and tables.

Note

If there are no data objects, contact your Databricks SQL administrator.

You can also browse data objects in the data explorer.

1. In the box below a SQL endpoint, click the icon and select a database.

   

   Note

   • You must select a running SQL endpoint.
   • In order to see a data object in the schema browser, you must either be the data object owner or be granted privileges to the object.

   To refresh the schema, click the button.

   

   You can filter the schema by typing filter strings in the search box:

   

2. Click a table to shows its columns.

   

Construct a query

You construct a query by inserting elements from the schema browser or typing in the SQL editor.

• In the schema browser, click the double arrow on the far right of a data object.

  

• Type in the SQL editor.

  The SQL editor supports autocomplete. As you type, autocomplete suggests valid completions. For example, if a valid completion at the cursor location is a column, autocomplete suggests a column name. If you type select * from table as t where t., autocomplete recognizes that t is an alias for table and suggests the columns inside table.

  

When you are done editing, click Save.

Edit multiple queries

When the tabbed editor is enabled, you can edit multiple queries at the same time.

• Click the . A tab with the query name New Query displays.

  

• Click the Open Query link. The queries dialog displays.

  

  1. Optionally click My Queries or Favorites to filter the list of queries.

  2. In the row containing the query you want to view, click the Open button. The query displays in a new tab.

     

To open a new tab, click +, then select Create new query or Open existing query. To run the query, click Run. If you right click on a tab, you’ll see options to Close others, Close left, Close right, and Close all. If you right click on the first or last tab, the options to Close left or Close right are not available.

Execute a query

To execute a query:

1. Select a SQL endpoint.

2. Specify a query in the SQL editor.

3. Press Ctrl/Cmd + Enter or click the Execute button.

   

Note

• If an endpoint is stopped and you execute a query, the endpoint is started. To manually start an endpoint, follow the steps in Start an endpoint.
• The Limit 1000 checkbox is selected by default to ensure that the query returns at most 1000 rows. If you know that there are more rows, you can unselect the checkbox and specify a LIMIT clause in your query.

The query result displays in the Table tab.

Terminate a query

To terminate a query while it is executing, click Cancel. An administrator can terminate an executing query that was started by another user by viewing the Terminate an executing query.

Set query description

To set a query description:

1. Click the query info tab.

   

2. Click the Add description link or the existing description.

   

3. Edit the description and press Enter.

Refresh a query

If your query has a schedule it will refresh automatically. To manually refresh a query in between automatic refreshes, click the Refresh button.

Save a query

To save a query, press Ctrl/Cmd + S or click the Save button. Only saved queries display in the Queries list.

Revert to a saved query

When you edit a query, a Revert link displays next to the Save button.

You can click Revert to go back to your saved version. Your changes are persisted to browser storage when you leave, but the browser may still display warnings about losing work.

Move a query to Trash

To move a query to Trash, click the vertical ellipsis at the top-right of the SQL editor and select Move to Trash. Confirm by clicking Move to Trash.

Restore a query from Trash

1. In the All Queries list, click .
2. Click a query.
3. Click the vertical ellipsis at the top-right of the SQL editor and select Restore.

Permanently delete a query

1. In the All Queries list, click .
2. Click a query.
3. Click the vertical ellipsis at the top-right of the SQL editor and select Delete.

Copy a query

To create a copy of a query (created by you or someone else), you can fork it. To fork a query, click the vertical ellipsis at the top-right of the SQL editor and select Fork:

View query info

To view when a query was created or updated, click the query info tab:

View past executions of a query

To view past executions performed in the SQL editor, click the past executions tab:

This tab does not show scheduled executions.

Download a query result

You can download a query result as a CSV, TSV, or Excel file. Up to 1,000,000 rows of results can be downloaded.

1. Click the vertical ellipsis button beneath the results pane.
2. Select Download as [CSV | TSV | Excel] File.

If you cannot download a query, your administrator has disabled download for your Databricks SQL instance.

Transfer ownership of a query

When you save a query, you become the query’s owner. If a query’s owner is removed from a workspace, the query no longer has an owner. An admin user can transfer ownership of a query to a different user. To transfer ownership by using the Databricks SQL UI:

1. Open the query.
2. Click Share.
3. Select a user, and then select Can Manage.
4. Read the prompt. If you agree, click Confirm.

To transfer ownership by using the REST API, see Transfer ownership of a query.

If the query previously had an owner, that user no longer has the Can Manage permission on the query, and the user to whom you gave the Can Manage permission is now the owner.

SQL editor tools

Schema browser

To toggle the schema browser, press Alt/Option + D or click the pane handle between the schema browser and query pane.

Enable and disable tabbed editor

When the tabbed editor is enabled you can easily switch between multiple queries. To enable and disable the tabbed editor, click the Tabbed Editor toggle on the Queries page:

Auto Complete

The SQL editor has Auto Complete, which makes writing queries faster. Auto Complete can complete schema tokens, query syntax identifiers (like SELECT and JOIN), and the titles of query snippets.

Auto Complete is enabled by default unless your database schema exceeds five thousand tokens (tables or columns).

• To disable Auto Complete, press Ctrl + Space or click the button beneath the SQL editor:
• To enable Auto Complete, press Ctrl + Space or click the button beneath the SQL editor.

Configure query permissions

To configure who can manage and run queries, see Query access control.

Access admin view

A Databricks admin user has view access to all queries. In this view, an admin can view and delete any queries. However, an admin can’t edit a query if it is not shared with the admin.

To view all queries:

1. Click Queries in the sidebar.
2. Click Admin View on the right panel.

Transfer ownership of a query

An administrator can transfer ownership of a query. See Transfer ownership of a query.