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="b723b400-83f7-475a-8194-71dec6771eeb"><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="43507ddb-9810-4516-8d02-53989ed7bcfb"><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="ab7a1091-da81-4ada-b1f7-c24c70242d60"><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="5836018e-e9a3-47b9-815d-e5a7438de27a"><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="4b9f1f75-9d43-4fa4-a248-c2e0108dd9cf"><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="b73c0d2f-a6f5-4ae5-ad6b-2bceb13bc85a"><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="f4ec3844-49b0-4239-a7f3-a393333d222e"><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="6b345fc3-7f5b-417a-9761-d5ea8fd626ce"><ac:plain-text-body><![CDATA[ | 4 | [d", "e", "f] | ]]></ac:plain-text-body></ac:structured-macro> |
A query that adds an additional LATERAL VIEW:
...