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. — 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

Each 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 namecolumn 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.

Querying datapoints in Analytics

Stream table structure explained

Data stream types

Universal parameters

The `value` parameter

Example: Query a numeric stream

See also

👋

Stream table structure explained

Data stream types

Universal parameters

The value parameter

Example: Query a numeric stream

See also

👋

The `value` parameter