Within the custom SQL editor in Formant Analytics, you can view and query datapoints and streams in your organization.

This guide will walk you through the table structure for stream data in Formant Analytics, and teach you how to query stream data by the available parameters.

Stream table structure explained

Data stream types

Data streams in Formant are categorized in the following way:

Universal parameters

All data tables can be queried by the following parameters:

The value parameter

Each table has a unique format for the value parameter, which represents the data structure of that stream's datapoints:

📘

Parsing JSON data

When using the query_json table, the value parameter will be a JSON object. You can use the ClickHouse JSONExtract functions to navigate the JSON structure and extract specific values of various data types.

For example, if you want to query and plot an integer radius from your JSON object, you would use JSONExtractInt( value, "radius" ) to query only the radius column.

For more information, see ClickHouse: json-functions.

Example: Query a numeric stream

In the context of Formant Analytics, we can think of a stream's data as the value column of a table when the namecolumn is filtered for a given stream name.

For example, consider the following query_numeric table:

The query_numeric table contains all stream data for numeric streams in org a. To query the data for the stream berry_radius, we filter query_numeric by name berry_radius for the following result:

The above structure for stream names applies for all tables with a name and value field.

Example: Query by tags

All datapoint tables have a tags column, so we can query by tags.

For example, suppose we want to track the amount of time lost due to mechanical problems with a robot. We already have a tag key called Time Lost, and the value for that tag is the number of minutes lost to a mechanical issue. This value is assigned within an annotation.

A very simple query might look like this:

SELECT
   device_id AS device,
   tags ['Time Lost']
FROM
   query_annotation
WHERE
   query_annotation.message = 'Task completion delayed due to mechanical issue.'

In a more advanced case, you might want to sum Time Lost by device ID. We'll convert the values for Time Lost into unsigned integers, and then group the result by device:

SELECT
   device_id AS device,
   sum(
      CAST(
         SUBSTRING_INDEX(tags ['Time Lost'], ' ', 1) AS UNSIGNED
      )
   ) AS time_lost
FROM
   query_annotation
WHERE
   query_annotation.message = 'Task completion delayed due to mechanical issue.'
GROUP BY
   device;

See also

• Ingesting telemetry data
• Telemetry vs. realtime data streams
• How telemetry streams work

👋

If you notice an issue with this page or need help, please reach out to us! Use the 'Did this page help you?' buttons below, or get in contact with our Customer Success team via the Intercom messenger in the bottom-right corner of this page, or at [email protected].