bigquery - ARRAY와 관련된 몇 가지 활용 팁 (SAFE_OFFSET, STRING_AGG, ML.NGRAMS)

Bigquery의 특징 중 하나는 ARRAY라는 형식을 지원한다는 것이다.

몇 년 전의 나는 ARRAY라는 것이 일반 SQL에는 잘 쓰지 않기 때문에 사용할 일이 없다고 생각했었으나, 그건 천만의 말씀 만만의 콩떡이었다. 현재는 쿼리를 짤 때 굉장히 애용하고 있는 요소라 정리하는 차원에서 글을 써보려고 한다.

 

Array는 무엇이고, 왜 쓰는가?

 

사실 Array가 무엇인지, 어떻게 쓰는지에 대해서는 굉장히 정리가 잘 된 글들이 많고,

https://zzsza.github.io/gcp/2020/04/12/bigquery-unnest-array-struct/

BigQuery UNNEST, ARRAY, STRUCT 사용 방법

 

 

구글 클라우드에서 자체적으로 제공하는 가이드도 굉장히 잘 되어 있는 편이기도 하다.

https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays?hl=ko 

배열 작업  |  BigQuery  |  Google Cloud

 

Array를 써먹고 있는 사람의 입장에서 몇 가지 의견을 더하는 형태로 글을 써보면

 

• Array는 Python의 List와 비슷한 개념이다.
• 리스트와 비슷하게 Index 개념이 있다.

정도가 특징인데, 이 특징을 데이터를 세로로 길게 만들어야 하는 상황이 올 때 주로 써먹게 된다.

 

가령, 

 

이런 데이터가 있을 때, 각 메시지가 무엇을 의미하는지 정보를 보여주고 싶을 수 있다.

 

 

 

이 모양으로 데이터를 만들 때, Array 개념으로 접근하면, 매우 쉽게 문제를 해결할 수 있다. 

 

with message_data AS
(
  SELECT 'A 'as user_id, 200 AS message_1, 200 AS message_2, 404 AS message_3
)
SELECT
  * EXCEPT(message_array)
FROM  
  (
    SELECT
      user_id,
      array[message_1, message_2, message_3] AS message_array
    FROM message_data
  ),unnest(message_array) AS message

array로 각 컬럼을 하나의 리스트로 만들고 UNNEST를 통해 풀어버리면 쉽게 가로로 긴 데이터를 세로로 길게 만들 수 있다.

 

 

다른 방법을 쓸 수도 있다.

with message_data AS
(
  SELECT 'A 'as user_id, 200 AS message_1, 200 AS message_2, 404 AS message_3
)

    SELECT
      user_id,
      message_1
    FROM message_data

    UNION ALL
    (
    SELECT
      user_id,
      message_2
    FROM message_data  
    )
    UNION ALL
    (
    SELECT
      user_id,
      message_3
    FROM message_data
    )

이렇게 UNION ALL을 써서 데이터를 길게 만드는 방법도 있지만 쿼리가 길어진다는 단점이 있다.

 

 

혹은 인덱스 정보가 필요할 때 Array를 사용하기도 하는데  가령 

이런 데이터가 있을 때 포켓몬 공격력이 3번째로 높은 데이터를 갖고 오고 싶다고 가정해보자.

여기서는 이상해씨 - 피카츄 - 꼬부기 - 파이리 순으로 공격력이 높기 때문에 꼬부기를 얻어올 수 있으면 된다.

 

with pokemon_data AS
(
  SELECT 1 AS user_id, STRUCT('피카츄' AS pokemon_name, 30 AS attack, 100 AS hp) AS pokemon_info
  UNION ALL
  SELECT 1 AS user_id, STRUCT('꼬부기' AS pokemon_name, 45 AS attack, 80 AS hp) AS pokemon_info
  UNION ALL
  SELECT 1 AS user_id, STRUCT('파이리' AS pokemon_name, 55 AS attack, 70 AS hp) AS pokemon_info
  UNION ALL
  SELECT 1 AS user_id, STRUCT('이상해씨' AS pokemon_name, 20 AS attack, 120 AS hp) AS pokemon_info
)


SELECT
  *  
FROM
  (
    SELECT
      user_id,
      ARRAY_AGG(pokemon_info ORDER BY pokemon_info.attack) AS pokemon_array
    FROM pokemon_data
    GROUP BY
      user_id
  ),UNNEST(pokemon_array) with offset AS idx
WHERE idx = 2

이 때 포켓몬 공격력 순으로 정렬한 형태로 array를 만들고, list에서 인덱스를 알면 특정 데이터를 가져올 수 있는 것처럼 array에서 index를 가져오면 원하는 데이터를 가져올 수 있다.

 

 

이렇듯 array를 쓰면 원하는 모양에 맞춰 데이터를 쉽게 조작할 수 있다는 장점이 있다.

 

이제 데이터를 다시 쪼개고, 모으면서 Array를 쓸 때 알아두면 도움이 될 만한 내용들을 정리하려고 한다. (한 번씩은 내가 삽질을 했던 내용들이다....)

 

 

1. SAFE_OFFSET

 

앞서 array는 리스트와 비슷하기 때문에 원하는 데이터를 인덱싱해서 가져올 수 있다고 했다.

 

이런 데이터가 있을 때 각 유저(?)마다 두 번째로 잡은 포켓몬을 필터링하고 싶다고 하자.

 

with pokemon_data AS
(
  SELECT '지우' AS user_name, array['꼬부기', '피카츄', '캐터피', '구구'] AS pokemon_array
  UNION ALL
  SELECT '이슬' AS user_name, array['별가사리', '고라파덕', '아쿠스타']
  UNION ALL
  SELECT '웅이' AS user_name, array['꼬마돌', '롱스톤', '식스테일']
  UNION ALL
  SELECT '관철' AS user_name, array['마릴']
)


SELECT
  user_name,
  pokemon_array[offset(1)] as second_pokomon
FROM pokemon_data

 

여기서 바로 offset을 쓰면 오류가 난다. 왜냐하면 '관철'은 포켓몬이 1마리이기 때문에 2번째 포켓몬을 가져올 수 없기 때문이다. 

 

처음에는 1마리만 있는 데이터를 버려오는 전략을 썼었다. 즉, array의 길이가 1 초과하는 데이터만 가져오는 것이다. (arary_length 사용)

 

with pokemon_data AS
(
  SELECT '지우' AS user_name, array['꼬부기', '피카츄', '캐터피', '구구'] AS pokemon_array
  UNION ALL
  SELECT '이슬' AS user_name, array['별가사리', '고라파덕', '아쿠스타']
  UNION ALL
  SELECT '웅이' AS user_name, array['꼬마돌', '롱스톤', '식스테일']
  UNION ALL
  SELECT '관철' AS user_name, array['마릴']
)


SELECT
  user_name,
  pokemon_array[offset(1)] as second_pokomon
FROM pokemon_data
WHERE array_length(pokemon_array) > 1

 

이렇게 쓰면 데이터를 아래와 같이 가져올 수 있다.

그런데 위 방법을 쓰면 '관철'에 대한 정보를 아예 가져올 수 없게 된다. 포켓몬 2마리가 없어도 데이터를 NULL로 가져오는 식으로 처리를 하고 싶을 수도 있은데 위 방법을 쓰면 데이터를 아예 가져올 수가 없다.

 

이럴 때, SAFE_OFFSET을 쓰면 되는데,

• 2번째 포켓몬이 있다면, 2번째 포켓몬을 가져오고
• 포켓몬이 1마리만 있다면 2번째 포켓몬을 가져올 수 없는데 이 때 NULL로 표시한다.

with pokemon_data AS
(
  SELECT '지우' AS user_name, array['꼬부기', '피카츄', '캐터피', '구구'] AS pokemon_array
  UNION ALL
  SELECT '이슬' AS usr_name, array['별가사리', '고라파덕', '아쿠스타']
  UNION ALL
  SELECT '웅이' AS user_name, array['꼬마돌', '롱스톤', '식스테일']
  UNION ALL
  SELECT '관철' AS user_name, array['마릴']
)


SELECT
  user_name,
  pokemon_array[safe_offset(1)] as second_pokomon
FROM pokemon_data

 

데이터는 이런 모양으로 나오고, 보다 쿼리도 간결해진다.

 

 

 

 

2. STRING_AGG

 

사실 이건 Array에 대한 이야기는 아니다. 다만 array에만 함몰되면 못 보는 부분이 있어서, 이 부분에 대해 정리해보려고 한다.

 

이번에는 array로 묶이지 않은 데이터를 갖고 있다고 해보자. (위의 예시를 UNNEST로 풀면 아래 데이터 형태가 된다.)

이 데이터를 아래와 같이 갖고 있는 포켓몬을 "-"로 연결하는 한 줄의 데이터를 갖고 싶다고 하자.

 

ARRAY_AGG를 써서 문자열을 array로 묶고, 묶은 Array를 array_to_string을 써서 하나의 문자로 만들 수 있다.

 

with pokemon_data AS
(
  SELECT '지우' AS user_name, array['꼬부기', '피카츄', '캐터피', '구구'] AS pokemon_array
  UNION ALL
  SELECT '이슬' AS user_name, array['별가사리', '고라파덕', '아쿠스타']
  UNION ALL
  SELECT '웅이' AS user_name, array['꼬마돌', '롱스톤', '식스테일']
  UNION ALL
  SELECT '관철' AS user_name, array['마릴']
),
pokemon_data_long AS
(
    SELECT
      * EXCEPT(pokemon_array)
    FROM pokemon_data,UNNEST(pokemon_array) AS pokemon_name
)

SELECT
  user_name,
  ARRAY_TO_STRING(pokemon_array, "-") AS pokemon_list
FROM
  (
    SELECT
      user_name,
      ARRAY_AGG(pokemon_name) AS pokemon_array
    FROM pokemon_data_long
    GROUP BY
      user_name
  )

 

그렇지만 문자를 Array로 묶었다 풀었다 하는 모습이 어쩐지 번잡스러워 보인다.

이럴 때 쓰는 게 STRING_AGG인데, 문자열을 원하는 구분자(예시에서는 대시(-))로 묶어서 보여준다.

 

with pokemon_data AS
(
  SELECT '지우' AS user_name, array['꼬부기', '피카츄', '캐터피', '구구'] AS pokemon_array
  UNION ALL
  SELECT '이슬' AS usr_name, array['별가사리', '고라파덕', '아쿠스타']
  UNION ALL
  SELECT '웅이' AS user_name, array['꼬마돌', '롱스톤', '식스테일']
  UNION ALL
  SELECT '관철' AS user_name, array['마릴']
),
pokemon_data_long AS
(
    SELECT
      * EXCEPT(pokemon_array)
    FROM pokemon_data,UNNEST(pokemon_array) AS pokemon_name
)


SELECT
  user_name,
  STRING_AGG(pokemon_name, "-") AS pokemon_list
FROM pokemon_data_long
GROUP BY
  user_name

 

ARRAY에 현혹되면 모든 걸 다 Array로 처리하고 싶어지는데, 이럴 때 쉽게 가는 길을 돌아서 가게 되는 불상사를 만들어 낼 수 있어 주의해야 한다.

 

 

3. ML.NGRAMS

 

가령, "첫 번째 포켓몬을 물 포켓몬을 선택하면, 유저는 약점을 보완해주기 위해 두 번째 포켓몬으로 구구(비행 속성)를 잡는 경향이 있을 것이다" 라는 가설을 세웠다고 가정해보자.

 

(참고로 물 포켓몬은 풀 포켓몬에 약하고, 비행 포켓몬은 풀 포켓몬에 강하다)

 

그리고 나는 이런 데이터를 가지고 있다고 해보자.

 

이 때 내가 원하는 데이터는

 

유저가 잡은 첫번째 포켓몬 - 유저가 잡은 두번째 포켓몬 - 유저 수에 대한 정보이다.

이런 데이터 형태를 만들기 위해서는 다양한 방법을 사용할 수 있겠으나, 첫번째 - 두번째 포켓몬의 조합을 만들어서 처리하면 간단하게 처리할 수 있을 것 같다는 생각이 든다.

 

다른 SQL에도 지원하는 기능인지는 모르겠지만, bigquery에는 조합(Combination)을 만들어주는 ML.NGLAMS 라는 함수가 있다.

 

해당 함수에 Array를 넣어주고, 몇 개의 조합을 만들고 싶은지, 그리고 구분자로 어떤 걸 넣어주고 싶은지를 parameter로 넣어주면 된다!

 

with org_data AS
(
  SELECT 
    *
  FROM `get_pokemon_log`
),
array_data AS
(
    SELECT
      user_id,
      ARRAY_AGG(pokemon_name ORDER BY get_time) AS pokemon_array
    FROM    
      (
        SELECT
          *,
          ROW_NUMBER() OVER (partition by user_id ORDER BY get_time) AS idx
        FROM org_data
      )
    GROUP BY
      user_id
) SELECT
      * EXCEPT(pokemon_array),
      ML.NGRAMS(pokemon_array, [1, 3], "-")
    FROM array_data

맨 아래줄 쿼리 중 ML.NGRAMS가 조합을 만들어주는 함수 부분이고, [1, 3]이라고 쓰인 부분은 조합을 1개 ~ 3개까지 만들어 달라는 뜻이다.

 

그러면 이런 식으로 1~3개 사이의 조합을 만들어준다.

 

그럼 위 명령어를 사용해서, 첫번째 - 두번째 포켓몬의 조합을 만들어보자.

with org_data AS
(
  SELECT 
    *
  FROM `get_pokemon_log`
),
array_data AS
(
    SELECT
      user_id,
      ARRAY_AGG(pokemon_name ORDER BY get_time) AS pokemon_array
    FROM    
      (
        SELECT
          *,
          ROW_NUMBER() OVER (partition by user_id ORDER BY get_time) AS idx
        FROM org_data
      )
    WHERE idx <= 2
    GROUP BY
      user_id
),
combination_data AS
(
    SELECT
      *
    FROM array_data, UNNEST(ML.NGRAMS(pokemon_array, [2], "-")) AS pokemon_tuple
)
  SELECT    
    SPLIT(pokemon_tuple, "-")[SAFE_OFFSET(0)] AS pokemon_1,
    SPLIT(pokemon_tuple, "-")[SAFE_OFFSET(1)] AS pokemon_2,
    COUNT(1) AS get_cnt
  FROM combination_data
  GROUP BY
    pokemon_1,
    pokemon_2

 

그러면 위와 같은 결과를 얻을 수 있다.

사실 의도적으로 조작(?)해서 만든 데이터긴 하지만... 꼬부기를 잡은 경우 두번째 포켓몬으로 유저는 구구를 선택하고 그렇지 않은 경우(파이리, 이상해씨)는 피카츄를 데려가는 경향이 있는 것을 확인할 수 있다. 

 

---

 

Array는 잘 써먹기만 하면 데이터 분석을 쉽게 만들어주기 때문에 유용한 도구라고 말할 수 있겠다.

Array를 통해 진짜로 골치아픈 일을 해치워버릴 수 있으면 하는 바람으로 글을 마쳐본다.