GCP Bigquery

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

Published on Jun 1, 2023

개요

Bigquery 사용 후기

서론

누군가의 퇴사로 인해 Bigquery에 있는 데이터 활용을 내가 하게 되었다…
쿼리 개선 , 대시보드 구성 …한마디로 일이 늘어 났다?!

Bigquery는 무엇인가 ?

큰 데이터에 대한 SQL 쿼리를 빠르게 수행해주는 GCP 서비스 중의 하나 입니다.
몇가지 간단한 설정으로 어플리케이션 에서 발생한 로그(파이어베이스) 데이터를 쉽게 Biquery 이관하고 활용 할수 있습니다.

개선한 쿼리

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 를 사용하였고 파이썬 기반으로 쿼리를 작성하였다.

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

최근 3일 데이터를 지속적으로 업데이트 해주고 있다
왜 3일 데이터를 조회 및 갱신해주고 있는것인가 와
유저 테이블에 마지막 접속일을 넣고 하면 복귀 구분이 가능하지 않냐 ? 라는 의문의 들수 있다.. 그이유는 아쉬운점에 남기는거로 ..

결과

최소한의 dau , wau , mau 에 대한 대시보드 데이터 구성이 되었고
Spring boot 에서 기간을 넣으면 조회 가능하게도 구현 하였다.

아쉬운점

너무 많다 Spring boot 에서 bigquery 호출시 연결 속도나 쿼리 결과물이 너무 느렸다…..
그래서 배치를 돌려 dau,wau,mau 확정된 내용만 mysql 로 가져오는 식으로 할까 생각하였지만 내일의 나에게로 미룬 상태 이다 그리고 3일치 처리 하는 이유는 파이어베이스에서 bigquery 로 데이터 이관이 최대 3일까지 지속적으로 발생된다 3일전 내용이 오늘에서야 들어올수 있는 구조이다 보니 3일 데이터를 조회 및 갱신해주고 있었다 4일 이전 데이터가 만약 파이어베이스에 발생되어도 bigquery 에는 반영이 되지 않는다 ( 공식 문서에 써있더라.. ) 그러다 보니 dau,wau,mau 작업시처리한 구조가 되게 되었다…