Time series query syntax
When calling an Analytics API endpoint, the time-series query is represented as a structured input such as a JSON object, rather than a DSL string. While this is not as expressive as using a parsed SQL DSL string it has several advantages:
The query syntax is directly specified in the OpenAPI specification as the only documentation of the API. There is no need to maintain another form of input specification for the query syntax. Furthermore, injecting malicious SQL is much harder as parsing and sanitizing requests are much simpler.
Query OpenAPI schema
To query the /{customer_id}/analytics/query endpoint the OpenAPI 3 schema of the request is the following TimeSeriesQuery:
openapi: 3.0.3
components:
schemas:
TimeSeriesQuery:
type: object
required:
- range_start
- range_end
- select
- resolution
properties:
range_start:
description: "TheiInclusive oldest time of the data from an ISO 8601 notation. If a UTC offset is given, the responded timestamps will also be in local time, using this parameter's offset."
type: string
format: date-time
example: "2021-01-08T08:14:52+0000"
range_end:
description: "The inclusive newest time of the data from an ISO 8601 notation. If a UTC offset is given, the responded timestamps will be also in local time, using the offset provided via range_start"
type: string
format: date-time
example: "2021-01-08T16:14:52+0000"
resolution:
description: "The time resolution to bin the data into equally sized buckets: Must be a positive integer followed by s,m,h,d for the second, minute, hour, and day. Additionally, 1w and 1M (one week and one month) are also supported."
type: string
pattern: '^\d+(s|m|h|d)|1w|1M$'
example: "1h"
select:
description: "The list of selected clauses determining what data columns to retrieve."
type: array
items:
$ref: "#/components/schemas/SelectClause"
minLength: 1
maxLength: 5
where:
description: "The list of filtering clauses determining the data records to retrieve the data from. Implicitly filled with the range_start and range_end even if unset otherwise."
type: array
items:
$ref: "#/components/schemas/FilterClause"
minLength: 0
maxLength: 5
groupby:
description: "The list of columns to group the data by, additional to the implicit binned time resolution. Results in one time series per column value."
type: array
items:
type: string
minLength: 0
maxLength: 1
order:
description: "Sort order"
type: string
enum:
- ASC
- DESC
default: DESC
orderby:
description: "Sorting fields. The results are always ordered by the implicit time first. Then we sort by the order of fields supplied here."
type: array
items:
type: string
minLength: 0
maxLength: 5
SelectClause:
type: object
required:
- name
properties:
name:
description: "Name of the column (measure, attribute or function) to select."
type: string
example: "measure_value::varchar"
aggregate:
description: "Aggregation function to use."
type: string
enum:
- avg
- count
- sum
example: "count"
alias:
description: "Name to map the column to for convenience."
type: string
example: "scans"
FilterClause:
type: object
required:
- name
- value
properties:
name:
description: "The name of the column (measure, attribute) to compare. Must be a string value for now"
type: string
example: "measure_name"
operator:
description: "Comparison operator. Must be '=' for now."
type: string
value:
description: "Value to compare against."
type: string
example: "scan_code"Response OpenAPI schema
The obtained response TimeSeries consists of a XAxis listing the labels of each reported time in the series and the time series data itself: A list with one element for each XAxis element which itself is a list of all the selected fields.
components:
schemas:
TimeSeries:
type: object
description: "Time series data"
required:
- xaxis
- series
properties:
xaxis:
$ref: "#/components/schemas/XAxis"
series:
type: array
items:
$ref: "#/components/schemas/SeriesCollection"
warning:
type: string
description: "An optional warning, displayed if something is wrong or when an ambiguity is removed."
example: "Different UTC time offsets for the start and end time range detected. Reporting with applied start time range's offset."
XAxis:
type: object
description: "Time series data X-axis values: Either the binned times or other grouping selectors"
required:
- categories
- type
properties:
type:
type: string
example: "datetime"
categories:
type: array
items:
type: string
example: "2021-01-08 10:00:00.000000000"
SeriesCollection:
description: "The list of the time-series."
type: array
items:
$ref: "#/components/schemas/Series"
Series:
description: "The time-series data values matching the X-Axis positions and the selected clauses."
type: object
required:
- data
- name
properties:
data:
type: array
items:
type: string
description: "The requested data."
example: "9"
name:
type: string
description: "The name of the series. Corresponds to the first selected name or the value of the groupby attribute, if one is provided."
example: "scans"Example queries
Retrieve the number of scans for a use case in time windows of one hour grouped by the used scanner device:
{
"range_start": "2021-02-20T00:00:00+0000",
"range_end": "2021-02-22T23:59:59+0000",
"resolution": "1h",
"select": [
{"name": "measure_value::varchar", "aggregate": "count", "alias": "scans"},
{"name": "device_serial"}
],
"where": [
{"name": "measure_name", "operator": "=", "value": "scan_code"},
{"name": "gateway_l2_id", "operator": "=", "value": "b2a931"}
],
"groupby": ["device_serial"]
}Summarizes the number of steps in one day for a specific use case in all locations:
{
"range_start": "2021-01-08T00:00:00+0000",
"range_end": "2021-01-08T23:59:59+0000",
"select": [
{"name": "measure_value::double", "aggregate": "sum", "alias": "steps"},
],
"where": [
{"name": "measure_name", "operator": "=", "value": "metrics_worker_steps"},
{"name": "customer_l3_name", "operator": "=", "value": "picking"},
],
"resolution": "1h"
}Retrieves the overhead of acquiring a successful scan measured by the combined durations of triggering the scan engine preceding a successful scan for a whole day:
{
"range_start": "2021-01-08T00:00:00+0000",
"range_end": "2021-01-08T23:59:59+0000",
"select": [
{"name": "measure_value::double", "aggregate": "sum", "alias": "activations"},
],
"where": [
{"name": "measure_name", "operator": "=", "value": "trigger_effort_bce_activation_total"},
],
"resolution": "1h"
}