THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
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="f5906f52efd3d72c-c7f93105-47154d8d-a8afb6f6-4406b22a52f3f89c791fa6dd"><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="aaf8fef25b927aad-b43fdb0a-45494799-8aeb8383-a3217c59e845aaf720e1b555"><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="9c9f20f89409eef1-f03ecaf3-461845c7-9cb6bfbd-00f76bff483bf59a319c2291"><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="740e87e7eedfffd6-83446a66-401444a3-a8548e63-42b51c855a2324571f2a2da9"><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="e2eb2c9b1e2059d1-503cb279-4ffe4e02-bce29557-64abc46b85b8adec841669bb"><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="dd3ac07dbeaf348a-5570a4c0-4a544345-92ab9094-8bc2ac8f821b0775210e9c42"><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="77aef22e83640e72-0d3b81ea-45a542b8-801f928a-396506ab303446d3dd7fbb4a"><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="181119a6fa0d3f66-f4e97de9-4a784908-9edb9070-1b274322ad7e8dc5ba2e1e68"><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
...