...
Lateral view is used in conjunction with user-defined table generating functions such as explode(). As mentioned in Hive-LanguageManual -UDFUDF#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.
...
string pageid | Array<int> adid_list | ||
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="0731b891-2f4a-4c31-9e99-84db79d77f3e"><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="04659a68-9026-4593-9f41-8776a7882482"><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() Hive-LanguageManual -UDFUDF#explode can be used to convert adid_list into separate rows using the query:
...
Array<int> col1 | Array<string> col2 | ||
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="70879d09-2e25-4fa4-ac28-6bdb1bef500a"><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="6f29ac69-725b-4f8e-a486-b33774cee24e"><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="060b178c-37e2-4b10-939f-ca909f3bb51f"><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="39839421-4eff-460d-bc3b-9b6b0c236b2a"><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="a0c3ae72-0d5e-4e53-8bff-12990f0a0413"><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="fef4d67c-6b85-43c0-b44a-802e887bea81"><ac:plain-text-body><![CDATA[ | 4 | [d", "e", "f] | ]]></ac:plain-text-body></ac:structured-macro> |
A query that adds an additional LATERAL VIEW:
...