Query datapoints in Analytics
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:
Table name |
---|
query_battery |
query_bitset |
query_health |
query_json |
query_numeric |
query_numeric_set |
query_text |
Universal parameters
All data tables can be queried by the following parameters:
Parameter | Description |
---|---|
organization_id | Organization ID related to this datapoint. |
device_id | Device ID related to this datapoint. |
name | Name of the stream associated with this datapoint. |
tags | Tags associated with this device or datapoint, used for filtering. |
time | Timestamp associated with this datapoint. |
The value
parameter
value
parameterEach table has a unique format for the value
parameter, which represents the data structure of that stream's datapoints:
Table name | value parameter format |
---|---|
query_battery | query_battery does not have a value parameter, but instead has the following parameters:- percentage : number between 0 and 100 - voltage : number - charge : number |
query_bitset | Key-value pair of the form: Map(String, Bool) |
query_health | query_health does not have a value parameter, but instead has a status parameter of type string . |
query_json | string containing JSON data. |
query_numeric | number |
query_numeric_set | Array of: - label : string - value : number - unit : string Map(String,Tuple(value Float64, unit String)) |
query_text | string |
Parsing JSON data
When using the
query_json
table, thevalue
parameter will be a JSON object. You can use the ClickHouseJSONExtract
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 useJSONExtractInt( value, "radius" )
to query only theradius
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 name
column is filtered for a given stream name.
For example, consider the following query_numeric
table:
organization_id | device_id | name | tags | time | value |
---|---|---|---|---|---|
org a | device 1 | berry_radius | berry-stats | 12:00 PM | 12.2 |
org a | device 1 | berry_diameter | berry-stats | 12:00 PM | 28.8 |
org a | device 1 | pick_duration | berry-stats | 12:01 PM | 2.2 |
org a | device 1 | pick_duration | berry-stats | 12:02 PM | 1.7 |
org a | device 1 | berry_radius | berry-stats | 12:02 PM | 14.5 |
org a | device 1 | berry_diameter | berry-stats | 12:03 PM | 25.9 |
org a | device 1 | pick_duration | berry-stats | 12:03 PM | 5.3 |
org a | device 1 | berry_radius | berry-stats | 12:05 PM | 10.7 |
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:
organization_id | device_id | name | tags | time | value |
---|---|---|---|---|---|
org a | device 1 | berry_radius | berry-stats | 12:00 PM | 12.2 |
org a | device 1 | berry_radius | berry-stats | 12:02 PM | 14.5 |
org a | device 1 | berry_radius | berry-stats | 12:05 PM | 10.7 |
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
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].
Updated 9 days ago