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

Syntax:
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:

You have a table named pageAds. The table contains two columns: pageid string and adid_list. Each value in the adid_list column is a collection of ad IDs that are separated by commas (,).
STRING pageid Array<INT> adid_list
"front_page" [1, 2, 3]
"contact_page" [3, 4, 5]
To collect statistics on the number of times each ad ID appears on all pages, perform the following steps:
  1. 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
  2. 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.

Example:
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"