Pfthb

Assume I have this data:

player_id stats
100 ["position":"offense","wins":35,"position":"defense","wins":17]
200 ["position":"offense","wins":85,"position":"defense","wins":52]
300 ["position":"offense","wins":12,"position":"defense","wins":98]

And I want to display it as such:

player_id offense_wins defense_wins
100 35 17
200 85 52
300 12 98

The original data above is currently thrown into an ORC table using:

SELECT p.player_id
 , s.position
 , s.wins
FROM player_stats p
LATERAL VIEW EXPLODE(p.stats) sTable as s

Which gets me:

player_id position wins
100 offense 35
100 defense 17
200 offense 85
200 defense 52
300 offense 12
300 defense 98

Now from this point in MySQL I can just group_by the player_id then case the position, pulling in the associated wins value when it = 'offense' or 'defense' into their own columns, then wrap each case in a COALESCE() to prevent the nulls from coming through. Super fast.

In Hive, instead of COALESCE I have to use MIN or MAX, but the result will be the same regardless.

Here would be the primary way this data is queried:

SELECT player_id
 , max(case when position = 'offense' then wins end) as offense_wins
 , max(case when position = 'defense' then wins end) as defense_wins
FROM orctable
WHERE player_id = 100
GROUP BY player_id

Which would result in :

player_id offense_wins defense_wins
100 35 17

Now, in my real world situation the original dataset has six instances of that 'stats' array, each containing a map of 3-5 pairs. Because of this, the ORC table has player_id listed some 700 times from the repeated lateral views.

The entire table is 300k rows, and the player_id in the real world example is duplicated on this table just over 700 times.

Question 1 - is this the only and/or proper way to transform the data into the desired end result?
Question 2 - should this query be taking between 5 and 10 seconds to complete? The same dataset on a small MySQL server would do this in milliseconds.