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

하둡 환경에서 데이터 분석을 하면 필수적으로 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에도 함수를 통해서 여러가지 번거러운 작업들을 쉽게 할 수 있는 방법들이 존재했다. 향후에도 이런 유용한 함수들에 대해서 하나씩 알아보도록 하자.