SQL 칼럼 기준 텍스트 편집 - COLLECT_SET, COLLECT_LIST, CONCAT_WS?!

2021. 12. 2. 09:26

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

 

오늘은 SQL에서 한 칼럼의 텍스트들을 붙이는 방법에 대해서 알아보도록 하겠다. 여기서는 Hive SQL을 기준으로 칼럼 기준 텍스트를 편집하는 방법에 대해서 알아보았다. 하지만 다른 SQL에도 웬만하면 동일하게 적용할 수 있지 않을까 싶다.

 

 

SQL 칼럼 기준 텍스트 편집

 


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

 


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

grp1 grp2 grp3 var1 var2 var3
A Z 1 NULL NULL
B Y 3 5 -1
C X 9 4 2
A Z 3 NULL -3
B Y 2 2 1
C X 4 3 NULL

 

 

1. COLLECT_SET

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

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

 

[결과]

grp _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에도 함수를 통해서 여러가지 번거러운 작업들을 쉽게 할 수 있는 방법들이 존재했다. 향후에도 이런 유용한 함수들에 대해서 하나씩 알아보도록 하자.

댓글()