The crate_query_cont node

[experimental]

since 0.19.0

Query the CRATE database for time series data.

This node is used for continous timeseries queries.

Query

A select statement will be executed periodically, on every iteration a timefilter gets ajusted according to the period parameter. For this to work, the query given must contain the $__timefilter placeholder in the query's where clause:

 def query = 
 '
    SELECT ts, id, temp1 FROM doc.table 
    WHERE $__timefilter 
    AND stream_id = 'dd419f94834a'
    ORDER BY ts ASC
 '

The timefilter placeholder gets replaced by this statement:

ts >= $1 AND ts < $2

Start

The start parameter determines the query start time. It's value is a past point in time.

There are two possible ways to provide this:

  • provide an ISO8601 Datetimestamp, ie: '2021-11-16T17:15:00.000Z'
  • provide a query that results in 1 row with exactly 1 column named 'ts' containing an ISO8601 Datetimestamp.
SELECT DATE_FORMAT(ts) FROM table WHERE worked_on = false ORDER BY ts LIMIT 1

Historic and up-to-date data

While reading data from the past, min_interval will be used to schedule the operation. Once the timefilter reaches persent wall-clock time the offset parameter will determine an amount of time to add to the scheduled time, this is to account for late incoming data to the database.

Example

def period = 1m
def sql = 
'SELECT ts, id, temp1 FROM doc.table 
 WHERE $__timefilter AND stream_id = 'dd419f94834a'
 ORDER BY ts ASC
'

|crate_query_cont()
.query(sql)
.period(period)  
.start('2021-11-16T16:03:42.040000Z')

|crate_query_cq()
.query(sql)

The above example will execute the query periodically, emitting data_batch items with data_points worth of 1 minute.

start will be aligned to period, so that the timefilter will look like this for the first query:

ts >= '2021-11-16T16:03:0.000000Z' AND ts < '2021-11-16T16:04:0.000000Z'

Parameters

Parameter Description Default
host( string ) CrateDB host from config
port( integer ) CrateDB port from config
user( string ) username from config
pass( string ) password from config
database( string ) Database name from config
query( string ) 'SELECT' query with $__timefilter placeholder
start( string ) timefilter start point .ISO8601 datetime string or query that retrieves an ISO8601 datetime string from the database
stop( string ) timefilter stop point .ISO8601 datetime string or query that retrieves an ISO8601 datetime string from the database undefined
filter_time_field( string ) name of timestamp db column, used for timefiler 'ts'
result_time_field( string ) name of result column, used for retrieving timestamps defaults to filter_time_field
period( duration ) timefilter timespan, query bounds will be aligned to this value 1h
offset( duration ) offset at which the database is queried when the timefilter reached 'now' time 20s
min_interval( duration ) minimum query-interval when the timefilter is in the past 5s