...
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.
...
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):
Column name | Column type |
---|---|
pageid | STRING |
adid_list | Array<int> |
An example table with two rows:
pageid | string pageid | Array<int> adid_list | |
---|---|---|---|
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="ccff91ba3a79d506-e21e0c89-46064ae9-98d79b71-8f971382921a2d8e844b3c0e"><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="358950a24075d18e-47af8bf4-485b4e1a-82a3bcb0-ad83d5a71afd406ca9585d87"><ac:plain-text-body><![CDATA[ | " contact_page " | [3, 4, 5] | ]]></ac:plain-text-body></ac:structured-macro> |
...
A lateral view with explode() can be used to convert adid_list
into separate rows using the query:
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
|
The resulting output will be
pageid (string | pageid ) | adid (int | adid ) |
---|---|---|---|
"front_page" | 1 | ||
"front_page" | 2 | ||
"front_page" | 3 | ||
"contact_page" | 3 | ||
"contact_page" | 4 | ||
"contact_page" | 5 |
Then in order to count the number of times a particular ad appears, count/group by can be used:
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT adid, count(1)
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid
GROUP BY adid;
|
...
For example, the following could be a valid query:
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT * FROM exampleTable LATERAL VIEW explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(myCol1) myTable2 AS myCol2; |
...
Array<int> col1 | Array<string> col2 | ||
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="c09437c581af0377-2760d7cb-49174da1-85faa256-4cacf651aa55bd6327331739"><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="d964f6edb32c7769-29f97424-45ff4c50-9ff7ac08-87163530aab30e0bee72c47e"><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="e098404ab7029261-8446de69-45dc42a8-9f67aefd-688d6295717050542eeecb25"><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="195bcdb48f3fa652-c6f83243-4aca491b-8eee81c7-4dce5bd4c7723b6316c52174"><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="07effa4d570ac3df-0aaae813-495d41f6-aade9585-5988f5ca565624fc928db278"><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="80098af881e63f7e-55be784e-4db1406c-85279dd1-d7414660ef7c66e5fd1cd6d7"><ac:plain-text-body><![CDATA[ | 4 | [d", "e", "f"] | ]]></ac:plain-text-body></ac:structured-macro> |
A query that adds an additional LATERAL VIEW:
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT myCol1, myCol2 FROM baseTable LATERAL VIEW explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(col2) myTable2 AS myCol2; |
...