Lateral View Explode in Hive SQL and attribute INT to one record

Question

I have a table that looks like this. Date (string), Path (string), Conversions (INT)

|------------|------------|---------------------|-------------|
|     Date   |  cust_id   |         Path        | Conversions |
|------------|------------|---------------------|-------------|
| 2020-01-01 |    1234    | page1, page2, page3 |      1      |
| 2020-01-01 |    4567    | page5, page4, page2 |      0      |
| 2020-01-01 |    8910    | page4, page3, page1 |      2      |
|------------|------------|---------------------|-------------|

What I want to do is explode the path field into rows while keeping the conversion number the same one one row with null to fill in the rest, see below.

|------------|------------|-------|-------------|
|     Date   |  cust_id   | Path  | Conversions |
|------------|------------|-------|-------------|
| 2020-01-01 |    1234    | page1 |      1      |
| 2020-01-01 |    1234    | page2 |    null     |
| 2020-01-01 |    1234    | page3 |    null     |
| 2020-01-01 |    4567    | page5 |      0      |
| 2020-01-01 |    4567    | page4 |    null     |
| 2020-01-01 |    4567    | page2 |    null     |
| 2020-01-01 |    8910    | page4 |      2      |
| 2020-01-01 |    8910    | page3 |    null     |
| 2020-01-01 |    8910    | page1 |    null     |
|------------|------------|-------|-------------|

Unfortunately when I later view explode I get the conversions number duplicated on all rows.

|------------|------------|-------|-------------|
|     Date   |  cust_id   | Path  | Conversions |
|------------|------------|-------|-------------|
| 2020-01-01 |    1234    | page1 |      1      |
| 2020-01-01 |    1234    | page2 |      1      |
| 2020-01-01 |    1234    | page3 |      1      |
| 2020-01-01 |    4567    | page5 |      0      |
| 2020-01-01 |    4567    | page4 |      0      |
| 2020-01-01 |    4567    | page2 |      0      |
| 2020-01-01 |    8910    | page4 |      2      |
| 2020-01-01 |    8910    | page3 |      2      |
| 2020-01-01 |    8910    | page1 |      2      |
|------------|------------|-------|-------------| 

Any thougts?

Answer 1

If you need Conversions to be populated for first array element only, use posexplode and if position = 0 then assign value, else NULL:

select t.`Date`,
       t.cust_id,
       case when e.pos=0 then t.Conversions end Conversions --NULL for pos>0
from mytable t
lateral view posexplode(Path) e as pos, path