hive lateral view

db 2017.10.12 15:37

아래와 같은 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 lateral view  (0) 2017.10.12
Hive GROUP_CONCAT  (0) 2015.04.09
MySQL(MariaDB) 사분위수 구하기  (0) 2014.09.02
Oracle FlashBack  (0) 2011.04.05

WRITTEN BY
빵군
Web Programmer HOONS닷넷(http://www.hoons.kr) 2011 ASP.NET 시삽 http://about.me/y2kpooh

받은 트랙백이 없고 , 댓글이 없습니다.
secret

기존 Array에 신규 Array값을 Merge하려면 for loop를 돌면서 키값을 비교하며 교체하는 방법은 있으나

그런 날코딩(?)은 너무 우아하지 않아서 찾아보니 Underscore.js로 가능하더라.


기존 Array로 Merge대상

var ori = [

{"KEY": "A", "VALUE" : 1 }, 

{"KEY": "B", "VALUE" : 2 }, 

{"KEY": "C", "VALUE" : 3 },

{"KEY": "D", "VALUE" : 4 }

];


신규 Array

var inp = [

{"KEY": "D", "VALUE" : 44 },

{"KEY": "E", "VALUE" : 5 }

];


신규 Array의 각 Object의 "KEY"값을 기준으로 기존 Array를 추가하거나 변경하는 작업으로 원하는 결과 값은 아래와 같다.


[

{"KEY":"A","VALUE":1},

{"KEY":"B","VALUE":2},

{"KEY":"C","VALUE":3},

{"KEY":"D","VALUE":44},

{"KEY":"E","VALUE":5}

]


Underscore.js의 uniq, union 메서드를 조합하면 원하는 결과 값을 얻을 수 있으며 아래 소스상에서 sortBy메서드는 Object의 "KEY" 값 정렬을 위해 사용되었다.


_.sortBy(

_.uniq(

_.union(inp, ori), false, function(item, ori){ return item.KEY; }

)

, "KEY");




저작자 표시
신고

WRITTEN BY
빵군
Web Programmer HOONS닷넷(http://www.hoons.kr) 2011 ASP.NET 시삽 http://about.me/y2kpooh

받은 트랙백이 없고 , 댓글 하나 달렸습니다.
  1. 안녕하세요? 경력 15년차 개발자 입니다.
    저는 아직도 Array Merge하는거에 익숙하지 않아서 for문으로 열라 돌렸는데요,
    황군님 블로그 봐서 많은 도움이 됏습니다. 이런분이 제 옆에서 같이 일하면 즐거울텐데 말이죠
    코와붕가
secret

Hive GROUP_CONCAT

db 2015.04.09 14:46

Hive에서 GROUP_CONCAT를 사용하고자 한다면 아래와 같이 사용할 수 있다.


1
2
3
SELECT GROUP_COL CONCAT_WS('|', COLLECT_SET(CONCAT_COL))
FROM TABLE_NAME
GROUP BY GROUP_COL
cs


끝.

저작자 표시
신고

'db' 카테고리의 다른 글

hive lateral view  (0) 2017.10.12
Hive GROUP_CONCAT  (0) 2015.04.09
MySQL(MariaDB) 사분위수 구하기  (0) 2014.09.02
Oracle FlashBack  (0) 2011.04.05

WRITTEN BY
빵군
Web Programmer HOONS닷넷(http://www.hoons.kr) 2011 ASP.NET 시삽 http://about.me/y2kpooh

받은 트랙백이 없고 , 댓글이 없습니다.
secret