How to explode and aggregate a stream in pipeline builder

Ask the Community
,
1

question about aggregating on a stream

what we are trying to do:
we have two datasets: a stream dataset streamA and static dataset staticB

streamA has a column containing string arrays keys and staticB has a column containing strings key

we want to join streamA with staticB when key (staticB) exists in keys (streamA)

then we want to create a new column in streamA containing all values in key (staticB) that exists in keys (streamA) called joinedKeys (streamA)

how we are doing it:

  1. we applied an assign timestamps and watermarks transform on streamA

  2. we are exploding keys (streamA) into exploded_key (streamA) then joining with staticB when exploded_key (streamA) = key (staticB)

  3. then we are using aggregate over window with parition paritionCol (streamA), window type session event time window and window gap 1 millisecond and no lateness defined

  4. then we collect distinct array of key (staticB) as joinedKeys (streamA)

what’s the problem:
we did not see any data when defining the window this way - should it be defined differently?

2

Can you explode array, join on key, and then collect using the stream’s PK?