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="80e4046a20928100-d4ebaae3-4fdf4bea-99fa9772-1189d1bdba77c71588357dbb"><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="e34381b6338756ff-3bb839f0-472a4593-96338122-06035776e23861fd40c4e0bc"><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="379b6b10cd4b026c-40df9642-43934af1-92b7b86c-721daf314063b1fd5e120b5f"><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="f9e45bdd45cab65f-eac08d72-43b241ef-94389d51-ae3b477df300d769ed3b44a1"><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="a3d712e475abee64-8e0147ff-40714c02-9a0f8169-e5551576871ec7de4018d67f"><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="163c12381514a5dc-d762de63-46c14656-84aea151-45eb2ab32a49f3cf14f89081"><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="9604a37b1d0e3662-480a30e8-474c4677-acea8d73-93c00369f7c7cc1fd949eb40"><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="8f12383434f88b20-389a853e-4ca344ef-a11a91ba-3b65cbe636587bd10f93edd5"><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;

...