GCP Bigquery

일이 늘어났다......

Published on Jun 1, 2023

개요

BigQuery 사용 후기입니다.

서론

누군가의 퇴사로 인해 BigQuery에 있는 데이터 활용을 제가 하게 되었습니다.

쿼리 개선, 대시보드 구성… 한마디로 일이 늘어났습니다.

BigQuery는 무엇인가?

BigQuery는 큰 데이터에 대한 SQL 쿼리를 빠르게 수행해주는 GCP 서비스 중 하나입니다.

몇 가지 간단한 설정으로 애플리케이션에서 발생한 로그, 예를 들면 Firebase 데이터를 쉽게 BigQuery로 이관하고 활용할 수 있습니다.

개선한 쿼리

UNNEST 기능에 대한 부분적인 쿼리 개선을 했습니다.

// unnest 의 무분별한 호출을 우선 변경하였다 성능도 테스트 당시 만족스러웠다

// 기존 쿼리
SELECT
    event_date
    , p1.value.string_value as p1
    , p2.value.string_value as p2
    , p3.value.string_value as p3
    , p4.value.string_value as p4
  from `{프로젝트}.events_*`
    , unnest(event_params) as p1
    , unnest(event_params) as p2
    , unnest(event_params) as p3
    , unnest(event_params) as p4
where
    _table_suffix between '20230601' and '20230601'
    and p1.key = 'p1'
    and p2.key = 'p2'
    and p3.key = 'p3'
    and p4.key = 'p4'
group by user_pseudo_id,event_timestamp

// 변경 쿼리
 select
    event_date
    , MAX(if(param.key = "p1", param.value.string_value, NULL)) as p1
    , MAX(if(param.key = "p2", param.value.string_value, NULL)) as p2
    , MAX(if(param.key = "p3", param.value.string_value, NULL)) as p3
    , MAX(if(param.key = "p4", param.value.string_value, NULL)) as p4
    from (
        SELECT event_date, user_pseudo_id, event_timestamp, param
        from `{프로젝트}.events_*`
    , UNNEST(event_params) AS param
        where
            _table_suffix between '20230601' and '20230601'
    )
group by user_pseudo_id, event_timestamp

DAU, WAU, MAU를 간략하게나마 볼 수 있도록 제공했습니다.

이쪽은 전문 분야가 아니다 보니, 다음과 같은 결과가 나오도록 작업했습니다.

  • DAU/WAU/MAU: 일별 접속한 사용자 전체
  • 복귀: 14일 동안 접속이 없는 회원
  • 신규
  • 기존

복귀와 신규를 구분하려면 회원 단위 데이터도 필요했습니다. 스케줄링으로 처리할 경우 다음과 같이 데이터가 생성됩니다.

이때 스케줄링 처리는 Vertex AI를 사용했고, Python 기반으로 쿼리를 작성했습니다.

  • 유저 테이블: 회원, 최초 접속 확인 날짜
  • 회원 일 단위 접속 정보: 복귀 여부 판단을 위해 일 단위 접속 회원 기록
  • DAU: 일 단위 접속 회원 통계
  • WAU: 1주일 동안 접속한 회원 통계
  • MAU: 한 달 동안 접속한 회원 통계

최근 3일 데이터를 지속적으로 업데이트하고 있습니다.

“왜 3일 데이터를 조회 및 갱신하는가?”, “유저 테이블에 마지막 접속일을 넣으면 복귀 구분이 가능하지 않나?”라는 의문이 들 수 있습니다. 그 이유는 아쉬운 점에 남깁니다.

결과

최소한의 DAU, WAU, MAU 대시보드 데이터 구성이 되었습니다.

Spring Boot에서 기간을 넣으면 조회 가능하게도 구현했습니다.

아쉬운 점

너무 많습니다.

Spring Boot에서 BigQuery 호출 시 연결 속도나 쿼리 결과가 너무 느렸습니다.

그래서 배치를 돌려 DAU/WAU/MAU처럼 확정된 내용만 MySQL로 가져오는 식으로 할까 생각했지만, 내일의 나에게 미룬 상태입니다.

그리고 3일치 데이터를 처리하는 이유는 Firebase에서 BigQuery로 데이터 이관이 최대 3일까지 지속적으로 발생하기 때문입니다. 3일 전 내용이 오늘에서야 들어올 수 있는 구조라 3일 데이터를 조회 및 갱신하고 있었습니다.

4일 이전 데이터가 Firebase에 발생하더라도 BigQuery에는 반영되지 않습니다. 공식 문서에 써 있더군요.

그러다 보니 DAU/WAU/MAU 작업 시 위와 같은 구조로 처리하게 되었습니다.