Versions Compared

Old Version 10

changes.mady.by.user Navis Ryu

Saved on

compared with

New Version 11

changes.mady.by.user Lars Francke

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Formatting changes

...

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:

Array<int>

pageid

string pageid

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
sql
sql
SELECT pageid, adid
	FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

The resulting output will be

pageid adid

pageid (string

)

adid (int

)

"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
sql
sql
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
sql
sql
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
sql
sql
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
sql
sql
SELECT myCol1, myCol2 FROM baseTable
	LATERAL VIEW explode(col1) myTable1 AS myCol1
	LATERAL VIEW explode(col2) myTable2 AS myCol2;

...