Hive Lateral view Introduction

Lateral View syntax

Hive supports array type columns so that you can store a list of values for a row all inside a single column.

For example

pageAds, has two column pageid and adid_list:

string pageid Array adid_list
“front_page” [1, 2, 3]
“contact_page” [3, 4, 5]

Using following SQL sentence:
SELECT pageid, adid FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

can output following result:

string pageid Array adid_list
“front_page” 1
“front_page” 2
“front_page” 3
“contact_page” 3
“contact_page” 4
“contact_page”

Then in order to count the number of times a particular ad appears, count/group by can be used:

int adid count(1)
1 1
2 1
3 2
4 1
5 1

Leave a Reply

Your email address will not be published. Required fields are marked *