Visualize queries and create a dashboard in Databricks SQL

This tutorial uses the New York City taxi dataset in Samples. It shows you how to use SQL editor in Databricks SQL to create a visualization for each of several queries and then create a dashboard using these visualizations. It also shows you how to create a dashboard parameter for each of the visualizations in the dashboard.

Connect to Databricks SQL with SQL editor

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

    The SQL editor opens. If you do not have access to Databricks SQL, request access to from an admin.

  2. Select a warehouse.

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

  1. Click Starter Warehouse. This warehouse is created for you automatically to help you get started quickly. For information on creating SQL warehouses, see Create a SQL warehouse.

Query for pickup hour distribution

  1. In SQL editor, paste the following query in the new query window to return the distribution of taxi pickups by hour.

    SELECT
    date_format(tpep_pickup_datetime, "HH") AS `Pickup Hour`,
    count(*) AS `Number of Rides`
    FROM
    samples.nyctaxi.trips
    GROUP BY 1
    
  2. Press Ctrl/Cmd + Enter or click Run (1000). After a few seconds, the query results are shown below the query in the results pane.

    Limit 1000 is selected by default for all queries to ensure that the query returns at most 1000 rows. If a query is saved with the Limit 1000 setting, this setting applies to all executions of the query (including within dashboards). If you want to return all rows for this query, you can unselect LIMIT 1000 by clicking the Run (1000) drop-down. If you want to specify a different limit on the number of rows, you can add a LIMIT clause in your query with a value of your choice.

    The query result displays in the Results tab.

  3. Click Save and save the query as Pickup hour.

    Results of your first query nyc taxi query

Create a visualization for the distribution of taxi pickups by hour.

  1. Next to the Results tab, click + and then click Visualization.

    The visualization editor displays.

  2. In the Visualization Type drop-down, verify that Bar is selected.

  3. Change the visualization name to Bar chart.

  4. Verify that Pickup Hour is specified for the Y column drop down.

  5. Verify that Number of Rides and Sum are specified for the X column drop down.

    Pickup hour distribution
  6. Click Save.

    The saved chart displays in the SQL editor.

Create a dashboard using these visualizations

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

  2. Set the dashboard name to NYC taxi trip analysis.

  3. Click Save.

  1. In the Choose warehouse drop-down list, select Starter Warehouse.

  1. In the Add drop-down list, click Visualization.

  2. In the Add visualization widget window, select the Daily fare to distance analysis query.

  3. In the Select existing visualization list, select Fare by distance.

  4. In the Title text box, enter Daily fare trends.

    Add visualization widget
  5. Click Add to Dashboard.

    The Daily fare trends visualization appears on the dashbard design surface.

  6. In the Add drop-down list to add a second widget to the dashboard, and then click Visualization.

  7. In the Add visualization widget window, select the Pickup hour query.

  8. In the Select existing visualization list, select Bar chart.

  9. In the Title text box, enter Pickup hour distribution.

  10. Click Add to Dashboard.

  11. Resize this visualization to match the width of the first visualization in the dashboard.

  12. Click Done Editing.

Initial dashboard

Add a pickup zip code parameter to each query

  1. In SQL editor, open the Daily fare to distance analysis query.

  2. Add the following WHERE clause to the Daily fare to distance analysis query to filter the query by pickup zip code.

    WHERE
      pickup_zip IN ({{ pickupzip }})
    
  3. In the pickupzip text box, enter 10018 and then click Apply changes to execute the query with the pickup zip code parameter.

  4. Click Save.

  5. Open the Pickup hour query.

  6. Add the following WHERE clause to the Pickup hour query to filter the query by the pickup zip code. Add this clause before the GROUP BY clause.

       WHERE
         pickup_zip IN ({{ pickupzip }})
    
  7. In the pickupzip text box, enter 10018 and then click Apply changes to execute the query with the pickup zip code filter.

  8. Click Save.

Update the dashboard to use a dashboard parameter

  1. Open the NYC taxi trip analysis dashboard.

    Each of the visualizations now includes a parameter for the pickup zip code.

    Widget - parameters
  2. Click the kebab menu Vertical Ellipsis for this dashboard and then click Edit.

  3. Click the kebab menu Vertical Ellipsis for Daily fare trends visualization and then click Change widget settings.

  4. In the Parameters section, click the pencil icon Edit icon for the Widget parameter in the Value field.

    View widget parameters
  5. In the Edit source and Value window, change the Source to New dashboard parameter.

    Change widget parameters to new dashboard parametes
  6. Click OK and then click Save.

    The pickupzip dashboard parameter appears and the widget parameter for the Daily fare trends visualization no longer appears.

    1. Click the kebab menu Vertical Ellipsis for Pickup hour distribution visualization and then click Change widget settings.

  7. In the Parameters section, click the pencil icon Edit icon for the Widget parameter in the Value field.

  8. In the Edit source and Value window, change the Source to Existing dashboard parameter.

  9. Verify that pickupzip is selected as the Key value.

  10. Click OK and then click Save.

    The widget parameter for the Pickup hour distribution visualization no longer appears.

  11. Click Done editing.

  12. Change the value of the pickupzip dashboard parameter to 10017 and then click Apply changes.

    The data in each of the vizualizations now displays the data for pickups in the 10017 zip code.

    Change widget parameters to new dashboard parameters