본문 바로가기

db

hive lateral view

아래와 같은 hive의 테이블 데이터가 있다.


name

start_date

end_date 

 P

 2017-10-11

 2017-10-13 

 D

 2017-10-11 

 2017-10-12 


위 테이블의 start_date와 end_date의 데이터를 이용하여 사용자별로 해당 기간을 아래 표 처럼 row형태로 데이터를 추출하는 방법을 고민해보았으나 쉽지 않았다.


 name

date 

 P

 2017-10-11

 P

 2017-10-12 

 P

 2017-10-13

 D

 2017-10-11 

 D

 2017-10-12 


oracle이라면 connect by를 사용하거나 하면 해결이 될 것 같았는데 hive sql은 connect by가 지원하지 않더라.

이것저것 찾다보니 hive lateral view라는 기능이 있어 해결한 방법을 공유한다.


lateral view?

단순히 번역을 하면 측면보기로 번역이 되는데 한 컬럼의 array데이터를 가상의 뷰를 만든다.  

아래 예제를 보면 이해가 쉽게 될 것 이다.


sample data

pageid

adid_list

front_page

[1, 2, 3]

contact_page

[3, 4, 5]


sample query

1
2
SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
cs


sample result

pageid (string)

adid (int)

"front_page"

1

"front_page"

2

"front_page"

3

"contact_page"

3

"contact_page"

4

"contact_page"

5


LATERAL VIEW는 explode와 함께 사용되지만 explode는 많은 UDTF 중 하나이며 더 자세한 정보는 링크를 통해서 확인할 수 있다.



그렇다면 기간 데이터를 어떻게 row형태로 데이터를 변환할 수 있는지 실제 작성한 query를 보자.


1
2
3
4
5
select t.name, date_add(t.start_date, pe.i) as date from (
    select name, start_date, end_date from sample_table
) t
lateral view
posexplode(split(space(datediff(t.end_date, t.start_date)), ' ')) pe as i, x
cs


위 샘플과는 다르게 posexplode를 사용하였다. explode와는 다르게 row 인덱스를 알 수 있다.

위 select절의 date_add함수의 두번째 인자 값을 보면 pe.i가 있는데 해당 값이 row 인덱스로 보면된다.


datediff로 t.end_date와 t.start_date간의 기간 차이를 구하고 그 차이가 즉 row로 변환되어 i는 인덱스,

x는 차이값이 된다.


실제 위 hive query를 실행해보면 아래와 같은 결과를 볼 수 있다.


 name

date 

 P

 2017-10-11

 P

 2017-10-12 

 P

 2017-10-13

 D

 2017-10-11 

 D

 2017-10-12 


#참고자료

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-posexplode(array)

'db' 카테고리의 다른 글

Hive GROUP_CONCAT  (0) 2015.04.09
MySQL(MariaDB) 사분위수 구하기  (1) 2014.09.02
Oracle FlashBack  (0) 2011.04.05