Query Druid for Latest Values for Given Dimension

Hello. I’m trying to query Druid for the latest values from a column named value (based on __time) for a dimension (column id). I thought a topN query would be a good choice, so I created this query:

{

“queryType” : “topN”,

“dataSource” : “sensor”,

“intervals” : [“2019-10-20/2019-10-29”],

“granularity” : “all”,

“dimension” : “id”,

“metric” : “latest_value”,

“threshold” : 25,

“aggregations” : [

{

“type” : “stringLast”,

“name” : “latest_value”,

“fieldName” : “value”

}

]

}

However, I get the error:

“errorMessage” : “Could not resolve type id ‘stringLast’ into a subtype of [simple type, class io.druid.query.aggregation.AggregatorFactory]\n at [Source: HttpInputOverHTTP@536a5d97[c=306,q=1,[0]=EOF,s=STREAM]; line: 1, column: 219] (through reference chain: java.util.ArrayList[0])”,

“errorClass” : “com.fasterxml.jackson.databind.JsonMappingException”,

“host” : null

I am using Druid 0.12.1.

My questions:

  1. Is the TopN query the way to get what I need?

  2. Is the stringLast aggregator type available in 0.12.1?

Thanks in advance for any help.

Chris

On your 2nd question:

stringFirst/stringLast were introduced in Druid 0.13.0-incubating
https://github.com/apache/incubator-druid/issues/6442

Thanks,

Vaibhav

Thanks, Vaibhav. So now I am wondering how I might possibly do this without the stringLast aggregation type…

Would you able to write equivalent SQL for your requirement? Or could you give an example with input and expected output?

btw : You can read more on TopN here
https://druid.apache.org/docs/latest/querying/topnquery.html

Thanks,

Vaibhav

The equivalent SQL would be something like this:

with max_row as select id, max(timestamp) as max_timestamp from my_table group by id;

select t.id, t.timestamp, t.value

join my_table t, max_row m

on t.id = m.id

and t.timestamp = m.max_timestamp

Thanks,

Chris

In my knowledge, Joins are still not supported in Druid. There is a proposal to support joins in druid which is actively being worked.
https://github.com/apache/incubator-druid/issues/8728

Lets hope, Someone in the community can help us to get an equivalent query which could be written without join and able to get the results you want.

Thanks,

Vaibhav

Hey Chris,

Is it possible that you really want doubleLast not stringLast? (Assuming value is numeric, doubleLast would be right.)