hive에서 원하는 순서대로 array 만드는 방법은?!

하이브에서 원하는 순서대로 array를 만드는 방법에 대해서 알아보았다.. 구글에서 array order by column in hive로 검색해 봤지만 원하는 결과를 찾기는 어려웠다. 필자가 찾은 방법 중 하나는 아래와 같은데, 쿼리를 수행하면 array의 순서가 뒤죽박죽이다.

 

create table temp.table2
as select
t1.id
,collect_list(t1.val) as val_list
from
(
select p1.id
       ,p1.id_no
       ,p1.val
from temp.table1
distributed by p1.id
sort by p1.id,p1.id_no

) t1
group by t1.id, t1.id_no
;

 

 

그 외에도 몇 가지 방법을 찾아서 해 봤는데 잘 되지 않는다. 그 중에 하나는 윈도우 함수(over)를 이용하는 방법인데, 모든 행에 대해서 array를 만든다. 그래서 최종 행을 선택해 줘야 하는데 이게 또 맘처럼 되지 않는다.

 

필자가 찾아본 자료에서는 아래 내용을 서브 쿼리로 last_value함수를 이용하는 방법인데, 시간도 오래 걸리고 last_value함수가 잘 적용되지 않는다.

 

create table temp.table2
as select t1.id
          ,collect_list(t1.val) over (partition by t1.id order by t1.id_no) as val_list
from temp.table1

 

 

그래서 아래와 같은 방법으로 했다. 문자열을 만들고 str_to_map함수를 이용하면, key와 값의 형태로 만들기 쉽다. 이후에 sort_array함수를 이용하면 정렬이 되는데, 필자가원하는 값은 실수형이라 이 방법이 어려웠다.

 

그래서 map으로 만든 다음에, array로 만들고 정렬을 했다. 이후에는 lateral view explode 구문을 이용해서 다시 row단위로 만들었다. 그 다음에 map에서 값만 가져와서 array를 만드니 순서대로 만들 수 있었다.

 

select t2.id, collect_list(map_valeus(val)[0])
from
(
select t1.id,sort_array(collect_list(t1.val_list)) as val_list
from 
(
select p1.id,map(p1.id_no,p1.val) as val_list
from temp.table1
) t1
group by t1.id
) t2 lateral view explode(t2.val_list) m as val
group by t2.id
;

 

 

참고로 스파크에서는 map의 형변환이 가능하다고 한다. 형변환이 가능하면, 위의 코드도 더 쉽고 간결하게 작성할 수 있다. 하지만 안타깝게도 하이브에서는 map의 형변환이 불가능한 듯 하다.

 

-- SPARK SQL에서는 map 자료형의 형변환이 가능하다고 한다.

select cast(str_to_map("A:1,B:1,C:1") as map<string, int>)

 

 

그런데 데이터가 많아지니 속도가 나오질 않는다. 그래서 돌고돌아 가장 첫번째 방법에서, distributed by~ sort by~를 cluster by로 바꿨다. cluster by는 distributed by와 sort by를 한 번에 수행하는 것이라고 알고 있는데, 왜 후자는 되고 전자는 안 되는지 모르겠다. 여기서 안 된다는 것은 array를 만들 때 순서가 유지되지 않는다는 얘기이다.

가장 깔끔한 방법이 아닌가 싶다.

 

create table temp.table2
as select
t1.id
,collect_list(t1.val) as val_list
from
(
	select p1.id
	       ,p1.id_no
	       ,p1.val
	from temp.table1
	cluster by p1.id,p1.id_no

) t1
group by t1.id, t1.id_no
;

 

 

하이브 어레이

 

 

오늘은 이렇게 hive에서 원하는 순서대로 array 만드는 방법에 대해서 알아보았다. 아마도 하이브 버전에 따라 되는 쿼리가 있고, 안 되는 쿼리가 있는 듯 하다. 혹은 하이브를 띄울 때 옵션에서 차이가 있는 건 아닌가 싶기도 하다. array를 원하는 순서대로 만드는 것보다 map자료형을 이용하는 것을 추천한다. map의 키를 인덱스로 넣으면 정렬할 필요가 없기 때문이다. 필자는 이미 약속된 바가 있어 array를 최대한 이용했지만 말이다.

 

혹은 UDF를 작성해서 해결하는 것도 방법이 될 듯 하다. 더 좋은 방법이 있다면 댓글로 남겨주면 좋겠다.