I have a table that looks like this:
|customer|category|room|date|
-----------------------------
|1 | A | aa | d1 |
|1 | A | bb | d2 |
|1 | B | cc | d3 |
|1 | C | aa | d1 |
|1 | C | bb | d2 |
|2 | A | aa | d3 |
|2 | A | bb | d4 |
|2 | C | bb | d4 |
|2 | C | ee | d5 |
|3 | D | ee | d6 |
I want to create two maps out of the table:
1st. map_customer_room_date: will group by customer and collect all different rooms (key) and with date (value).
I'm using the collect() UDF Brickhouse function.
this can be archived with something similar as:
select customer, collect(room,date) as map_customer_room_date
from table
group by customer
2nd. map_category_room_date A bit more complicated, consists also of the same map type collect(room, date) and it will contain as keys, all the rooms across ALL categories where for customer X is categories.
This means that for customer1 it will take room ee even though it belongs to customer2. This is because customer1 has category C and this category is also present in customer 2.
The final table is grouped by customer and will look like:
|customer| map_customer_room_date | map_category_room_date |
-------------------------------------------------------------------|
| 1 |{aa: d1, bb: d2, cc: d3} |{aa: d1, bb: d2, cc: d3,ee: d6}|
| 2 |{aa: d3, bb: d4, ee: d6} |{aa: d3, bb: d4, ee: d6} |
| 3 |{ee: d6} |{ee: d6} |
I am having issues building the second map and presenting the final table as described. Any idea how this can be accomplished?