Query tasks

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.

    Sidebar onboarding label showing 1 of 3 tasks completed
  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.

    Results of your first query

View and organize queries

Queries can be viewed in one of two ways:

  • New queries can now be viewed in the workspace browser by clicking Workspace Icon Workspace in the sidebar. These queries are viewable, by default, in the Home folder. Users can organize queries into folders in the workspace browser along with other Databricks objects. To view and organize currently existing queries, users (or admins) must migrate them into the workspace browser. For information on the Databricks SQL workspace browser, including migration, see Databricks SQL Workspace browser.

  • All queries, regardless of creation time or migration status, can also be viewed by clicking the Queries Icon Queries in the sidebar.

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

Organize queries into folders in the workspace browser

You can now organize new and existing queries into folders in the workspace browser along with other Databricks objects. For more information, see [_](../workspace-browser/index.md.

Filter the list of saved queries

You can filter the list of all 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 Icon Create in the sidebar and select Query.

    • In the sidebar, click Queries Icon Queries and then click + Create Query.

    • In the sidebar, click Workspace Icon Workspace and then click + Create Query.

    The SQL editor displays.

  2. In the data warehouses drop-down list, select a SQL warehouse. To filter the list, enter text in the text box.

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

    The icon next to the SQL warehouse indicates the status:

    • Running Running

    • Starting Starting

    • Stopped Stopped

    Note

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

You can also create a query with the Databricks Terraform provider and databricks_sql_query. You can create a visualization for a query with databricks_sql_visualization. You can create a sample dashboard with queries by using dbsql-nyc-taxi-trip-analysis.

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

  • You must select a running SQL warehouse.

    • 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 Refresh Schema Icon.

      Refresh schema

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

  1. Click a table to shows its columns.

    Table 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 right of a data object to insert the object into the SQL editor.

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

    Autocomplete alias
  • When you are done editing, click Save.

Edit multiple queries

By default, the SQL editor uses tabs so you can edit multiple queries at the same time.

  • Click Add Tab Icon.

  • Click Open existing query to see your list of saved queries.

    Queries Dialog
    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 Open.

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.

To disable the tabbed editor, open Queries in the sidebar and click the toggle next to Tabbed editor.

Execute a query

To execute a query:

  1. Select a SQL warehouse.

  2. Specify a query in the SQL editor.

  3. Press Ctrl/Cmd + Enter or click (Run All limit 1000).

    Execute query

Note

  • If a warehouse is stopped and you execute a query, the warehouse is started. To manually start a warehouse, follow the steps in Start a warehouse.

  • 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 toggle the checkbox and modify the LIMIT clause in your query.

The query result displays in the Table tab.

Execute query result

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 kebab menu Vertical Ellipsis next to the query and click Edit query info.

    Query info
  2. In Description, enter a description and then click Save.

Save a query

To save a query, press Ctrl/Cmd + S or click Save.

Revert to a saved query

When you edit a query, a Revert changes option appears in the context menu for the query.

Revert link

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 kebab menu Vertical Ellipsis next to the query in 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 <Trash>.

  2. Click a query.

  3. Click the kebab menu Vertical Ellipsis at the top-right of the SQL editor and select Restore.

Permanently delete a query

  1. In the All Queries list, click <Trash>.

  2. Click a query.

  3. Click the context menu for the query and select Delete.

Copy a query

To create a copy of a query (created by you or someone else), click the context menu for the query and select Clone:

Clone query

View query info

To view when a query was created or updated, click the Vertical Ellipsis next to the query and click Edit query info:

Query info

View past executions of a query

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

Past executions

This tab does not show scheduled executions.

Download a query result

You can download a query result as a CSV, TSV, or Excel file. Users can now download up to approximately 1GB of results data from Databricks SQL in CSV and TSV format, and up to 64,000 rows to an Excel file.

  1. Click the kebab menu Vertical Ellipsis beneath the results pane.

  2. Select Download as [CSV | TSV | Excel] File.

Download query result

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

Transfer ownership of a query

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

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 Icon Queries in the sidebar.

  2. Click Admin View on the right panel.

SQL editor tools

Schema browser

To toggle the schema browser, press Alt/Option + D or click the pane handle 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:

Tabbed editor toggle

Live autocomplete

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

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

  • To disable live autocomplete, press Ctrl + Space or click Auto Complete Enabled beneath the SQL editor:

  • To enable live autocomplete, press Ctrl + Space or click Auto Complete Disabled beneath the SQL editor.