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

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

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를 보자.

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

위 샘플과는 다르게 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)