Visualization in Databricks SQL

Databricks has built-in support for charts and visualizations in both Databricks SQL and in notebooks. This page describes how to work with visualizations in Databricks SQL. For information about using visualizations in notebooks, see Visualizations in Databricks notebooks.

To view available visualuzation types, see visualization types.

Create a visualization

1. Run the following query in the SQL editor. Enter 10001 as the parameter in the widget below the query.

   USE CATALOG samples;
   SELECT
      hour(tpep_dropoff_datetime) as dropoff_hour,
      COUNT(*) AS num
   FROM samples.nyctaxi.trips
   WHERE pickup_zip IN ({{pickupzip}})
   GROUP BY 1
   

   

2. After running a query, in the Results panel, click + and then select Visualization.

3. Enter a visualization name, such as Dropoff rates, as the new title in the visualization editor.

4. In the Visualization Type drop-down, choose Bar.

5. Review the visualization properties.

   

6. Click Save.

Edit a visualization

To edit a visualization:

• Click the downward pointing arrow in the visualization tab. Then, click Edit.

• Use the tabs in the Visualization Editor to access and edit different parts of the chart.

Clone a visualization

To clone a visualization:

1. Open the visualization in the SQL editor.

2. Click the downward pointing arrow in the visualization tab. Then click Duplicate.

Enable aggregation in a visualization

For bar, line, area, pie, and heatmap charts, you add aggregation directly in the visualization rather than modifying the query to add an aggregation column. This approach has the following advantages:

• You don’t need to modify the underlying SQL.

• Modifying the aggregation allows you to quickly perform scenario-based data analysis.

• The aggregation applies to the entire dataset, not just the first 64,000 rows displayed in a table.

Aggregation is available in the following visualizations:

• Line

• Bar

• Area

• Pie

• Heatmap

• Histogram

Aggregations do not support combination visualizations, such as displaying a line and bars in the same chart. To create a new combination chart, clone a legacy visualization.

Table visualizations display only the first 64,000 rows.

To aggregate Y-axis columns for a visualization:

1. From the SQL editor, create a new visualization or edit an existing one.

   If you see the message This visualization uses an old configuration. New visualizations support aggregating data directly within the editor, you must re-create the visualization before you can use aggregation.

2. Next to the Y-axis columns, select the aggregation type from the following for numeric types:

   • Sum (the default)

   • Average

   • Count

   • Count Distinct

   • Max

   • Min

   • Median

   Or from the following for string types:

   • Count

   • Count Distinct

   Your changes are applied to the preview of the visualization.

3. Click Save.

4. The visualization shows the number of rows that it aggregates.

In some cases, you may not want to use aggregation on Y-axis columns. To turn off aggregation, click the kebab menu next to Y columns and uncheck Use aggregation.

Temporarily hide or show only a series

To hide a series in a visualization, click the series in the legend. To show the series again, click it again in the legend.

To show only a single series, double-click the series in the legend. To show other series, click each one.

Colors

Chart visualizations feature default colors selected for aesthetics and readability.

You can customize a visualization’s colors when you create the visualization or by editing it.

1. Create or edit a visualization.

2. Click Colors.

3. To modify a color, click the square and select the new color by doing one of the following:

   • Click it in the color selector.

   • Enter a hex value.

4. Click anywhere outside the color selector to close it.

5. Click Save in the Visualization Editor to save the changes.

Download a visualization as a CSV, TSV, or Excel file

To download a visualization as a CSV, TSV, or Excel file, click the downward-facing arrow next to the visualization name and select the type of download desired. If the visualization uses aggregations, the downloaded results are also aggregated. The downloaded results are from the most recent query run that created the visualization.

Download a chart visualization as a PNG file

To download a visualization as a PNG file, hover over the canvas and click the download icon in the upper-right corner.

A PNG file is downloaded to your device.

Explore chart data

Use the following features to help analyze chart data.

Series selection

To select a specific series to analyze on a chart, use the following commands:

• Click on a single legend item to select that series

• Cmd/Ctrl + click on a legend item to select or deselect multiple series

Sorted tooltips

Use tooltips on line and unstacked bar charts, ordered by magnitude, for quick analysis.

Zoom

For data-dense charts, zooming in on individual data points can be helpful for investigating details and cropping outliers. To zoom in on a chart, click and drag on the canvas. To clear the zoom, hover over the canvas and click the Clear zoom button in the upper right corner of the visualization.

Add a visualization to a dashboard

1. Click the kebab menu button beneath the visualization.

2. Select + Add to Dashboard.

3. Enter a dashboard name. A list of matching dashboards appears.

4. Select a dashboard.

   

5. Click OK. A pop-up appears with a link to the dashboard.

   

To learn about editing visualizations on a dashboard, see Visualizations.

Legacy visualizations

The latest version of chart visualizations is on by default. The settings in this section describe legacy visualization that you might encounter when working with an older chart or if you have the latest version turned off.

Visualization tools

If you hover over the upper-right of a chart in the visualization editor, a Plotly toolbar appears where you can perform operations such as select, zoom, and pan.

If you do not see the toolbar, your administrator has disabled toolbar display.

Custom color palettes

Note

By default, if a legacy dashboard uses a custom color palette, visualization color choices are ignored. To override this setting, see Use a different color palatte for a visualization.