-
Big query를 이용한, 데이터 추출카테고리 없음 2020. 3. 5. 20:34
앱에 대한 사용성 데이터에는 크게 Firebase 기본 통계치 와 직접 함수를 삽입하여 수집하는 통계치들이 있다. 모두 Firebase에서 수집되고 어느정도 dash board를 통해, 시각화를 자동적으로 해준다. 하지만, 데이터를 다양한 방식으로 보기위해서는 계속해서 추출 방식을 새롭게 정의해주어야한다. 즉, Raw data에서 필요한 조건에 따라 새롭게 데이터를 추출할 필요가 있었다.
이번 시간에는 Raw data를 다양한 방식으로 뽑아내기 위한 SQL문을 다룰 것이다. 이때 Non-SQL의 대세인(그보다..지금 써본? ㅎㅎ) Big query를 써볼 것이다.
다음은 빅쿼리에서 Standard SQL 문법을 간단하게 소개하고 있다.
{기본 SQL 형식}
SELECT *
FROM '프로젝트.데이터셋.테이블명'
WHERE 조건
ORDER BY 컬럼
https://jungwoon.github.io/bigquery/2017/10/31/BigQuery-Syntax/BigQuery 기본 사용법 - Jungwoon Blog
BigQuery 기본 사용법 Big Query에서 사용하는 문법은 크게 Legacy SQL와 Standard SQL이 있는데, LegacySQL은 Big Query 초기부터 사용되던 문법이나 2.0 이후에 Standard SQL이 지원되었기 때문에, 여기서는 Standard SQL 기준으로 포스팅하도록 하겠습니다. (좀더 자세히 알고 싶으신 분들은 구글 문서를 참조하시기 바랍니다) Legacy SQL자세한 설명은 변성윤님의 Github에 매우 자세히 나와
jungwoon.github.io
Google Bigquery는 1:n 매칭의 데이터를 가지며, 기존의 RDBMS와 달리 비정형 데이터 구조를 따른다. 따라서, 분석에 용이하게 데이터 구조를 뽑아내는 꾸울팁을 소개한다.
하나의 ID로 조인을 하면 중복된 이벤트 행이 많이 발생하는데, 해당 이벤트를 ARRAY의 형태로 묶어서 저장할 수 있다.
이를 통해, 사용자 당 발생한 어떠한 이벤트를 얼만큼 사용했는지 효과적으로 묶어줄 수 있다.
https://cojette.github.io/bigqueryandarray/
Google BigQuery 쉽게 쓰게 하기 (feat. ARRAY)
서두용 아무말
cojette.github.io
그렇다면, 이제 실제 데이터에 적용해보자. 샘플로 준비한 데이터는 FCM에 대한 데이터이다. FCM은 Firebase Cloud Message로서 앱과 연동된 Firebase상에서 알람 메세지를 보내는 기능으로 앱에 대한 Retention을 높이고자 활용된다.
FCM에 대하여 데이터를 Bigquery와 연동시키면 메세지에 대한 오픈 여부, 앱실행 여부를 상세하게 체크할 수 있다.
SQL의 가장 큰 이점은 서브 쿼리를 만들 수 있다는 점인데, FROM 뒤에 SELECT구문을 하여 쿼리속에 또 하나의 쿼리를 넣을 수도 있지만,
다음처럼 WITH 별칭 AS (데이터 테이블) 을 함으로써 서브 쿼리를 지정할 수 있다. 아래 쿼리는 WHERE문으로 앱 이름과 OS가 IOS버전인지 또한 해당 메세지가 서버상에서 유효한 지 조건을 걸어 주었다.
또한, EXTRACT구문을 보면, timestamp를 날짜만, 일자만, 년, 월 단위로 변환시켜 줄 수 있다. 날짜 단위를 필요한 부분만 표현해주면 훨씬 쿼리 테이블에서 보기가 편하다.
WITH FB_TABLE AS (
SELECT EXTRACT(DATE FROM event_timestamp) as the_day, app_name,device_recently_active,message_type,event,sdk_platform FROM `###.firebase_messaging.data`
WHERE app_name ='###' AND sdk_platform ='IOS'AND event = 'MESSAGE_ACCEPTED'
)
SELECT the_day, count(*) as MESSAGE_OK
FROM FB_TABLE
WHERE the_day = '2020-02-24' AND event = 'MESSAGE_ACCEPTED'
GROUP BY the_day
ORDER BY the_day@참고자료 (Google hompage)
BIG QUERY SQL구문소개: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions?hl=ko
FCM 이벤트 소개 :https://firebase.google.com/docs/cloud-messaging/understand-delivery#what-data-exported