Why a Real Time Query Language?
RTQL queries describe operations on realtime telemetry series that yield another realtime series as a result. The available operations are specifically designed for use in time series data - addition of two series (A + B) results in a series that is the sum of the latest value of “A” and “B”, updating when either series changes.
Traditional SQL-based historians do not see the time dimension as a fundamental aspect of telemetry data. To perform a simple task such as addition of two series, complex joins are required, especially when timestamps do not exactly match, and often further manual effort is needed to maintain performance on large datasets. Additionally, a query produces a point-in-time result, requiring polling.
RTQL provides a straightforward language for describing realtime series analysis, and an engine that ensures low-latency updates. This is used to provide ingestion to dashboard latencies that are typically bounded by network constraints, and measured in milliseconds.
RTQL In Brief
Query Statement
querySource expression* timeRestriction?
The most basic query is the name of a telemetry series. This will return the first value recorded, all historical values in order, and then stay open, providing live values as they arrive.
conveyor.speed
Time Restrictions
The time domain can be specified. If an end is specified then the result stream will terminate when the end is reached.
conveyor.speed between '2020-01-01' and '2020-01-02'
conveyor.speed after '2020-01-01'
The Grafana driver will automatically add time restrictions based on the current dashboard viewport.
Query Sources
A query source refers to either a series label or a calculation involving series. Series are streams of “double precision” floating point numbers. A value is considered “truthy” or true if it is above 0.5 - this affects logical operations, or when a logical value is needed (such as in “count”).
conveyor.speed
(conveyor.speed * weightometer.tonnes)
(conveyor.speed / 1000)
Algebraic operations are provided: + - * /, along with logical and relational operators | & < > <= >=. In addition, point-in-time aggregation operators can be applied to multiple series (sum, avg, latest, median, min, max, count) to simplify calculations.
avg(conveyor1.speed, conveyor2.speed, conveyor3.speed)
The series expansion operator allows wildcard expansion of a set of series labels (based on known series at time of query):
avg(...conveyor*.speed)
In all these cases, the resulting series represents the results of the calculation when applied to the latest value of each series at a given point in time. As each component changes, so does the output.
Expressions
Expressions modify the stream of data from a query source. They are used to perform rolling averages, filtering, smoothing, etc. Multiple expressions can be used in a query, and they are applied in sequence.
Expressions cooperate with date restriction clauses and will fetch extra data to satisfy the requested window. For example, a daily month-to-date sum for the second week of the month will still retrieve the first week’s data for calculations, even if this lies outside the date restriction.
Windowing Expressions
Windowing expressions divide up the timeline in various ways and provide a result using an aggregation function or reducer.
If cumulative is specified, the current result will be updated continuously. Otherwise, the result will only be emitted on the close of the window, reducing the number of points in a series. This can be useful for reducing update rates on complex visualisation (with eg: latest over 5 sec), or smoothing noise on erratic values (avg over 5 sec).
To perform “integration”, such as converting wattage measurements to total joules the antiderivative aggregation should be considered over sum. This treats each measurement as a continuous value until a replacement arrives, and ensures the calculation remains accurate with irregular measurements or periods partly outside the window.
Fixed Window
... ["cum[ulative]"] aggregation "over" timePeriod ["offset" timePeriod]
conveyor.speed avg over 5 mins
weightometer.tonnes cumulative sum over 1 month offset 2 weeks
Fixed windows quantize the timeline into time period chunks. By default, these are aligned on whole-period boundaries: ie “1 hour” results in 1am, 2am, 3am…, however offsetting can be used to tweak this behaviour.
Rolling Window (rolling)
... "rolling" aggregation "over" timePeriod
conveyor.speed rolling avg over 5 mins
This expression defines a rolling window from “now” until 5 minutes in the past. As values enter and exit this window from the source the aggregation is updated.
This can increase the number of data points, as the result may change as data both enters and exits the window. The cumulative keyword cannot apply to this window type.
Variable Window (until)
... ["cum[ulative]"] aggregation "until (" closingQuery ")" ["limit" timePeriod]
weightometer.tonnes sum until ( sensor.truckexit ) limit 1 hour
A window is held open until a closing query is truthy. It is optionally limited in duration to a limit preceding the closing query.
Filter Expressions
By Another Query (while)
... "while" "(" filterQuery ")"
outdoor.lightsensor while ( outdoor.lightswitch )
Passes values only while the filter query is truthy.
By Uniqueness (distinct)
... "distinct" ["epsilon" n]
bin.weight distinct epsilon 10.0
Suppresses minor changes in time series data. After a point is emitted, subsequent points will be suppressed until the difference exceeds the epsilon.
Utility Expressions
Threshold
... "threshold" n
motor.temperature threshold 55
Convert into a logical stream, passing 1 if greater than the threshold, 0 otherwise. Useful for monitoring exceptional conditions or conversion of noisy analogue values for use in until or logical operations.
Period
... "period"
sensor.truckexit period
Measures the period between truthy values in seconds.
Derivative (dx/dt)
... "dx/dt"
tank.liters dx/dt
Takes the derivative of the data stream with respect to time between points.
Stale
... "stale" timeSpan
unreliablemachine.power stale 2 sec
Allows specification of when values in a data stream become “stale”. Staleness indicates the period in which a new value is expected. If an updated value doesn’t arrive by this time, due to an offline sensor or network interruption, the last value is repeated and flagged as stale.
Staleness is a transitive attribute - adding a stale value to a “fresh” value leaves a stale result. This allows realtime queries to continue to produce a best-effort result when data is unavailable, while being able to indicate that the result is not accurate.