How to store and query array of key value pairs?

[Ankit Nayan]

My data format is like:

{
 timestamp string,
 name string,
 kind int,
 status_code int,
 ...
 tags: 

 {
   key1: value1,
   key2: value2,
   ...
  }
}

The list of keys in tags is varying in size for each row and the keys are also not defined (it can be anything string).

I want to query dataset by tags, i.e., return all rows which have given key: value pair.

In Pinot, we can achieve this by storing all keys in a multi-dimension column TAGS__KEYS and all values in another multi-dimension column TAGS__VALUES and then query using MAP_VALUE function.

Sample query:

SELECT * FROM xyzView WHERE ( tags__KEYS = 'key1' AND tags__VALUES = 'value1' AND mapValue(tags__KEYS,'key1',tags__VALUES) = 'value1' )

How can we achieve the same in Druid? Druid also has a multi-dimension column but does not have mapValue functionality. Or can we achieve this using any other way in Druid?

[Ankit Nayan]

Any inputs on this?

ClickHouse also has similar support using IndexOf in array. 

[Ben Krug]

I don't know that that functionality is built-in.  There are lookup tables and now "experimental" indexed tables, but they're for smaller lookup type of functionality.  Probably not good for arbitrary keys, as you describe.

You might be able to make something like it using multivalued dimensions and MVD functions like MV_OFFSET_OF and MV_OFFSET to extract the value that follows the key.  (If so, though, be aware of the behaviors of MVDs in GROUP BY queries, it may be counterintuitive in some cases.)

I hope that's helpful.

--
You received this message because you are subscribed to the Google Groups "Druid User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to druid-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/b0e2443b-098c-428b-98a4-29a9a779a3d4n%40googlegroups.com.

[Ankit Nayan]

Thanks, Ben for taking out some time. I think that's the right set of functions I was looking for. These functions don't seem to be available in 0.19.0, are they available in 0.20.0? 

[Ben Krug]

I thought they'd been around for a while.  Did you capitalize?  It has to be, eg, MV_OFFSET, not mv_offset.  I don't have 0.19 running right now, but I could check tomorrow if you still have problems.

To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/410627c6-6c19-43df-90a1-d51594edfaacn%40googlegroups.com.

[Ankit Nayan]

Not able to get this function in druid 0.19.0

[Ben Krug]

It doesn't autocomplete, but it will work.  I should maybe have suggested MV_OFFSET_OF.  (Same thing, won't autocomplete, but will work.  Eg, I tried 

SELECT MV_OFFSET_OF(d6_mvd,'ro') FROM dataTest

on a dataset with 0.19, and it worked.  (Note it starts counting with 0, and also, if the string isn't there, will return -1.)

To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/746613f2-f50f-42c9-8626-1fc01b964a04n%40googlegroups.com.

[Ankit Nayan]

You are right..it works. Thanks a lot for guiding me there. Appreciate your help

[Ben Krug]

You're welcome!  I hope it'll be useful!

To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/11387fa2-8e18-48e7-88b9-652210f08e34n%40googlegroups.com.