Stream static join with aggregation

rocky5 — Fri, 05 Apr 2024 12:27:55 GMT

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....

topic Stream static join with aggregation in Warehousing & Analytics

Stream static join with aggregation