Tables

In a SQL query you can choose your data return format, which columns to return, and in what order by modifying your query. Databricks SQL table visualizations also allow you to manually reorder, hide, and format data. This article describes how you can control data presentation in table visualizations.

Click the Edit Visualization button under the table view. The visualization editor displays:

Visualization editor

You can:

  • Reorder columns by dragging them up or down using the Column Handle handle
  • Hide columns by toggling the Visibility Icon icon
  • Format columns using the format settings

Format columns

Databricks SQL allows you to format some common data types –text, numbers, dates, and Booleans–and it has special support for non-standard column types like images, JSON documents, and links. To display type-specific formatting options, select the type in the Display as field.

Conditionally format column colors

You can configure the font colors for column results to a static color or a range of colors based on comparing the column value to a threshold.

  1. Edit the visualization.

  2. Optionally, set the default font color to a non-default value.

  3. Under Font Conditions, click + Add condition.

  4. Select the column, the threshold, the comparator, and the font color if the comparison succeeds.

    The threshold can be a numeric type, string, or date. For the comparison to succeed, the threshold must be the same data type as the column. For example, to colorize results whose values exceed the numeric value 500000, create the threshold > 500000, rather than > 500,000. Numeric types, strings, and dates are supported.

  5. Optionally add more conditions.

Common data types

If a query result does not provide type information Databricks SQL renders a column as text. You can force it to use arbitrary types. For example, you can:

  • Display floats out to three decimal places
  • Show only the month and year of a date column
  • Zero-pad integers
  • Prepend or append text to your number fields

Text

The Allow HTML content field has the following behavior:

  • Enabled: Unsafe HTML content from the column is removed and the column is rendered as HTML.
  • Disabled: The content is displayed without rendering the HTML.

Numeric and date-time

For reference information on formatting numeric and date and time data types, see:

Special data types

Databricks SQL supports the following special data types: image, JSON, and link.

Image

If a field in your database contains links to images, select Image to display the images inline with your table results. This is especially useful for dashboards. In the following dashboard, the Customer Image field is a link to an image that Databricks SQL displays in-place.

Dashboard with images

JSON

If your data returns JSON formatted text, select JSON. This lets you collapse and expand elements in a clean format.

To make HTML links from your dashboard clickable, select Link. Three fields appear: URL template, Text template, Title template. These template fields control how an HTML link is rendered. The fields accept mustache-style parameters (double curly braces) for the column names in the table. The mustache parameters support the pipe filter urlEscape, which URL-escapes the value.

Example

Suppose you have the following SQL query:

select 1 as n, 'title/tt7888964' as path, 'query with spaces' as q, '4/15/2021' as d
union all
select 2, 'title/tt0293429/', 'query & percent 100%', '8/1/2022'
union all
select 3, 'name/nm1212722', '!@#$%^&*()_+', '7/9/1967'

and you want to generate a link to IMDB. You would set the URL template field to:

https://www.imdb.com/{{ path }}?escapedQueryExample={{ q | urlEscape }}&nonEscapedQueryExample={{ q }}&escapedDate={{ d | urlEscape }}