search

orchestracities / ngsi-timeseries-api

QuantumLeap: a FIWARE Generic Enabler to support the usage of NGSIv2 (and NGSI-LD experimentally) data in time-series databases
https://quantumleap.rtfd.io/
MIT License
38 stars 49 forks source link

Complex Queries #165

Open c0c0n3 opened 5 years ago

c0c0n3 commented 5 years ago

At the moment QL only supports basic query functionality but we're likely to have to be able to accommodate more sophisticated queries, especially if we want to use QL as a Grafana data source. Here are some examples of queries @chicco785 came up with that we'll most likely have to support:

  1. What is the total number of free parking bays according to the most recent state (free/occupied) reported by the parking sensors deployed on the parking lot?
  2. Same as above but for a specified series of time periods?
  3. Filter an entity time series by attribute value.

QL can already do (1) through the lastN query parameter---specifying lastN = 1 as a query parameter has the effect of returning the most recent value of an attribute. (3) could be done by adding another where clause with a corresponding URL query parameter. But (2) is trickier as we'll have to deal with data that doesn't fit squarely into the time series paradigm and settle questions like how to avoid data points getting counted more than once in a total (@chicco785 can you clarify what you meant here?) or how to compensate for missing data. We detailed these issues in #164.

chicco785 commented 5 years ago

(1) what if i want this for a set of entities? will be usage of lastN=1 still ok? (3) i think context broker support "filter" with q parameter (see the specs) and a language called "Simple Query Language", I would align to that.

chicco785 commented 5 years ago

regarding (2), suppose i am querying the number of free parking slots per hour. probably we will need a way to 1) map the literals to a value use a group by entity id. in this way i would get the "average" occupancy of a each parking entity quite easily and by summing the averages get the total average.

alternatively, i decide that if the last value in the time window (or in the previous one) was occupied, i consider it occupied (i.e. 1.0) and i compute this for time windows.

solution 1) is way simpler, but also may make sense only when we have two values to compare and this makes easy to say 1 or 0.

c0c0n3 commented 5 years ago

(1) I don't think lastN = 1 will work for a set of entities but will let @taliaga comment on that.

(2) Very good suggestions, but we discussed this extensively yesterday and there are some hairy corner cases to work out first. Have a look at the diagram in #164.

(3) yes, simple query language is one of the options we discussed yesterday. but we also considered something more flexible: a little query language for sets with boolean terms and not/or/and combinators. it'd map cleanly to sql, would allow us to also support simple query language queries trivially, get rid of all the ad-hoc queries we have and avoid sql injection.

chicco785 commented 5 years ago

(1) then we will have to extend that, i think it will be a bit overkilling if not run 1) queries to now which entities are there; 2) for each of them retrieve a small value.

(2) i will try to look in to that

(3) i am not against a more flexible solution, but probably this won't be "friendly" for developers already engaged with context broker.

github-actions[bot] commented 3 years ago

Stale issue message