[일일회고] Athena Presto CASE 문, String Split함수

회고의 계절이 돌아왔다. 그래서 회고를 해보려는데 기억이 안난다. 일년간 뭘 배웠는지, 뭘 했는지 정말 기억이 나지 않는다. 분명히 새로운 것을 많이 배웠는데 여러가지 문제를 많이 해결했는데, 그 당시 문제 해결 할 때만 반짝 검색해서 쓰고 그 다음에는 기억이 안나서 또 검색하고 있다. 그래서 앞으로 배운게 있으면 짧게라도 정리해서 올려볼까 생각중이다. 기술 문제가 될 수도 있고, 프로세스나 사람간의 문제가 될 수도 있다.

오늘의 문제

요즘 데이터 분석 및 시각화를 하느라 Athena Presto 쿼리를 짜고 있다. 그 중 오늘 짠 쿼리에서, 문자열(스트링) 컬럼의 일부 정보를 다른 대체문자로 변경해야 하는 문제가 있었다. 예를들어 문자열은 ‘id-type-property-something_else’ 이런 식이고, 나는 property를 적절한 이름, 예를들어 P1, P2 등으로으로 바꾸고 싶었다. 

두가지 방법을 고려했다. 첫번째 방법은 문자열을 ‘-’로 쪼갠 후(col_arr이라고 하자) 배열의 위치 [’id’, ‘type’, ‘property’, ‘something_else’]이면 col_arr[3]으로 빼내는 것이다. 그런데 이렇게 해 보니 property의 위치가 제각각이라 모든 케이스를 고려하기 힘들었다. 어쩔대는 property가 배열의 마지막에, 마지막에서 두번째에, 세번째에, 이렇게 가지가지였다. 두번째 방법은 그냥 자바의 contains와 비슷한 기능을 써 원하는 문자열을 포함하는 행들을 찾아내고, case문을 이용해 적절히 값을 고치는 것이다. 이 경우 카테고라이징을 제대로 안하면 데이터가 전부 ELSE로 가 버릴 위험이 있다. 그러나… 두번째 방법이 더 간단해서 그냥 두번재 방법으로 하되 검증을 해보기로 했다.

오늘 배운 것

Athena Presto 쿼리에서 CASE문 작성하는 법[출처]. Athena Presto N년차지만... 아직도 기본적인 SQL이 아니면 검색해야한다. 🥲 그래서 정리해서 남기기로 한 것. 😄 

SELECT CASE column_to_be_matched
WHEN 'value_A' THEN 'the_replacement_of_value_A'
WHEN 'value_B' THEN 'the_replacement_of_value_B'
WHEN 'value_C' THEN 'the_replacement_of_value_C'
ELSE 'default'
END
FROM some_table

 

내가 오늘 작성한 쿼리에서는 아래와 같은 방식으로 쓰였다.

SELECT id, (CASE 
 WHEN col LIKE '%property_1%' THEN 'P1'
 WHEN col LIKE '%property_2%' THEN 'P2'
 ELSE col) as "type"
END
FROM some_table

 

조금 헤맨 부분은, 예제와 달리 WHEN 내부 자체의 결과물이 boolean인경우에는 CASE 다음에 아무 값도 지정하지 않는다는 것이다. CASE 다음에 값을 넣으면 WHEN 다음에 나오는 값과 비교하게 되는 것 같다.

이렇게 하고, 아래처럼 GROUP BY로 각 타입마다 갯수가 얼마인지 세, 내가 찾지 못한 type이 있는지 확인했다. property_1이나 property_2가 아니면 전부 col 그대로 들어가기 때문이다.

SELECT "type", count(distinct id) 
FROM 
(SELECT (CASE 
 WHEN col LIKE '%property_1%' THEN 'P1'
 WHEN col LIKE '%property_2%' THEN 'P2'
 ELSE col) as "type"
END
FROM some_table) 
GROUP BY "type"

 

이 쿼리로 잘못된 부분을 정정하고 마지막 ELSE 부분을 원하는 값으로 전부 바꿨다.

추가로 쪼개는 함수도 알아보았는데.. split이 문자를 배열로 쪼개는 함수이다. 배열을 리턴한다.

split(string, delimiter)

 

cardinality는 배열의 길이 리턴한다. 따라서 아래는 쪼개놓은 배열의 길이를 가져오는 방법. 

cardinality(split(string, delimiter))

 

이건 배열의 마지막 요소 반환. Athena Presto의 배열 인덱스는 1부터 시작한다.

split(string,delimiter)[cardinality(split(string, delimiter))]