LATERAL VIEW

The LATERAL VIEW clause is used in conjunction with generator functions (such as EXPLODE, POSEXPLODE, etc.) to generate a virtual table containing one or more rows. This clause allows users to operate on input arrays or maps and spread the results into separate rows.

Syntax

SELECT ...
FROM table_reference 
LATERAL VIEW [OUTER] generator_function [ alias ] AS column_identifier [, ...]

Parameter Description

• OUTER: Optional parameter. If the OUTER keyword is specified, a Null value will be returned when the input array or map is empty or Null.
• generator_function: Generator function, such as EXPLODE, POSEXPLODE, etc.
• alias: Optional parameter. Alias for the generator_function.
• column_identifier: List of column aliases for the rows output by the generator_function. The number of column identifiers must match the number of columns returned by the generator function.

Example

Prepare Data

CREATE TABLE employees(id int,name string,skills array<string>);
INSERT INTO employees (id, name, skills) VALUES
(1, 'John Doe', ['Java', 'Python', 'SQL']),
(2, 'Jane Smith', ['C++', 'Hadoop', 'SQL']),
(3, 'Bob Johnson', ['Python', 'Docker']);

Example 1: Using the EXPLODE Function Assume we have a table named employees, which contains a field named skills of array type, and we want to split each employee's skills into individual rows.

SELECT e.id, e.name, s.skill
FROM employees e
LATERAL VIEW EXPLODE(e.skills) s AS skill;
+----+-------------+--------+
| id |    name     | skill  |
+----+-------------+--------+
| 1  | John Doe    | Java   |
| 1  | John Doe    | Python |
| 1  | John Doe    | SQL    |
| 2  | Jane Smith  | C++    |
| 2  | Jane Smith  | Hadoop |
| 2  | Jane Smith  | SQL    |
| 3  | Bob Johnson | Python |
| 3  | Bob Johnson | Docker |
+----+-------------+--------+

Example 2: Using the POSEXPLODE Function The POSEXPLODE function is similar to EXPLODE, but it also returns the position index of the elements in the array. Using the same employees table and inserted data, we can use POSEXPLODE:

SELECT e.id, e.name, ps.position, ps.skill
FROM employees e
LATERAL VIEW POSEXPLODE(e.skills) ps AS position, skill;
+----+-------------+----------+--------+
| id |    name     | position | skill  |
+----+-------------+----------+--------+
| 1  | John Doe    | 0        | Java   |
| 1  | John Doe    | 1        | Python |
| 1  | John Doe    | 2        | SQL    |
| 2  | Jane Smith  | 0        | C++    |
| 2  | Jane Smith  | 1        | Hadoop |
| 2  | Jane Smith  | 2        | SQL    |
| 3  | Bob Johnson | 0        | Python |
| 3  | Bob Johnson | 1        | Docker |
+----+-------------+----------+--------+

Precautions

• When using the LATERAL VIEW clause, make sure to correctly specify the generator function, alias, and column identifiers.