i have a design / data model question. i am trying to count distinct accounts allocated in ab test buckets for an ab test over a time interval. so i have three fields, one is event time, second one is account id and second one is a hash map serialized to string. key of the has map is the experiment id and the value is the experiment bucket id. so basically

{e1:b1,e2:b2,e3:b3...}

an account can be in multiple ab tests. the query is going to be

SELECT exp_id, event_ts, bucket_id, DISTINCTCOUNTHLL(account_id) FROM table WHERE exp_id = <exp id> AND event_ts > start_time and event_ts < end_time GROUP BY event_ts, bucket_id

. event_ts has a granularity of some time interval so it is not a problem of high cardinality.

Perhaps have the schema with columns in your query?

I am thinking of having a map field in avro schema mapped to a multi value string field in pinot table schema, and then at the time of query i can use

MAP_VALUE(mapColumn, 'myKey', valueColumn)

to get bucket id. i am about to give it a try.

If you have the option, serialize the map to JSON, then you can use json extraction/transformation functions to map it to columns that are in your query

Yeah that was the second option I looked at. I was hoping to not write a flink job to transform the field for fast poc but looks like groovy script isn't working because it is not a simple expression.

No I am saying, looking at your query, you don’t need to store the serialized map. You can use transforms to directly extract and store columns from input into a flat schema

well the size of the map is not fixed so i am not sure i follow how to directly extract to columns for a flat schema

Not sure if I follow. If values not in map, they can be extracted as null?

the key in the hashmap is the actual experiment id, and the value is the bucket id. i am assuming that you are talking about ingestion transform, unless you are talking about transform at the time of query.

Oh I see. Have you looked at json index? Looking at your query though, wondering if the upstream events could have been experiment based and not user based

that change will take a long time to happen. i can however explode one event into multiple events with one event per entry in map if it comes down to that.

Hmm not sure if valueIn works like that. @Jackie ?

i am following

SELECT VALUEIN(RandomAirports, 'SEA') as airport, count(*)
FROM airlineStats 
GROUP BY airport

from https://docs.pinot.apache.org/configuration-reference/functions/valuein#signature with data like

RandomAirports
SEA,PSC,PHX,MSY,ATL,TYS,DEN,CHS,PDX,LAX,EWR,SFO,PIT,RDU,RAP,LSE,SAN,SBN,IAH,OAK,BRO,JFK,SAT,ORD,ACY,DFW,BWI,TPA,BFL,BOS,SNA,ISN
SEA,PSC,PHX,MSY,ATL,TYS,DEN,CHS,PDX,LAX,EWR,SFO,PIT,RDU,RAP,LSE,SAN,SBN,IAH,OAK
SEA,PSC,PHX,MSY,ATL,TYS,DEN
null
null
null
SEA,PSC,PHX,MSY,ATL,TYS,DEN,CHS
SEA,PSC,PHX,MSY,ATL,TYS,DEN,CHS,PDX,LAX,EWR,SFO,PIT,RDU,RAP,LSE,SAN

Ah ok

if anyone stumbles across this i was able to get it working by using

"ingestionConfig": {
      "transformConfigs": [
        {
          "columnName": "derivedfield",
          "transformFunction": "Groovy({myfield.split('&')}, myfield)"
        }
      ]
    },

and then doing this query

SELECT VALUEIN(derivedfield, 'e1:e1#b1', 'e1:e1#b2') AS bucket_id,
       DISTINCTCOUNTHLL(account_id) AS distinct_allocations
FROM   allocation_events
GROUP  BY bucket_id

to get final response like

bucket_id	distinct_allocations
e1:e1#b1	403
e1:e1#b2	418

@Priyank Bagrecha Can you try adding a filter

derivedfield = 'e1:e1#b1'

in the query?

I can't because derivedField can be

e1:e1#b1.e2:e2#b2,...

etc but yeah what i have right now wont scale well as it scans all documents

Adding a filter with the same values in the

VALUE_IN

should give you the same result without scanning all the documents

interesting, it works great. i am still confused why it works lol. for sake of simplicity i'll replace `e1:e1#b1`by

e1:b1

etc. i have events where derivedField =

e1:b1,e2:b2.e3:b3...

so to me derivedField doesn't exist in ('e1:b1', 'e1:b2'). how does this work? is derivedField in evaluating every element in the array instead of evaluating entire array?

It is modeled as a MV column, and Pinot will match every element with the predicate