firebase + BigQuery 이벤트로그 쿼리 Tip

이벤트 로그의 쿼리가 복잡해서 정리 할겸 남기는 글. 

이벤트 로그란? 

이벤트는 유저의 행동, 로그는 기록이 남는 것으로 어떠한 특정 버튼을 클릭하거나 스크롤을 하거나 하는 행동이 있을경우 각 이벤트마다 로그를 남기는 것을 이벤트 로그라고 한다. DB에 저장된 정보만으로는 알 수 없는 유저의 행동 데이터를 수집해서 어떤 식으로 서비스를 이용하고 있는지 분석해서 앞으로 서비스 방향성을 잡거나 기능에 대해 평가를 진행 할 수 있다. 

Firebase-BigQuery

firebase를 이용하여 앱 로깅을 할 수 있으며, 구글 빅쿼리와 연동해 대용량 로그를 실시간에 근접하게 쌓을 수 있다. 기본적으로 session id, device 정보, event_timestamp 같은 정보는 따로 설정해주지 않아도 공통으로 들어가게 된다. 

신규서비스 오픈 후 이벤트 로그 데이터에 대한 니즈가 계속 늘어나서 학생이 이용할 수 있는 페이지에 우선순위대로 이벤트를 정의하는 작업을 진행했었다. 이벤트 정의 시 액션 / 파라미터로 나누어서 각 액션이 일어났을때 같이 수집되어야 하는 정보들을 파라미터로 넣어서 받을 수 있도록 했다. 실제로 이벤트를 연동하는 작업은 프론트엔드팀에서 진행했기 때문에 애매한 부분들은 계속 대화하면서 작업했다. (파라미터로 어떤 값을 넣을 수 있을지 모르겠다면 개발자도구에서 graphQL 스키마를 확인하면서 작업하면 시간이 절약된다!)

이벤트 로그 쿼리

firebase-bigquery 연동 작업을 하게 되면 아래와 같이 빅쿼리 테이블에 사전에 정의한 이벤트 값이 액션마다 들어가게 된다. (day 마다 새로운 테이블로 변경) 

스키마 일부

스키마를 보면 event_params 라는 RECORD repeated 컬럼에 key, value 가 나뉘어서 이벤트 로그를 저장한다. 기본 파라미터 + 우리가 직접 정의해 넣은 파라미터가 전부 하나의 event_params 안에 record 형태로 쭉 들어가있다. 키에 들어갈 값이 int 면 int_value에, string 이면 string_value에만 값이 들어가고 나머지 value는 null 처리 되어있다. 

이 record 유형이 배열로 들어 갈 수 있다는 점이 빅쿼리의 특징이자 쿼리가 복잡해지는 이유이기도 하다. key, value 값이 반복적으로 배열로 들어가기 때문에 일반적인 쿼리로는 key를 조회 할 수 없고 반드시 UNNEST를 통해 값을 풀어 준 후에 select문에 지정해서 조회를 할 수 있다.

아래 코드같이 UNNEST 하지 않고 repeated 열을 조회하면 에러가 난다. 

SELECT
  event_params,
  FROM  `이벤트테이블` 
WHERE
event_name = 'screen'
and event_params.key = 'screen_name'
and event_params.value.string_value = 'report'

아래와 같이 event_params를 UNNEST 해서 각 키 밸류를 한줄 한줄로 풀어주고, key가 screen_name이고 report라는 string value인 값만 조회 할 수 있도록 쿼리를 수정해줘야 한다. 

SELECT
  event_params,
  FROM  `이벤트테이블` ,
  UNNEST (event_params) as ep
WHERE
event_name = 'screen'
and ep.key = 'screen_name'
and ep.value.string_value = 'report'

추가적으로, 이벤트 로그 테이블에 존재하는 자체 event_timestamp 열은 완전히 정확한 시간이 아니어서 event_parmas에 존재하는 시간으로 정렬해야 제대로된 이벤트 순서대로 정렬이 가능한다. 로그 저장이 완전히 실시간까지는 아닌데, event_timestamp는 여러개 로그가 모여서 한번에 DB로 들어올 때의 시간으로 찍혀서 자세한 정렬이 되지 않는다. event_params의 timestamp 키의 int_value 안에 밀리세컨드로 시간이 들어가 있기 때문에 해당 값을 또 다시 UNNEST로 풀어서 가져온 후 datetime 파싱을 해주면 된다. 

만약 다른 키 값을 조회하고 싶다면 UNNEST를 조회하고 싶은 키값마다 사용해서 풀어주어야 한다. 조건문이 많아진다면 UNNEST도 엄청나게 늘어나게 된다.. 아래는 datetime 파싱과 여러개의 UNNEST를 적용한 최종 예시코드이다. 

SELECT ep3.value.string_value as user_id,
    PARSE_DATETIME('%s', CAST(TRUNC(ep2.value.int_value/1000) AS STRING)) as real_time,
    ep4.value.int_value as session_id,
FROM `테이블이름`,
    UNNEST (event_params) as ep1,
    UNNEST (event_params) as ep2,
    UNNEST (event_params) as ep3,
    UNNEST (event_params) as ep4
where 
     ep1.key = 'screen_name'
     and ep1.value.string_value in ('원하는 screen name 값')
     and ep2.key = 'timestamp'
     and ep3.key = 'userId'
     and ep4.key = 'ga_session_id'
order by ep2.value.int_value desc