[Big Query] 빅 쿼리 UNNEST 사용법 / 개념

Key-Value

데이터 구조 이해하기

우리가, 아니 적어도 내가 일반적으로 봐 왔던 데이터들은 이렇게 Key-Value 쌍으로 이루어진 Record의 연속이었다.

그런데 이번에 회사에서 일하면서 Big Query를 만지게 되었는데 데이터 구조가 생판 처음보는 식으로 되어있었다!

그림으로 표현하자면...이렇게?

 

필드 하나가 배열이 될수도 있고, 구조체가 되거나 구조체의 배열까지 될수 있는 복잡한 구조다.

 

각 필드는 Array, Stuct, Array of Struct까지 될 수 있다.

 

 

예제로 알아보기

그래서 '일반적인' SQL문으로 내가 원하는 데이터를 뽑아내기는 어렵고, 특별한 문법요소 하나를 더 써야하는데 그것이 바로 UNNEST이다.  손을 더럽히면서 배워보자. bigquery.cloud.google.com/dataset/firebase-analytics-sample-data:android_dataset 이 주소로가서

Go to Google Cloud Console 버튼을 클릭하면 샌드박스 모드에서 빅 쿼리를 만져볼 수 있다.

 

SELECT event_dim 
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607` 
LIMIT 50

샌드박스에서 이렇게 입력하면,  아래와 같은 결과들이 나온다.

분명 필드는 event_dim 하나 밖에 집어주지 않았지만, event_dim 하위에 name, params 그리고 params 하위에 key-value 쌍이 있는 형태로 되어있기 때문이다.

 

 

위 에서 event_dim.name 은 round_completed, unlock_achievement, item_purchased 이렇게 여러개가 있다. 이럴때 event_dim.name이 "round_completed" 인 레코드만 불러오는 방법은 없을까?

SELECT event_dim 
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607` 
WHERE event_dim.name = "round_completed"

삐-빅 이렇게하면 오류가 뜬다. "round_completed"라는 String  값을 배열 안의~구조체의~요소와 비교하고 있기 때문이다. 이를 해결하기 위해서는 앞서 언급했듯 UNNEST 가 필요하다.

 

event_dim의 구조

 

CROSS JOIN UNNEST는 배열을 쪼개서 각 원소들을 원래의 Record에 복사해서 Join을 하는건데....

어렵지만 아래 사진들을 보면 조금...감이올것이다.

 

15 아래의 소수

"primes under 15"라는 항목하나와 primes_array 배열이 쌍으로 있는 Record가 있다.

 

이걸  CROSS JOIN UNNEST 로 쪼개면 다음과 같이 Flat하게 만들 수 있다. 

그리고 CROSS JOIN UNNEST 는 줄여서, 쉼표(,) UNNEST로 사용할 수 있다. 그리고 아래 SQL처럼 UNNEST한 요소에 조건을 걸 수도 있다.

WITH data AS (
  SELECT "primes under 15" AS description,
  [1,2,3,5,7,11,13] AS primes_array)
SELECT description, prime 
FROM data, UNNEST (primes_array) as prime
WHERE prime > 8

 

다시 원래 문제로 돌아와서, event_dim.name이 "round_completed"인 레코드만 뽑고 싶다면 어떻게 해야할까?

바로 UNNEST로 event_dim을 쪼개서, name = round_completed 조건을 걸어주면 된다.

SELECT event_dim 
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607`,
UNNEST (event_dim) as event
WHERE event.name='round_completed'

round_completed만 필터링 된 모습

 

짜잔! 의도대로 event_dim.name이 "round_completed"인 레코드만 걸러졌다.

그러면 더 나아가서, 이 상태에서, event_dim.params.key=score이고, 그 score가 40000 이상인 레코드만 뽑고싶다면 어떻게 해야할까?

SELECT event.name, ep.key, ep.value.int_value as score
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607`,
UNNEST (event_dim) as event,
UNNEST (event.params) as ep
WHERE event.name='round_completed' AND ep.key='score' AND ep.value.int_value > 40000

 

event_dim에서 event_dim.params에 한번 더 UNNEST를 해서 개별 key에 조건을 걸수있게되었다.

쿼리 결과는 다음과 같다.

 

 

새벽 한시...이제 자야해서 더 이상 못 쓰겠다. 그룹핑, 카운팅에 관한 튜토리얼은 아래 링크에 더 있으니 참조하길 바란다.

firebase.googleblog.com/2017/03/bigquery-tip-unnest-function.html