In Hive, what is the difference between explode() and lateral view explode()

2
votes

Assume there is a table employee:

+-----------+------------------+
| col_name  |    data_type     |
+-----------+------------------+
| id        | string           |
| perf      | map<string,int>  |
+-----------+------------------+

and the data inside this table:

+-----+------------------------------------+--+
| id  |                perf                |
+-----+------------------------------------+--+
| 1   | {"job":80,"person":70,"team":60}   |
| 2   | {"job":60,"team":80}               |
| 3   | {"job":90,"person":100,"team":70}  |
+-----+------------------------------------+--+

I tried the following two queries but they all return the same result:

1. select explode(perf) from employee;
2. select key,value from employee lateral view explode(perf) as key,value;

The result:

+---------+--------+--+ | key | value | +---------+--------+--+ | job | 80 | | team | 60 | | person | 70 | | job | 60 | | team | 80 | | job | 90 | | team | 70 | | person | 100 | +---------+--------+--+

So, what is the difference between them? I did not find suitable examples. Any help is appreciated.

1
hive

1 Answers

4
votes

For your particular case both queries are OK. But you can't use multiple explode() functions without lateral view. So, the query below will fail:

select explode(array(1,2)), explode(array(3, 4))

You'll need to write something like:

select
    a_exp.a,
    b_exp.b
from (select array(1, 2) as a, array(3, 4) as b) t
lateral view explode(t.a) a_exp as a
lateral view explode(t.b) b_exp as b