...
Lateral View Syntax
| Code Block |
|---|
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
|
...
string pageid | Array<int> adid_list | ||
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="8a260ee5cec96257-7289d45f-483b48b8-b73992ec-b8101d8e62cb2952b8619d5f"><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="b7e1397887b8adfd-48264fd0-46d349d9-81f2b49d-5a337fb3dd936339e748eebc"><ac:plain-text-body><![CDATA[ | "contact_page" | [3, 4, 5] | ]]></ac:plain-text-body></ac:structured-macro> |
...
| Code Block |
|---|
SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
|
...
| Code Block |
|---|
SELECT adid, count(1)
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid
GROUP BY adid;
|
...
Array<int> col1 | Array<string> col2 | ||
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="f9779d785a2427da-89777c6e-4fa64976-abc08ade-28bf1aa27643bbeaa0f1ab9c"><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="b0c4dd6dde7e66f8-98dee08e-46824698-997f9fe6-33c9846fbf3d505c6949f524"><ac:plain-text-body><![CDATA[ | [3, 4] | [d", "e", "f"] | ]]></ac:plain-text-body></ac:structured-macro> |
...
int mycol1 | Array<string> col2 | ||
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="9d41ee50095cd6eb-1b710de0-4651421f-aaf9a4ce-773dad3fa26eaec8d3d2afab"><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="49257f6961db2805-8a15a9e6-4ce049d8-ae698fef-90de83a26b14f4762789e203"><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="2cc59ebbbbc1c79b-8b73a9c9-4f1640ab-83e39c4f-a2cbf9300edcd7b560f93396"><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="2fbba8cf5e332eb3-d178bbb0-4deb4947-90cfa8a7-a9e531a49b7540319628df22"><ac:plain-text-body><![CDATA[ | 4 | [d", "e", "f"] | ]]></ac:plain-text-body></ac:structured-macro> |
...
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" |
Outer Lateral Views
| Info | ||
|---|---|---|
| ||
Introduced in Hive version 0.12.0 |
User can specify optional OUTER keyword to make a row with NULL(s) for UDTF side in case the UDTF does not make any rows.
For example, the following query returns empty result:
| Code Block |
|---|
select * from src LATERAL VIEW explode(array()) C AS a limit 10;
|
But with OUTER keyword,
| Code Block |
|---|
select * from src LATERAL VIEW OUTER explode(array()) C AS a limit 10;
|
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
...