LanguageManual LateralView

Lateral View Syntax


lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*

Description

Lateral view is used in conjunction with user-defined table generating functions such as explode(). As mentioned in LanguageManual UDF#UDTF, a UDTF generates one or more output rows for each input row. A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias.

Important note

Currently, lateral view does not support the predicate push-down optimization. If you use a WHERE clause, your query may not compile. Look for the fix to come out at https://issues.apache.org/jira/browse/HIVE-1056

Until then, try adding set hive.optimize.ppd=false; before your query.

Example

Consider the following base table named pageAds. It has two columns: pageid (name of the page) and adid_list (an array of ads appearing on the page):

string pageid	Array<int> adid_list
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="7849dd53-70c7-4513-be36-9508dc6a40a3"><ac:plain-text-body><![CDATA[	"front_page"	[1, 2, 3]	]]></ac:plain-text-body></ac:structured-macro>
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="6f8c8a5c-d5c2-4bec-b52c-94478a133287"><ac:plain-text-body><![CDATA[	"contact_page"	[3, 4, 5]	]]></ac:plain-text-body></ac:structured-macro>

and the user would like to count the total number of times an ad appears across all pages.

A lateral view with explode() LanguageManual UDF#explode can be used to convert adid_list into separate rows using the query:

SELECT pageid, adid 
	FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

The resulting output will be

string pageid	int adid
"front_page"	1
"front_page"	2
"front_page"	3
"contact_page"	3
"contact_page"	4
"contact_page"	5

Then in order to count the number of times a particular ad appears, count/group by can be used:

SELECT adid, count(1) 
	FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid
GROUP BY adid;

int adid	count(1)
1	1
2	1
3	2
4	1
5	1

Multiple Lateral Views

A FROM clause can have multiple LATERAL VIEW clauses. Subsequent LATERAL VIEWS can reference columns from any of the tables appearing to the left of the LATERAL VIEW.

For example, the following could be a valid query:

SELECT * FROM exampleTable
	LATERAL VIEW explode(col1) myTable1 AS myCol1
	LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

LATERAL VIEW clauses are applied in the order that they appear. For example with the following base table:

Array<int> col1	Array<string> col2
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="9170d64d-f0db-49db-9832-c37c7892f29d"><ac:plain-text-body><![CDATA[	[1, 2]	[a", "b", "c]	]]></ac:plain-text-body></ac:structured-macro>
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="60e20c83-2fab-4e9d-a2e2-0432db380fa3"><ac:plain-text-body><![CDATA[	[3, 4]	[d", "e", "f]	]]></ac:plain-text-body></ac:structured-macro>

The query:

SELECT myCol1, col2 FROM baseTable
	LATERAL VIEW explode(col1) myTable1 AS myCol1;

Will produce:

int mycol1	Array<string> col2
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="14646b7e-512c-4e5c-95bb-eb154d9c7679"><ac:plain-text-body><![CDATA[	1	[a", "b", "c]	]]></ac:plain-text-body></ac:structured-macro>
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="c894be66-b095-468b-8d54-d7cd82964937"><ac:plain-text-body><![CDATA[	2	[a", "b", "c]	]]></ac:plain-text-body></ac:structured-macro>
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="71188982-76b6-44bc-8a54-bfc55b9107e7"><ac:plain-text-body><![CDATA[	3	[d", "e", "f]	]]></ac:plain-text-body></ac:structured-macro>
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="acebb79c-ac7a-4fc7-9399-0ced22d0f246"><ac:plain-text-body><![CDATA[	4	[d", "e", "f]	]]></ac:plain-text-body></ac:structured-macro>

A query that adds an additional LATERAL VIEW:

SELECT myCol1, myCol2 FROM baseTable
	LATERAL VIEW explode(col1) myTable1 AS myCol1
	LATERAL VIEW explode(col2) myTable2 AS myCol2;