THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
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="4215c6dd-7f72-4222-af7f-83ad83e69603"><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="20c33970-ed98-4268-b56d-ad9278009ad3"><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="fa6a7c4c-909a-4af5-987a-b2cfd7ce66dd"><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="47f0b921-0139-41a2-8599-07892a855513"><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="7046aebf-6919-4581-8b4a-50702a4f391f"><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="e32698db-3b69-44e5-96a4-8b644c669d50"><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="495be566-b084-47c7-b4f3-cd1cc7c58ad2"><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="5d074330-c5f1-4981-9c20-e72f4aa8a340"><ac:plain-text-body><![CDATA[ | 4 | [d", "e", "f] | ]]></ac:plain-text-body></ac:structured-macro> |
A query that adds an additional LATERAL VIEW:
...