A LATERAL VIEW clause is used with a table function, such as split() or explode(). The clause can split a row of data into multiple rows and aggregate the split data.
Single LATERAL VIEW clause
lateralView: LATERAL VIEW [OUTER] udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
LATERAL VIEW [OUTER]: If the table function you defined does not generate rows, the related input rows remain in the output of the LATERAL VIEW clause. In addition, all columns that are generated by the function are NULL.
Example:
STRING pageid | Array<INT> adid_list |
---|---|
"front_page" | [1, 2, 3] |
"contact_page" | [3, 4, 5] |
- Split ad IDs.
SELECT pageid, adid FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
The following result is returned.string pageid int adid "front_page" 1 "front_page" 2 "front_page" 3 "contact_page" 3 "contact_page" 4 "contact_page" 5 - Execute the following statement to aggregate the statistics:
SELECT adid, count(1) FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid GROUP BY adid;
The following result is returned.INT adid count(1) 1 1 2 1 3 2 4 1 5 1
Multiple LATERAL VIEW clauses
A FROM clause can be followed by multiple LATERAL VIEW clauses. A subsequent LATERAL VIEW clause can reference all the provided table and column names.
Array<INT> col1 | Array<STRING> col2 |
---|---|
[1, 2] | ["a", "b", "c"] |
[3, 4] | ["d", "e", "f"] |
- Execute a statement with a single LATERAL VIEW clause
SELECT myCol1, col2 FROM baseTable LATERAL VIEW explode(col1) myTable1 AS myCol1;
The following result is returned.INT mycol1 Array<STRING> col2 1 ["a", "b", "c"] 2 ["a", "b", "c"] 3 ["d", "e", "f"] 4 ["d", "e", "f"] - Execute a statement with two LATERAL VIEW clauses
SELECT myCol1, myCol2 FROM baseTable LATERAL VIEW explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(col2) myTable2 AS myCol2;
The following result is returned.INT myCol1 STRING myCol2 1 "a" 1 "b" 1 "c" 2 "a" 2 "b" 2 "c" 3 "d" 3 "e" 3 "f" 4 "d" 4 "e" 4 "f"