What is Hive Lateral View and How to use it?

  • Post author:
  • Post last modified:October 30, 2019
  • Post category:BigData
  • Reading time:5 mins read

The best part of Apache Hive is it supports array types. i.e. you can store the array values in Hive table columns. With the help of an array, you can minimize the table rows by grouping together in the form of an array. In this article, we will check what is the Hive lateral view and how to use it with array values.

What is Hive Lateral View and How to use it?

You can use lateral view either with EXPLODE or INLINE function.

What is Hive Lateral View?

Before going in detail, let us check what is lateral view?

In Hive, lateral view explode the array data into multiple rows. In other word, lateral view expands the array into rows.

For example, consider below example.

IDPhoneCity
1[1234567890, 9876543210][‘BLR’, ‘ND’]
2[2345678910, 8765432190][‘CH’, ‘MB’]

When you use later view along with explode function, you will get result something like below.

IDPhoneCity
11234567890BLR
19876543210BLR
11234567890 ND
19876543210 ND
22345678910CH
28765432190CH
22345678910 MB
28765432190 MB

How to use Lateral View?

The lateral views are used along with EXPLODE or INLINE functions. Both functions work on the complex data types such as array.

Explode function in the lateral view can contain embedded functions such as map, array, struct, stack, etc.

Let us discuss lateral view usage in detail.

Lateral View with EXPLODE Function

As mentioned earlier, explode function with expand the array values into rows or records.

For example, consider below lateral view with EXPLODE functions.

SELECT lv.* 
FROM   ( SELECT 0) t 
lateral VIEW explode(array(1234567890,9876543210)) lv AS phone;

Below is the sample output:

+-------------+--+
|  lv.phone   |
+-------------+--+
| 1234567890  |
| 9876543210  |
+-------------+--+
Lateral View with INLINE Function

You can use lateral view with inline function as well.

For example, consider below simple query with lateral view and inline function.

SELECT lv.* 
FROM   (SELECT 0) t 
lateral VIEW inline(array(struct('A',10,'AAA'),struct('B',20, 'BBB'),struct('B',300, 'CCC') )) lv AS col1, col2, col3;

Below is the sample output: 

+----------+----------+----------+--+
| lv.col1  | lv.col2  | lv.col3  |
+----------+----------+----------+--+
| A        | 10       | AAA      |
| B        | 20       | BBB      |
| B        | 300      | CCC      |
+----------+----------+----------+--+

Related Articles

Hope this helps 🙂

Tags: ,