Working with Hive Joins

Question

File1: Id file

id   interests_code
1         1,2
2         2,3
3         1,4

File2: Interests file

1 Football
2 Cricket
3 Baseball
4 Hockey

Here in File1, the column interests_code is an array of elements (array), I would like to create an output file as,

id    interests
1     Football,Cricket
2     Cricket,Baseball
3     Football,Hockey

Can a Join be done on a Column of array to another table?

Answer 1

You'd better normalize your database design.

But if you want get result from the current table structure, try:

SELECT t1.id, GROUP_CONCAT(t2.interest) AS interests
FROM id_file t1
LEFT JOIN interests_file t2 ON FIND_IN_SET(t2.id, t1.interests_code)
GROUP BY t1.id