Create Query

The SELECT … FROM … statement is used to initiate a query in RioDB.

Access Rule

Requires any role.

Syntax

Queries don’t have a “name”. They have an id number.

SELECT <comma separated fields>
FROM <comma-separated windows or streams> 
WHEN <conditions>
OUTPUT <plugin settings for trigger>
LIMIT <integer to expire query after n triggers>
SLEEP <integer value><time unit>

Details:
SELECT – required – fields to be returned when conditions are met.
FROM – required – data sources (streams or windows) for the query.
WHEN – optional – conditions upon which the query should trigger a hit. Without it, every stream message has conditions met, always a hit.
OUTPUT – optional – PLUGIN that will handle the trigger actions when conditions are met. Without it, RioDB assumes this is a one-time query.
SLEEP – optional – cool-off period after a triggered action (in other words, after conditions are met).
LIMIT – optional – force the query to expire after n-triggered actions (in other words, expire the query after conditions are met n times).

Example

This is a very basic example.

SELECT symbol, count, min, max
FROM   last_100_bids_on_tsla 
WHEN   max > min * 1.01
OUTPUT stdout
SLEEP  1m
LIMIT  100;

Explanation:
Assuming we have a window called last_100_bids_on_tsla, which is populated with some ‘TSLA’ stock data coming from some stream (which is not mentioned in the query), and the window running COUNT, MIN and MAX aggregations, this query will:

SELECT the symbol (which is always ‘TSLA’), the count (which is always 100), the min (smallest bid in the last 100), and the max (highest bid in the last 100)

FROM the window named last_100_bids_on_tsla. Assuming this window is populated whenever the stream receives a record of bid on “TSLA”, this query will be executed each time this occurs.

WHEN the max aggregation in the window is greater than the MIN aggregation multiplied by 1.01

OUTPUT will be sent to the stdout plugin, which basically prints the selected fields onto the terminal session where RioDB is running

SLEEP will suspend the query for 1 minute each time the conditions are met, preventing a flood of triggers,

LIMIT will force RioDB to expire (remove) this query after the conditions are met (trigger fired) 100 times.