CODEWITHSUNDEEP
sqlhadoophive
DevEx
DevEx

Reputation: 4571

Hive complex type to handle one to many relationships

How to handle one-to-many relationships using Hive complex type? for example, given two tables :

artist: artist_id, first_name, last_name 
song:  song_id, song_name, song_date, artist_id

How to write hiveql or sql to include collection of songs into unique artist i.e.

e.g.

112, drew, jackson, {10: [hill, 1992], 13: [away, 2011], .... }
113, maria,mcmillan, {25: [denial, 2000], 26: [fly, 1990], .... }

Upvotes: 2

Views: 782

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

select      a.artist_id, a.first_name, a.last_name 
           ,s.songs

from                    artist  as a

            left join  (select      artist_id
                                   ,concat('{',concat_ws(',',collect_list(concat(cast(song_id as string),':[',song_name,',',cast(song_date as string),']'))),'}') as songs

                        from        song    as s

                        group by    artist_id
                        ) s

            on          s.artist_id =
                        a.artist_id
;

Upvotes: 2

Related Questions