Hive lateral view statement (translated from hive wiki)

Lateral view syntax

 
Lateralview: LateralViewUdtf (expression) tablealiasAsColumnalias (','Columnalias)*Fromclause:FromBasetable (lateralview)*
 

Description

Lateral view is used with udtfs such as split and explode. It can split a row of data into multiple rows and aggregate the split data. Lateral view first calls udtf for each row of the original table. utdf Splits a row into one or more rows. Lateral view then combines the results to generate a virtual table that supports the alias table.

 

Example

Suppose we have a table pageads, which has two columns of data. The first column is pageid string, and the second column is adid_list, that is, a comma-separated ad Id set:

String pageid	Array <int> adid_list
"Front_page"	[1, 2, 3]
"Contact_page"	[3, 4, 5]

 

 

 

Count the number of times all ad IDs appear on all pages.

First, split the ad ID:

 
Select pageid, ADid from pageads lateral view explode (adid_list) adtable as ADid;
 

The execution result is as follows:

String pageid	Int ADid
"Front_page"	1
"Front_page"	2
"Front_page"	3
"Contact_page"	3
"Contact_page"	4
"Contact_page"	5

 

 

 

 

 

 

 

Next is an aggregation statistics:

Select ADid, count (1) From pageads lateral view explode (adid_list) adtable as adidgroup by ADid;
 

The execution result is as follows:

Int ADid	Count (1)
1	1
2	1
3	2
4	1
5	1

 

 

 

 

 

Multiple lateral view statements

A From statement can be followed by multiple lateral view statements. The lateral view statement can reference all tables and column names in front of it. The following table is used as an example:

Array <int> col1	Array <string> col2
[1, 2]	[A "," B "," C "]
[3, 4]	[D "," E "," F "]

 

 

 

 
Select mycol1, col2 from basetable lateral view explode (col1) mytable1 as mycol1;
 

The execution result is:

Int mycol1	Array <string> col2
1	[A "," B "," C "]
2	[A "," B "," C "]
3	[D "," E "," F "]
4	[D "," E "," F "]

 

 

 

 

Add a lateral view:

 
Select mycol1, mycol2 from basetable lateral view explode (col1) mytable1 as mycol1 lateral view explode (col2) mytable2 as mycol2;
 

The execution result is:

Int mycol1	String mycol2
1	""
1	"B"
1	"C"
2	""
2	"B"
2	"C"
3	"D"
3	"E"
3	"F"
4	"D"
4	"E"
4	"F"

Note that in the preceding statement, two lateral views are executed in the order of appearance.

From https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView #

Http://blog.csdn.net/inte_sleeper/article/details/7196114