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

Introduction

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, the values of all 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

For example, 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 in this table.
pageid col1 col2
front_page [1, 2, 3] ["a", "b", "c"]
contact_page [3, 4, 5] ["d", "e", "f"]

Examples

  • Use one LATERAL VIEW clause.
    • Example 1: Use a LATERAL VIEW clause to split col1. Sample code:
      select pageid, col1_new, col2 from pageAds lateral view explode(col1) adTable as col1_new;
      Returned 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: Use a LATERAL VIEW clause to split col1 and aggregate the split data. Sample code:
      select col1_new, count(1) as count from pageAds lateral view explode(col1) adTable as col1_new group by col1_new;
      Returned result:
      +------------+------------+
       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 code:
    select pageid,mycol1, mycol2 from pageAds 
        lateral view explode(col1) myTable1 as mycol1 
        lateral view explode(col2) myTable2 as mycol2;
    Returned 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          
    +------------+------------+------------+

References

To transpose data from rows to columns or from columns to rows during business development, you can also view the examples in Transpose rows to columns or columns to rows.