MaxCompute:Lateral View

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

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.