하이브 SQL에서 한 칼럼의 텍스트들을 붙이는 방법 알아보기!

2019. 7. 29. 06:30

하둡 환경에서 데이터 분석을 하면 필수적으로 SQL을 쓰게 된다. 정확히는 하이브나 스파크를 이용해서 데이터 분석을 하게 되는데, 이 때 SQL을 이용하는 것이 가장 편리하다. 하지만 세상에는 SQL보다 편리한 많은 언어와 툴들이 있다. 그래서 SQL을 등한시하였는데 다시금 SQL을 잘 이용해야 하는 이유가 생겼다.

오늘은 SQL에서 한 칼럼의 텍스트들을 붙이는 방법에 대해서 알아보도록 하겠다.


하이브_SQL_문자



SQL 자체가 일종의 표준 언어이지만 플랫폼에 따라 문법이 조금씩 다르다. 여기서는 가급적 Hive SQL을 기준으로 작성해보도록 하겠다. 텍스트 데이터를 조작하다보면 한 칼럼의 텍스트 데이터를 붙여야 하는 경우들이 생기게 된다. 이 때 유용한 것이 COLLECT_SET과 COLLECT_LIST이다. 


우선 데이터가 아래와 같이 있다고 가정해보자.

grp1

grp2

grp3

 var1

var2

var3 

A

Z

1 

NULL

NULL 

 B

Y

 나

 5

 -1

 C

 X

 다

 9

 4

 2

 A

 Z

 가

 3

 NULL

 -3

 B

 Y

 다

 2

 2

 1

 C

 X

 나

 3

 NULL



1. COLLECT_SET

GRP1이라는 변수를 기준으로 GRP2와 GRP3에 대해서 COLLECT_SET 함수를 적용하면 아래와 같이 된다.


SELECT GRP1, COLLECT_SET(GRP2), COLLECT_SET(GRP3)
FROM TEMP.PRACTICE
GROUP BY GRP1


[결과]

grp1

_c1

_c2

A

["Z"]

["가"]

 B

["Y"] 

 ["나", "다"]

 C

[" X"]

["다", "나"]


COLLET_SET을 적용하면 중복된 문자들을 날라가고, 위에서부터 순서대로 파이썬의 LIST와 같은 형태로 저장되는 것을 알 수 있다.



2.COLLECT_LIST

중복된 문자들도 포함시키고 싶다면 COLLECT_LIST를 사용하면 된다.


SELLECT GRP1, COLLECT_LIST(GRP2), COLLECT_LIST(GRP3)
FROM TEMP.PRACTICE

BY GRP1


[결과]

grp1

_c1

_c2

A

["Z", "Z"]

["가", "가"]

 B

["Y", "Y"]

["다", "나"]

 C

[" X","X"]

["나", "다"]



3. CONCAT_WS

일반적으로 생각하는 텍스트 결합을 하기 위해서는 CONCAT_WS함수를 이용해야 한다. 아래와 같이 구분자를 넣어서 문자열을 결할합 수 있다. 구분자가 필요없다면, 입력하지 않으면 된다.


SELECT GRP1, CONCAT_WD("|", COLLECT_LIST(GRP2)), COLLECT_LIST(GRP3)
FROM TEMP.PRACTICE
GROUP BY GRP1


[결과]

grp1

_c1

_c2

A

Z|Z

["가", "가"]

 B

Y|Y

["다", "나"]

 C

 X|X

["나", "다"]



오늘은 이렇게 하이브 SQL에서 한 칼럼의 텍스트들을 붙이는 방법에 대해서 알아보았다. 기본적인 SQL문법만 알고 있었는데, SQL에도 함수를 통해서 여러가지 번거러운 작업들을 쉽게 할 수 있는 방법들이 존재했다. 향후에도 이런 유용한 함수들에 대해서 하나씩 알아보도록 하자.


댓글()
  1. 이것저것 개발자 2020.04.28 11:36 댓글주소  수정/삭제  댓글쓰기

    안녕하세요
    덕분에 필요한 부분 찾아서 배우고 갑니다

    감사합니다.

  2. DS 2020.11.19 10:56 댓글주소  수정/삭제  댓글쓰기

    두번째 테이블에 오타가 있네요!

    ["A"]의 첫 element가 X가 아니라 Z가 되어야 하는 것 같아요!