Tables

For data sources that support a native query syntax (SQL or NOSQL), you can choose your data return format, which columns to return, and in what order by modifying your query. But sources like CSV files or Google Sheets don’t support a query syntax. So SQL Analytics allows you to manually reorder, hide, and format data in your table visualizations.

Visualization settings

To get started, click the Edit Visualization button under the table view. A settings panel appears that looks like:

Visualization options

You can:

  • Reorder Columns by dragging them up or down using the handle highlighted yellow
  • Hide Columns by toggling the Visibility Icon icon highlighted green
  • Format Columns using the format settings highlighted red

Format columns

SQL Analytics is sensitive to the data types that are common to most databases: text, numbers, dates, and Booleans. But it also has special support for non-standard column types like JSON documents, images, and links.

Note

SQL Analytics sanitizes HTML in query results. But if any HTML tags remain they are not escaped by default. Thus you may see odd effects if a query result includes string fields that include HTML (for example, from a web scraper). Toggle the Allow HTML content setting in the visualization editor to escape HTML characters.

Common data types

SQL Analytics renders a column as text if your underlying data source does not provide type information. You can force it to use arbitrary types using the table visualization editor. This is especially useful for sources like SQLite, Google Sheets, or CSV files where type data is not available. You can, for example:

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

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

Special data types

SQL Analytics also supports data types outside the common database specifications.

  • JSON document: If your underlying data returns JSON formatted text in a field, you can instruct SQL Analytics to display it as such. This lets you collapse and expand elements in a clean format.

  • Image: If a field in your database contains links to an image, SQL Analytics can display that image inline with your table results. This is especially useful for dashboards.

    Dashboard with images

    In the preceding dashboard, the Customer Image field is a URL to a picture which SQL Analytics displays in-place.

  • HTML link: Just like with images, HTML links from your dashboard can be made clickable. Just use the Link option in the column format selector.