Lateral View Clause
Description
Lateral view clause is used in conjunction with user-defined table generating functions(UDTF) such as explode()
. A UDTF generates zero or more output rows for each input row.
A lateral view first applies the UDTF to each row of base and then joins results output rows to the input rows to form a virtual table having the supplied table alias.
Syntax
lateralView: LATERAL VIEW [ OUTER ] udtf( expression ) tableAlias AS columnAlias [, ... ]
fromClause: FROM baseTable lateralView [, ... ]
The column alias can be omitted. In this case, aliases are inherited from fields name of StructObjectInspector which is returned from UDTF.
Parameters
Lateral View Outer
User can specify the optional
OUTER
keyword to generate rows even when aLATERAL VIEW
usually would not generate a row. This happens when the UDTF used does not generate any rows which happens easily with when the column to explode is empty. In this case, the source row would never appear in the results.OUTER
can be used to prevent that and rows will be generated withNULL
values in the columns coming from UDTF.Multiple Lateral Views
A FROM clause can have multiple LATERAL VIEW clauses. Subsequent LATERAL VIEWS can reference columns from any of the tables appearing to the left of the LATERAL VIEW.
Examples
Assuming you have one table:
CREATE TABLE pageAds(pageid string, addid_list array<int>);
And the table contains two rows:
front_page, [1, 2, 3];
contact_page, [3, 4, 5];
Now, you can use LATERAL VIEW
to convert the column addid_list
into separate rows:
SELECT pageid, adid FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
-- result
front_page, 1
front_page, 2
front_page, 3
contact_page, 3
contact_page, 4
contact_page, 5
Also, if you have one table:
CREATE TABLE t1(c1 array<int>, c2 array<int>);
You can use multiple lateral view clauses to convert the column c1
and c2
into separate rows:
SELECT myc1, myc2 FROM t1
LATERAL VIEW explode(c1) myTable1 AS myc1
LATERAL VIEW explode(c2) myTable2 AS myc2;
When the UDTF doesn’t produce rows, then LATERAL VIEW
won’t produce rows. You can use LATERAL VIEW OUTER
to still produce rows, with NULL
filling the corresponding column.
SELECT * FROM t1 LATERAL VIEW OUTER explode(array()) C AS a;