Use LATERAL VIEW to split a single row into multiple rows - MaxCompute

How it works

A standard SELECT statement returns one output row for each input row. A UDTF breaks this one-to-one constraint: it takes a single input row and emits multiple output rows, one for each element in an array or map column. Because of this one-to-many relationship, you cannot place a UDTF directly in the SELECT list.

LATERAL VIEW bridges the gap. It runs the UDTF against each row of the base table and joins the resulting rows back to that source row—similar to a nested loop where each iteration appends the UDTF output to the current row. The joined result is then available for filtering, aggregation, and projection like any ordinary table.

Syntax

LATERAL VIEW [OUTER] <udtf_name>(<expression>) <table_alias> AS <column_alias> [, <column_alias> ...]

FROM <base_table>
  [LATERAL VIEW ...]
  [LATERAL VIEW ...]

Parameters

Parameter	Required	Description
`udtf_name`	Yes	The UDTF that splits a single row into multiple rows. See Other functions.
`expression`	Yes	The column or expression whose data is passed to the UDTF.
`table_alias`	Yes	An alias for the virtual table produced by the UDTF.
`column_alias`	Yes	An alias for each column produced by the UDTF. Specify multiple aliases separated by commas when the UDTF outputs more than one column.
`base_table`	Yes	The source table.

OUTER keyword

Add OUTER when you want to preserve input rows even if the UDTF produces no output. Without OUTER, input rows with no UDTF output are silently dropped. With OUTER, those rows are retained and the UDTF-generated columns are set to NULL.

Multiple LATERAL VIEW clauses

Chain multiple LATERAL VIEW clauses after the FROM clause to expand different columns in the same query. Each subsequent clause can reference all tables and column aliases introduced by preceding clauses.

Note

When you chain multiple LATERAL VIEW clauses, each clause independently expands its column, and the results are cross-joined. If the source row has arrays of length m and n, the output contains m × n rows for that source row.

Sample data

The following examples use the pageAds table, which has three columns: pageid STRING, col1 ARRAY<INT>, and col2 ARRAY<STRING>.

pageid	col1	col2
front_page	\[1, 2, 3\]	\["a", "b", "c"\]
contact_page	\[3, 4, 5\]	\["d", "e", "f"\]

Run the following statements to create and populate the table:

CREATE TABLE pageAds (pageid STRING, col1 ARRAY<INT>, col2 ARRAY<STRING>);
INSERT INTO pageAds VALUES ('front_page', ARRAY(1,2,3), ARRAY("a","b","c"));
INSERT INTO pageAds VALUES ('contact_page', ARRAY(3,4,5), ARRAY("d","e","f"));

Examples

Single LATERAL VIEW

Example 1: Expand an array column

To list each integer in col1 as a separate row while keeping the other columns intact, use EXPLODE with a single LATERAL VIEW:

SELECT pageid, col1_new, col2
FROM pageAds
LATERAL VIEW EXPLODE(col1) adTable AS col1_new;

Result:

+--------------+------------+---------------+
| pageid       | col1_new   | col2          |
+--------------+------------+---------------+
| front_page   | 1          | ["a","b","c"] |
| front_page   | 2          | ["a","b","c"] |
| front_page   | 3          | ["a","b","c"] |
| contact_page | 3          | ["d","e","f"] |
| contact_page | 4          | ["d","e","f"] |
| contact_page | 5          | ["d","e","f"] |
+--------------+------------+---------------+

Example 2: Aggregate after expanding

To count how many times each integer value appears across all pages, expand col1 and then group by the expanded value:

SELECT col1_new, COUNT(1) AS count
FROM pageAds
LATERAL VIEW EXPLODE(col1) adTable AS col1_new
GROUP BY col1_new;

Result:

+------------+------------+
| col1_new   | count      |
+------------+------------+
| 1          | 1          |
| 2          | 1          |
| 3          | 2          |
| 4          | 1          |
| 5          | 1          |
+------------+------------+

The value 3 appears twice because it exists in both front_page and contact_page.

Multiple LATERAL VIEW clauses

To expand both col1 and col2 simultaneously, chain two LATERAL VIEW clauses. Each element in col1 is paired with every element in col2, producing a cross-product for each source row:

SELECT pageid, mycol1, mycol2
FROM pageAds
  LATERAL VIEW EXPLODE(col1) myTable1 AS mycol1
  LATERAL VIEW EXPLODE(col2) myTable2 AS mycol2;

Result:

+--------------+------------+------------+
| pageid       | mycol1     | mycol2     |
+--------------+------------+------------+
| front_page   | 1          | a          |
| front_page   | 1          | b          |
| front_page   | 1          | c          |
| front_page   | 2          | a          |
| front_page   | 2          | b          |
| front_page   | 2          | c          |
| front_page   | 3          | a          |
| front_page   | 3          | b          |
| front_page   | 3          | c          |
| contact_page | 3          | d          |
| contact_page | 3          | e          |
| contact_page | 3          | f          |
| contact_page | 4          | d          |
| contact_page | 4          | e          |
| contact_page | 4          | f          |
| contact_page | 5          | d          |
| contact_page | 5          | e          |
| contact_page | 5          | f          |
+--------------+------------+------------+

Each page produces 9 rows (3 values in col1 × 3 values in col2).

What's next

To transpose data between rows and columns using LATERAL VIEW, see Best practices for transposing rows and columns.