THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
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 Built-in Table-Generating Functions, 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.
Version
Prior to Hive 0.6.0, lateral view did not support the predicate push-down optimization. In Hive 0.5.0 and earlier, if you used a WHERE clause your query may not have compiled. A workaround was to add set hive.optimize.ppd=false; before your query. The fix was made in Hive 0.6.0; see https://issues.apache.org/jira/browse/HIVE-1056: Predicate push down does not work with UDTF's.
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="34eda14a-bd4b-4913-b498-f66fa7401a51"><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="93fd9832-53ec-4664-8536-1ed8b8619df7"><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() 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="be4c9960-8148-4e05-a8d4-a6365336e9fb"><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="c0d05da8-d6d1-48be-b287-70038e878db0"><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="58003c95-5edb-4a21-9c06-de7c724d493c"><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="e17bd225-df77-47f1-9dfb-d790e0323eb5"><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="f7a1e2b9-5661-47a2-bfa6-243123dacc07"><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="2a6733a9-5023-4749-ba31-050e22ace614"><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;
Will produce:
int myCol1 |
string myCol2 |
1 |
"a" |
1 |
"b" |
1 |
"c" |
2 |
"a" |
2 |
"b" |
2 |
"c" |
3 |
"d" |
3 |
"e" |
3 |
"f" |
4 |
"d" |
4 |
"e" |
4 |
"f" |