1

My table is like this in Hive:-

col1 | col2 | col3

C1 | F1,F2,F3 | V1,V2,V3

I need output in below format:-

col1 | col2 | col3

C1 | F1 | V1
C1 | F2 | V2
C1 | F3 | V3

I used the explode function as below:-

select col1, exp_col2, exp_col3 from my_table
LATERAL VIEW EXPLODE (split(col2, ',')) col2table AS exp_col2
LATERAL VIEW EXPLODE (split(col3, ',')) col3table AS exp_col3;

This query is doing cross join on col1, instead of returning 3 rows it is returning 9 rows as output.

Can anyone please help me out here?

Vishal
  • 55
  • 1
  • 4
  • 7
  • If col1 is a CK/PK then: explode (col1, col2) & explode (col1, col3); join the results on col1. Please read & act on [mcve]. Explain exactly what data could be in the input--Is col1 a PK? Can there be duplicate rows? Make input & code cut & paste & runnable with output diffable. – philipxy Oct 05 '18 at 05:28
  • Possible duplicate of [Hive Explode / Lateral View multiple arrays](https://stackoverflow.com/q/20667473/3404097) – philipxy Oct 05 '18 at 05:44
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Oct 05 '18 at 05:45
  • Possible duplicate of [Hive Explode / Lateral View multiple arrays](https://stackoverflow.com/questions/20667473/hive-explode-lateral-view-multiple-arrays) – leftjoin Oct 08 '18 at 08:43

0 Answers0