THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
| Table of Contents |
|---|
Lateral View Syntax
| Code Block |
|---|
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
|
...
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 zero 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.
| Info | ||
|---|---|---|
| ||
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 |
| Info | ||
|---|---|---|
| ||
From Hive 0.12.0, column aliases can be omitted. In this case, aliases are inherited from field names of StructObjectInspector which is returned from UTDF. |
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):
...
An example table with two rows:
pageid | adid_list | <ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="6bfda83a-bb02-42ac-ba1d-8e485a38c701"><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="02067cfa-1e2e-4cb9-9995-28753bd1edcd"><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:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
|
...
Then in order to count the number of times a particular ad appears, count/group by can be used:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT adid, count(1)
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid
GROUP BY adid;
|
...
For example, the following could be a valid query:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT * FROM exampleTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(myCol1) myTable2 AS myCol2;
|
...
Array<int> col1 | Array<string> col2<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="e18ae56f-48ad-4586-aac2-c3cbcf50603d"><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="88844e28-d393-4128-a454-a2290fec436a"><ac:plain-text-body><! | |
[CDATA[ [3, 4] | [d", "e", "f"] | ]]></ac:plain-text-body></ac:structured-macro> |
The query:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT myCol1, col2 FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1;
|
...
int mycol1 | Array<string> col2 | ||||
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="7508a9ba-fa7c-4a91-9225-29a32766321e"><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="abc11bad-d0c3-4d3c-8556-40fbf9c92884"><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="ff4e7d5b-3803-41b1-a99e-3a08bea47fa2"><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="94d936e0-c911-4c24-b4aa-598fedc5c980"><ac:plain-text-body><![CDATA[ |
4 | [d", "e", "f"] ]]></ac:plain-text-body></ac:structured-macro> |
A query that adds an additional LATERAL VIEW:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT myCol1, myCol2 FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;
|
...
| Info | ||
|---|---|---|
| ||
Introduced in Hive version 0.12.0 |
User The user can specify the optional OUTER keyword to make a row with NULL(s) for UDTF side in case the UDTF does not make any rows. keyword to generate rows even when a LATERAL VIEW usually would not generate a row. This happens when the UDTF used does not generate any rows which happens easily with explode when the column to explode is empty. In this case the source row would never appear in the results. OUTER can be used to prevent that and rows will be generated with NULL values in the columns coming from the UDTF.
For example, the following query returns an empty result:
| Code Block | ||
|---|---|---|
| ||
SELEC select * fromFROM src LATERAL VIEW explode(array()) C AS a limit 10; |
But with the OUTER keyword, keyword
| Code Block | ||
|---|---|---|
| ||
SELECT select * fromFROM src LATERAL VIEW OUTER explode(array()) C AS a limit 10; |
Will it will produce:
238 val_238 NULL
86 val_86 NULL
311 val_311 NULL
27 val_27 NULL
165 val_165 NULL
409 val_409 NULL
255 val_255 NULL
278 val_278 NULL
98 val_98 NULL
...