This topic describes how to use a LATERAL VIEW clause with a user-defined table-valued function (UDTF) to split a row of data into multiple rows and aggregate the split data.

Description

If you directly use a UDTF in a SELECT statement, issues may occur. To address these issues, you can use a LATERAL VIEW clause with a UDTF to split a row of data into multiple rows and aggregate the split data.

If the UDTF that you defined does not generate rows, the related input rows remain in the output of the LATERAL VIEW clause. In addition, all values of the columns that are generated by the UDTF are NULL.

Syntax

lateralView: lateral view [outer] <udtf_name>(<expression>) <table_alias> as <columnAlias> (',' <columnAlias>) 
fromClause: from <baseTable> (lateralView) [(lateralView) ...]
  • udtf_name: required. This parameter specifies the name of the UDTF that splits a row of data into multiple rows and aggregates the split data. For more information, see Other functions.
  • expression: required. This parameter specifies the name of the column to which the row data that you want to split belongs.
  • table_alias: required. This parameter specifies the alias of the result set of the UDTF.
  • columnAlias: required. This parameter specifies the alias of the column that is obtained after data splitting.
  • baseTable: required. This parameter specifies the name of the source table.
    Note

    Multiple LATERAL VIEW clauses may follow a FROM clause. The LATERAL VIEW clauses can reference the aliases of the tables and columns that are listed before the FROM clause. This aims to split row data in different columns.

Sample data

The pageAds table contains three columns. The first column is pageid string. The second column is col1 array<int>. The third column is col2 array<string>. The following table provides the data of this table.
pageid col1 col2
front_page [1, 2, 3] ["a", "b", "c"]
contact_page [3, 4, 5] ["d", "e", "f"]

Examples

  • Use a single LATERAL VIEW clause
    • Example 1: Use a LATERAL VIEW clause to split col1. Sample statement:
      select pageid, col1_new, col2 from pageAds lateral view explode(col1) adTable as col1_new;
      The following result is returned:
      +------------+------------+------------+
| 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: Use a LATERAL VIEW clause to split col1 and aggregate the split data. Sample statement:
      select col1_new, count(1) as count from pageAds lateral view explode(col1) adTable as col1_new group by col1_new;
      The following result is returned:
      +------------+------------+
| col1_new   | count      |
+------------+------------+
| 1          | 1          |
| 2          | 1          |
| 3          | 2          |
| 4          | 1          |
| 5          | 1          |
+------------+------------+
  • Use multiple LATERAL VIEW clauses
    Use multiple LATERAL VIEW clauses to split col1 and col2. Sample statement:
    select pageid,mycol1, mycol2 from pageAds 
    lateral view explode(col1) myTable1 as mycol1 
    lateral view explode(col2) myTable2 as mycol2;
    The following result is returned:
    +------------+------------+------------+
| 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          |
+------------+------------+------------+