...
Lateral view is used in conjunction with user-defined table generating functions such as explode(). As mentioned in Built-in Table-Generating Functions, 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.
Important note
...
| Info | ||
|---|---|---|
| ||
Prior to Hive 0.6.0, lateral view |
...
did not support the predicate push-down optimization |
...
. In Hive 0.5.0 and earlier, if you used a WHERE clause |
...
your query may not |
...
have compiled. A workaround was to add |
...
: Predicate push down does not work with UDTF's. |
Example
Consider the following base table named pageAds. It has two columns: pageid (name of the page) and adid_list (an array of ads appearing on the page):
string pageid | Array<int> adid_list | ||
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="91ef0caa25a6d238-bf0a1db5-46d3472d-9a58bda3-3c7f44947976dd770ab31f98"><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="942a5e06c0f017e4-38c422a5-4e2a4852-a4769871-bdb7f37e24a9ad3d4e00424d"><ac:plain-text-body><![CDATA[ | "contact_page" | [3, 4, 5] | ]]></ac:plain-text-body></ac:structured-macro> |
...
Array<int> col1 | Array<string> col2 | ||
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="e7fc009728d13bd5-18e4e9ba-45444529-bd1b9dca-3325107bcc46bd306a6dcec5"><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="2d4de0bfcc55767d-5bcedc22-4bde48df-80f685a6-f65e80b5875e3219661b4066"><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="6880cf724770126b-2a7c1a87-42874f96-b31d8704-748e1ac3d01eee432f4f8cbb"><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="fefec8f8dc291ad2-f857945e-489c4f3d-be458de9-ca447466ed0000aec329e999"><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="b590c543487dcefe-e683e324-4f1c432c-b1c08afa-dcaeeac35d2238351efde055"><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="f61f0e6d204a2912-36d2ef72-457743c6-b4c1b869-224532b7458a4d730291f80b"><ac:plain-text-body><![CDATA[ | 4 | [d", "e", "f"] | ]]></ac:plain-text-body></ac:structured-macro> |
...