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.
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.
ID | Phone | City |
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.
ID | Phone | City |
1 | 1234567890 | BLR |
1 | 9876543210 | BLR |
1 | 1234567890 | ND |
1 | 9876543210 | ND |
2 | 2345678910 | CH |
2 | 8765432190 | CH |
2 | 2345678910 | MB |
2 | 8765432190 | 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
- Hadoop Hive Create View Syntax and Examples
- Hive Date Functions and Examples
- Hive Join Types and Examples
- Apache Hive Array Functions, Usage and Examples
Hope this helps 🙂