Visualize queries and create a legacy dashboard

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.

Important

• Databricks recommends using AI/BI dashboards (formerly Lakeview dashboards). Earlier versions of dashboards, previously referred to as Databricks SQL dashboards are now called legacy dashboards. Databricks does not recommend creating new legacy dashboards.

• Convert legacy dashboards using the migration tool or REST API. See Clone a legacy dashboard to an AI/BI dashboard for instructions on using the built-in migration tool. See Dashboard tutorials for tutorials on creating and managing dashboards using the REST API.

Connect to Databricks SQL with SQL editor

1. Click New in the sidebar and select Query.

   The SQL editor opens.

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.

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

   

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.

   

6. Click Save.

   The saved chart displays in the SQL editor.

Query for daily fare trends

1. In SQL editor, click + and then click Create new query.

2. In the new query window, paste the following query to return the daily fare trends.

    SELECT
     T.weekday,
     CASE
       WHEN T.weekday = 1 THEN 'Sunday'
       WHEN T.weekday = 2 THEN 'Monday'
       WHEN T.weekday = 3 THEN 'Tuesday'
       WHEN T.weekday = 4 THEN 'Wednesday'
       WHEN T.weekday = 5 THEN 'Thursday'
       WHEN T.weekday = 6 THEN 'Friday'
       WHEN T.weekday = 7 THEN 'Saturday'
       ELSE 'N/A'
     END AS day_of_week,
     T.fare_amount,
     T.trip_distance
   FROM
     (
       SELECT
         dayofweek(tpep_pickup_datetime) as weekday,
         *
       FROM
         `samples`.`nyctaxi`.`trips`
     ) T
   

3. Click Save and save the query as Daily fare to distance analysis.

Create a visualization for daily fare trends

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

   The visualization editor displays.

2. In the Visualization Type drop-down, select Scatter.

3. Change the visualization name to Fare by distance.

4. On the General tab, set the value for the X column to trip_distance and set the value for the Y columns to fare_amount.

5. In the Group by drop-down, set the value to day_of_week.

6. On the X axis tab, set the Name value to Trip distance (miles).

7. On the Y axis tab, set the Name value to Fare Amount (USD).

8. Click Save

   The saved chart displays in the SQL editor.

   

Create a dashboard using these visualizations

1. Click New in the sidebar and select Legacy 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.

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

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

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

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

   

9. Click Add to legacy dashboard.

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

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

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

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

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

14. Click Add to legacy dashboard.

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

16. Click Done Editing.

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.

   

2. Click the kebab menu for this dashboard and then click Edit.

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

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

   

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

   

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.

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

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

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

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

11. Click OK and then click Save.

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

12. Click Done editing.

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