Quickstart: Run and visualize a query

This quickstart shows you how to query a table of people to find the number of women named Mary grouped by birth year, and visualize the result.

The columns of the table are id, firstName, middleName, lastName, gender, birthDate, ssn, and salary.

This article shows how to use the Databricks SQL UI and the BI tools Power BI and Tableau Online to do the same query.

Requirements

Your Databricks SQL admin must complete the Databricks SQL admin quickstart before you complete the steps in the following sections.

Query a table and create a visualization using the Databricks SQL UI

Use the sidebar

You can access all of your Databricks assets using the sidebar. The sidebar’s contents depend on the selected persona: Data Science & Engineering, Machine Learning, or SQL.

  • By default, the sidebar appears in a collapsed state and only the icons are visible. Move your cursor over the sidebar to expand to the full view.

  • To change the persona, click the icon below the Databricks logo Databricks logo, and select a persona.

    change persona
  • To pin a persona so that it appears the next time you log in, click pin persona next to the persona. Click it again to remove the pin.

  • Use Menu options at the bottom of the sidebar to set the sidebar mode to Auto (default behavior), Expand, or Collapse.

Switch to a different workspace

If you have access to more than one workspace in the same account, you can quickly switch among them.

  1. Click Account Icon in the lower left corner of your Databricks workspace.
  2. Under Workspaces, select a workspace to switch to it.

Step 1: Log in to Databricks SQL

When you log in to Databricks SQL your landing page looks like this:

User landing page

If the Databricks Data Science & Engineering or Databricks Machine Learning environment displays, use the sidebar to switch to Databricks SQL.

Step 2: Query the people table

In Quickstart: Set up a user to query a table, an admin created a table named people10. In this section, you run a query on this table for the birthdays and birth years of all people named Mary.

  1. Click Create Icon Create in the sidebar and select Query.

    The query editor displays.

  2. In the box below New Query, click the Down Arrow Icon icon and select Starter Endpoint.

    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 endpoint is selected.

  3. In the box below the endpoint, click the Down Arrow Icon icon and if not selected, select the default database.

    Default database
  4. Paste in a SELECT statement that queries the number of women named Mary:

     SELECT year(birthDate) as birthYear, count(*) AS total
     FROM default.people10m
     WHERE firstName = 'Mary' AND gender = 'F'
     GROUP BY birthYear
     ORDER BY birthYear
    
  5. Press Ctrl/Cmd + Enter or click the Execute button.

    The Limit 1000 checkbox is selected by default to ensure that the query returns at most 1000 rows. If you want more rows, you can unselect the checkbox and specify a LIMIT clause in your query. The query result displays in the Table tab.

    Query result

Step 3: Create a visualization

  1. Click the + Add Visualization tab.

    The visualization editor displays.

    Visualization editor
  2. In the X Column drop-down, select birthYear.

  3. In the Y Column drop-down, select total.

  4. Click the X Axis tab.

  5. In the Name field, enter Birth Year.

  6. Click the Y Axis tab.

  7. In the Name field, enter Number of Marys by Birth Year.

  8. Click Save.

    The saved chart displays in the query editor.

    Marys SQLA chart

Step 4: Create a dashboard

  1. Click Create Icon Create in the sidebar and select Dashboard.

  2. Enter People.

  3. Click Save.

  4. Click the Add Widget button at the bottom right.

  5. Select Marys.

  6. Click Add to Dashboard.

  7. Click Done Editing.

    Complete dashboard

Next steps

  • View some sample dashboards that provide a starting point for rich visualizations and usable layout by doing one of the following:
    • Click View gallery at the bottom of the Dashboards page.
    • Go to https://<databricks-instance>/sql/dashboards/samples, replacing your <databricks-instance> with the Databricks workspace instance name, for example dbc-a1b2345c-d6e7.cloud.databricks.com.
  • Query a table and create a visualization using common BI tools.

Query a table and create a visualization using BI tools connected to Databricks SQL

This section shows how to collect Databricks SQL information, connect Power BI Desktop and Tableau Online to Databricks SQL, and in each BI tool, query a table of people and visualize the data.

Databricks SQL

This section describes how to collect endpoint connection information and generate a token that you use to authenticate your tool to Databricks SQL.

Step 1: Get SQL endpoint connection details

  1. Log in to Databricks SQL.

  2. Click Endpoints Icon SQL Endpoints in the sidebar.

  3. In the Endpoints list, type Starter in the filter box.

  4. Click the Starter Endpoint link.

  5. Click the Connection Details tab.

    Connection details
  6. Click Copy Icon to copy the Server Hostname and HTTP Path.

Step 2: Get a personal access token

The personal access token authenticates you to Databricks SQL.

  1. Click User Settings Icon Settings at the bottom of the sidebar and select User Settings.
  2. Click the Personal Access Tokens tab.
  3. Click + Generate New Token.
  4. Optionally enter a comment and modify the token lifetime.
  5. Click Generate.
  6. Click Copy Icon to copy the token and click OK.

Power BI

Step 1: Configure the connection from Power BI to Databricks SQL

  1. Select Get Data > More… > Azure and select the Azure Databricks connector.

    Databricks connector in Get Data list

    Note

    Although the label says Azure Databricks, the connector also works with Databricks on AWS.

  2. Click Connect.

  3. Enter the Server Hostname and HTTP Path you copied in Step 1: Get SQL endpoint connection details.

    Power BI data source
  4. Click OK.

  5. At the authentication prompt, select the Personal Access Token tab. Enter the personal access token you copied in Step 2: Get a personal access token.

  6. Click Connect.

    If the SQL endpoint is not running, it will start but may take a few minutes to become available.

  7. In the Power BI Navigator, select the default.people10m table.

  8. Click Load.

Step 2: Shape the data

Return the records of women with the first name Mary and compute the number of Marys in each year.

  1. Remove all columns except firstName, gender, and birthDate.
  2. Filter the data with firstName = Mary and gender = F.
  3. Change the type of birthDate to Date and remove all date components except year.
  4. Group by year to count the number of Marys per year.
Marys Power BI table

Step 3: Visualize the data

Marys Power BI chart

Tableau Online

Step 1: Configure the connection from Tableau Online to Databricks SQL

  1. In Tableau Online, open a workbook or create a new one.

  2. Select Data > New Data Source.

    Tableau Online data source
  3. In the Connect to Data dialog, select Connectors > Databricks.

    Tableau Online Databricks
  4. In the Databricks connection dialog, enter the Server Hostname and HTTP Path you copied in Step 1: Get SQL endpoint connection details.

    Tableau data source
  5. Authenticate using token as the username and the token from Step 2: Get a personal access token as the password.

  6. Click Sign in.

    If the SQL endpoint is not running, it will start but may take a few minutes to become available.

  7. In the Schema field, enter default. Press Enter.

  8. In the Select schema drop-down, select default.

  9. In the Table field, enter people10m. Press Enter.

  10. Drag the people10 table to the canvas.

Step 2: Shape the data

Return the records of women with the first name Mary and compute the number of Marys in each year.

  1. Remove all columns except firstName, gender, and birthDate.
  2. Filter the data with firstName = Mary and gender = F.
  3. Change the type of birthDate to Date and remove all date components except year.
  4. Group by year to count the number of Marys per year.
Marys Tableau table

Step 3: Visualize the data

Visualize as a bar chart:

Marys Tableau chart