Querying datapoints in Analytics
Within the custom SQL editor in Formant Analytics, you can view and query datapoints and streams in your organization.
![Viewing available data point parameters to query in the custom SQL editor.](https://files.readme.io/16fe70b-Screenshot_2024-07-09_at_5.22.31_PM.png)
Viewing available datapoint parameters to query in the custom SQL editor.
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 |
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.
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 about 2 months ago