We are currently taking a look at Druid as storage solution for our IoT data. Some of our values are observations of state rather than numeric metrics. It looks something like:
The assumption is, that the state changes at the time of the first observed value that is different from the previous recorded value. Any subsequent record that is of the same value is just another observation.
We would like to query the total duration that a device was in a specific state. Like:
SELECT condition, count(*) as duration
FROM (SELECT time_bucket(‘1 second’, __time, 10, 30) AS bucket,
last_observation_carry_forward(last(value)) AS condition
GROUP BY 1) AS bucket_cond
GROUP BY 1;
which would count the number of seconds (time_bucket) from __time=10 to __time=30 by taking the last observation of each second and fills in the last observed value if there is a gap.
Does Druid support queries like this?