Databricks에서 원하는 조건의 순서에 맞게 배열 정렬하기

부제: 데이터 분석가는 그의 업무시간 중 70%를 데이터 전처리에 쓴다.

 

 

챗GPT와 논쟁 → 구글링 → 각종 도큐멘트 정독 등등의 과정을 거치며 알아낸 정보를 기록해둔다. 나중에 같은 문제를 맞닥뜨린 누군가가 해당 글을 통해 더 빠르게 문제를 해결할 수 있기를...🙏 

 

---

내가 마주한 문제

우리 서비스의 고객 행동을 분석하고, 관련 지표를 설계하던 중 로우데이터의 일부를 하나의 배열 안에 넣을 필요가 있었다. 근데 배열 안에 데이터를 넣을 때 event_time 순서대로 넣어야 했다.

 

예를 들면,

user_id별 영화 시청기록 데이터 (가상)

 

이러한 로우데이터를 가공을 통해 아래와 같은 데이터를 만들어내야 했다.

 

 

 

흠, 쉽네.
ARRAY_AGG()로 film을 묶은 다음에 event_time 기준으로 ORDER BY 하면 되겠군.

 

나는 이 데이터 전처리에 나의 일요일 오후를 통째로 쓰게 될 줄 몰랐다.

 

 

PostgreSQL의 ARRAY_AGG()

ARRAY_AGG()  함수는 PostgreSQL의 네이티브 함수 중 하나로, 값 집합을 받아들이고 집합의 각 값이 배열의 요소에 할당된 배열을 반환하는 집계 함수이다.

PostgreSQL Tutorial에 나와있는 예제에 따르면 아래와 같이 사용된다. (*샘플 데이터베이스의 film, film_actor 및 actor 테이블 사용)

SELECT
    title,
    ARRAY_AGG (first_name || ' ' || last_name) actors
FROM
    film
INNER JOIN film_actor USING (film_id)
INNER JOIN actor USING (actor_id)
GROUP BY
    title
ORDER BY
    title;

 

출력값에서 actors를 first_name 순서대로 order by 하고 싶으면 ARRAY_AGG() 함수에 단 하나의 옵션만 추가해주면 된다.

 

SELECT
    title,
    ARRAY_AGG (
        first_name || ' ' || last_name
        ORDER BY
            first_name
    ) actors
FROM
    film
INNER JOIN film_actor USING (film_id)
INNER JOIN actor USING (actor_id)
GROUP BY
    title
ORDER BY
    title;

 

 

우리 회사는 현재는 데이터플랫폼으로 Databricks를 사용하지만, 몇 년 전에는 PostgreSQL를 사용했었던지라 ARRAY_AGG()에 대해서 알고 있었다. 또한, 순서(order)를 고려했던 적이 없었을 뿐 Databricks에서도 ARRAY_AGG()를 사용했던 경험이 있었기 때문에 안 될거란 생각을 전혀 않고 내가 아는대로 코드를 작성하여 실행했다.

 

[PARSE_SYNTAX_ERROR] Syntax error at or near 'ORDER': missing ')'

 

네? Syntax error요?😨💦

너무 예상 못한 타입의 에러가 떠서 챗GPT한테 물어봤더니 

ARRAY_AGG 함수 내에서 ORDER BY 절을 사용하는 것은 일부 SQL 엔진에서 지원되지 않을 수 있습니다. 대신, ordered_event CTE에서 event_time을 기준으로 정렬하는 방법을 사용할 수 있습니다. ARRAY_AGG 함수는 기본적으로 입력된 순서대로 배열을 생성합니다.

 

라는 답변을 주었다. 이 답변을 받고 나는 '아하 그렇구나. 데이터브릭스에서는 ORDER BY 옵션이 지원되지 않는군. 뭐, 입력된 순서대로 배열을 생성한다면 CTE를 통해 순서를 선행적으로 정렬하면 되겠지'라고 생각했다.

그래서 ordered_event라는 event_time 순서대로 정렬된 CTE를 추가한 뒤에, 최종 SELECT문에서는 ARRAY_AGG(film) AS film_lst를 작성했다. 에러도 안 뜨고 출력값도 그럴싸하게 나오길래 '성공인가?'하고 몇몇 데이터를 골라서 검토하는데 전혀!! event_time 순서대로 나온 게 아닌, film들이 랜덤하게 리스트 안에 들어가 있었다. 입력된 순서대로 배열 생성한다며...!!

 

이 때 곧바로 공식 도큐멘트를 찾아보는 등의 구글링을 시작했다면 좋았겠지만, 챗GPT와 논의를 주고받는 게 너무 익숙해진 나는 4차례 정도 더 논의를 주고받고 나서야 챗GPT로부터는 해결책을 얻어낼 수 없다는 걸 깨달았다.

 

Spark에서의 ARRAY_AGG() 함수 제한

한 시간이 넘는 구글링 끝에 내가 찾아낸 결론은 Spark에서는 PostgreSQL의 네이티브 함수를 완전히 동일하게 사용하지 못하는 경우들이 왕왕 있다는 것이었다. 즉, ARRAY_AGG()를 Spark에서도 사용할 수 있지만, Spark SQL에서는 ARRAY_AGG() 함수에 ORDER BY 조건을 직접 적용할 수 없는 제한이 있다. 그리고 이는 Apache Spark를 기반으로 하는 클라우드 기반의 데이터 분석 플랫폼인 Databricks에서도 동일했다.

*참고: Spark에서 ARRAY_AGG()를 사용할 수 있었던 것도 3.3.0 버전부터로, 그 전까지는 함수 자체가 존재하지 않았다고 한다.

 

구글링하다 나와 똑같은 문제를 겪는 사람을 찾으면 그렇게 반가울 수가 없다...!

 

그러면 Databricks에서 원하는 조건의 순서대로 데이터를 배열화해야 할 경우에는 어떻게 해야 할까?

 

COLLECT_LIST() & SORT_ARRAY()

정답은 바로  COLLECT_LIST()  함수와  SORT_ARRAY()  함수를 사용하는 것이다.

 

위의 가상의 데이터를 사용하여 구체적으로 설명해보자면 다음과 같다.

 

1. event_time와 film을 연결하여 정렬 기준을 만든다. 

CONCAT(CAST(event_time AS STRING), '_', film) AS combined

 

2. SORT_ARRAY()와 COLLECT_LIST()를 조합하여 데이터를 원하는 순서로 배열에 집계한다.

sort_array(COLLECT_LIST(combined)) AS sorted_combined

 

3. 정렬된 배열에서 정렬 키(event_time)를 제거하여 최종 데이터를 얻는다.

ARRAY_AGG(regexp_replace(combined, '^[^_]+_', '')) AS film_lst

 

 

각 CTE들을 연결해 전체 SQL 쿼리를 작성하면 이런 형태가 된다.

WITH film_events AS (
    SELECT
        event_date,
        user_id,
        CONCAT(CAST(event_time AS STRING), '_', film) AS combined
    FROM film_table
), sorted_film_events AS (
    SELECT 
        event_date, 
        user_id, 
        sort_array(COLLECT_LIST(combined)) AS sorted_combined
    FROM film_events
    GROUP BY user_id, event_date
)
SELECT 
    event_date,
    user_id,
    ARRAY_AGG(regexp_replace(combined, '^[^_]+_', '')) AS film_data_lst
FROM sorted_film_events
LATERAL VIEW explode(sorted_combined) exploded AS combined
GROUP BY user_id, event_date
ORDER BY user_id, event_date

 

Spark에 대해 잘 알고 있는 사람 입장에서는 '기본기'와 같은 지식일 수 있겠지만, 나는 Spark를 최근 들어서야 조금씩 알아가는 단계이다 보니까 애먹었다. 이걸 아는데만 오늘 오후 통째로 썼다ㅎㅎ; 예전에는 분석하고 싶어서 노트북 열었다가 오늘처럼 전처리하는 데에만 시간을 다 쓰면 ‘이게 맞나…?’ 싶었는데 이젠 전처리 과정도 분석의 중요한 일부라는 걸 깨달아서 그런지 괜찮아졌다. 몰랐던 함수도 이렇게 삽질하면서 하나하나 알아가는거지😇

---

결론

Databricks에서 특정 컬럼의 순서에 따라 배열을 반환하고 싶다면 SORT_ARRAY()와 COLLECT_LIST()를 활용한다!

---

참고문서

• https://neon.tech/postgresql/postgresql-aggregate-functions/postgresql-array_agg     
• https://docs.databricks.com/en/sql/language-manual/functions/sort_array.html  

 

 

+ 혹시 이 방법 외에 더 단순하고 간단한 방법이 있다면 말씀해주시면 감사하겠습니다!🙏