Hi,
I am trying to make Stream - Static join with aggregation with no luck. I have a streaming table where I am getting events with two nasted arrays
ID Array1 Array2
1 [1,2] [3,4]
I need make two joins to static dictionary tables (without any timestamps) based on IDs contained in arrays, second join is based on first, once I will get descriptive values I need to assign and sum scores, so result should be like that:
ID Score1 Score2
1 10 12
so one record per id. I am using Databricks SQL. Any ideas how I can make it? Exploding arrays cause cartesian results, when I join to scoring table results will be incorrect. I was thinking to join by array and get an array in both cases, but I am getting errors that I need to apply watermark (even to static tables!). Appreciate any ideas....
