brunch

매거진 SQL 데이터 분석

라이킷 1 댓글

You can make anything
by writing

C.S.Lewis

계정을 잊어버리셨나요?

by 데이터리안 Jul 17. 2024

빅쿼리 UNNEST의 필요성과 사용법

빅쿼리는 왜 이렇게 특이한 문법이 필요할까요?

◆ Google BigQuery 시리즈 모아보기

1. 빅쿼리에 데이터를 연결하는 3가지 방법

2. UNNEST의 필요성과 사용법

Google BigQuery를 쓰다보면 아래와 같이 생긴 데이터를 만나게 됩니다.

이전 글을 통해 Google Analytics를 Google Bigquery에 연결하고나서 데이터를 보려고 했더니 일반적인 데이터 형태가 아니라서 당황한 분들도 있을거예요. MySQL 등 다른 데이터베이스에서는 한 행에 데이터가 1개씩 있는데 이 데이터는 어딘가 이상하게 생겼죠.

데이터리안 웹사이트 Google Analytics 로그 일부

맨 앞에 있는 컬럼부터 살펴보면 event_date, event_timestamp, event_name까지는 한 행에 데이터가 1개씩 들어있는데 event_params.key 라는 컬럼은 1번 행에만 8개의 데이터(batch_page_id, session_engaged, ga_session_id, page_title, ga_session_number, engaged_session_event, page_location, batch_ordering_id)가 들어가있습니다.

이 데이터를 가지고 ‘https://datarian.io/lecture’ 페이지에서 발생한 이벤트만 필터링하고 싶다면 어떻게 해야할까요?

일반적으로 이렇게 생각하실텐데요. 이 쿼리는 동작하지 않습니다.

SELECT *
FROM data
WHERE event_params.key = 'page_location'
AND event_params.value.string_value = 'https://datarian.io/lecture'

동작하지 않는 쿼리

'UNNEST'를 사용해야 원하는 결과를 얻을 수 있습니다.

SELECT *
FROM data
WHERE event_params.key = 'page_location'
AND event_params.value.string_value = 'https://datarian.io/lecture'

동작하는 쿼리

왜 이게 필요한거야?

Google BigQuery는 왜 이렇게 특이한 문법을 요구하는 데이터를 만들었을까요.

좀 더 힌트를 얻기 위해 이 데이터의 스키마를 살펴보겠습니다. event_params라는 컬럼의 데이터 유형은 ‘RECORD’입니다. Google BigQuery에서는 중첩 또는 반복이 있는 데이터 유형을 ‘RECORD’라고 지정할 수 있습니다. 모드에 ‘REPEATED’는 데이터의 반복이 있다는 것을 의미합니다. 여기에서는 여러개의 key, value 쌍이 있을 수 있다는 것을 의미합니다.

데이터리안 웹사이트 Google Analytics 로그 스키마

이 ‘RECORD’라는 데이터 유형이 왜 필요한지 더 잘 이해하기 위해서 일단 예시 데이터를 좀 더 자세히 볼 필요가 있습니다.

이 데이터는 데이터리안 웹사이트의 Google Analytics 로그입니다. Google Analytics는 사용자들의 웹사이트 내 페이지 뷰, 클릭 등 이벤트 데이터를 저장하고 분석하는 도구인데 이 데이터를 Google BigQuery에 연결하여 원본 데이터를 SQL로 조회하여 볼 수 있습니다. 아래와 같은 데이터들이 수집됩니다.

event_date: 이벤트 발생 날짜

event_timestamp: 이벤트 발생 시각

event_name: 이벤트 이름 (예. page_view, 25% Scroll)

event_params: 이벤트 파라미터

이벤트 파라미터는 이벤트 종류마다 다릅니다. page_view의 경우 아래와 같은 파라미터들을 수집합니다.

- page_title

- page_location

- batch_page_id

- session_engaged

- ga_session_id

- ga_session_number

- engaged_session_event

- batch_ordering_id

… 등

이와 같이 하나의 이벤트에는 여러 이벤트 파라미터, 즉 추가적인 정보들이 붙습니다. 이벤트가 발생한 페이지의 제목이 뭔지, 페이지 주소는 어디인지, 어떤 세션에서 발생한 것인지 등 ‘페이지 뷰라는 이벤트가 발생했다’는 정보와 함께 저장해야 하는 것들을 이벤트 파라미터로 저장하는 것이죠.

그런데 이 이벤트 파라미터는 어떤 이벤트냐에 따라 종류가 달라집니다. 예를 들어, scroll이라는 이벤트에는 page_view와 달리 percent_scrolled라는 이벤트 파라미터가 붙고 페이지에서 몇 % 까지 스크롤을 했는지를 저장합니다. 그러니까 이벤트 파라미터 정보들을 모두 테이블 컬럼으로 만드는 것 보다 유연하게 저장할 방법이 필요합니다.

아래처럼 모든 이벤트 파라미터를 컬럼으로 만드는 경우 사용하지 않는 컬럼이 많아집니다. 또한 새로운 파라미터를 추가하고 싶을 때 새로운 컬럼을 추가하는 등 테이블 구조 자체를 변경해야하기 때문에 비효율적입니다.

각주: 여기에서는 event_params 컬럼의 값을 이해하기 쉽도록 JSON 형식으로 표현했습니다. 또 실제로는 value의 데이터 타입이 여러가지이기 때문에 실제 Google Analytics 로그 데이터는 이보다 좀 더 복잡합니다. 여기에서는 ‘RECORD 데이터 유형이 있기 때문에 컬럼을 추가하는 것처럼 테이블 구조 변경을 하는 것보다 훨씬 더 유연하게 대처할 수 있겠구나’ 정도를 이해하시면 충분합니다.

UNNEST 사용법

이제 데이터를 저장하는 입장에서 이 구조가 필요한 것은 이해가 되셨나요? 제가 이 구조를 처음 봤을 때 가장 길게 한 고민이 ‘도대체 이걸 왜 이렇게 저장해놓은거지? 그냥 컬럼으로 저장해주면 안됐던건가?’였기 때문에 자세하게 얘기를 해보았어요.

자 이제, 이렇게 생긴 데이터를 꺼내서 쓰려면 어떻게 해야할까요?

데이터리안 웹사이트 Google Analytics 로그 일부

UNNEST로 데이터 평면화하기

일단 event_params 라는 컬럼에 있는 데이터들을 꺼내서 펼쳐주는 작업을 해야합니다. 이걸 평면화(Flatten)이라고 합니다. 'CROSS JOIN'과 'UNNEST()' 를 조합해서 위 데이터를 아래 이미지처럼 펼칠 수 있습니다.

SELECT *
FROM `datarian.analytics_234259439.events_20231204`
CROSS JOIN UNNEST(event_params) AS ep

‘RECORD’ 유형인 event_params 컬럼을 평면화하는 쿼리 ver 1

'CROSS JOIN' 은 명시를 안해줘도 동작하기 때문에 아래와 같이 쓸 수도 있습니다.

SELECT *
FROM `datarian.analytics_234259439.events_20231204`, UNNEST(event_params) AS ep
WHERE ep.key = 'page_title'
AND ep.value.string_value = 'https://datarian.io/lecture'

‘RECORD’ 유형인 event_params 컬럼을 평면화하는 쿼리 ver 1

‘RECORD’ 유형인 event_params 컬럼을 평면화하는 쿼리 결과

데이터 평면화를 이미지로 잘 보여주는 자료가 있어 아래에 첨부합니다.

이미지 출처: How to use the UNNEST function in BigQuery to analyze event parameters in Analytics

이제 드디어 한 행에 여러 데이터가 들어가있는 이상한 구조의 데이터가 아니라 한 행에는 데이터가 1개 들어가있는 우리 눈에 익숙한 데이터가 되었습니다!

평면화 한 데이터 필터링하기

이렇게 평면화 한 데이터를 이용해 WHERE 절에서 데이터를 필터링 할 수도 있습니다. 아래 쿼리는 특정 페이지에서 발생한 이벤트 목록을 필터링하는 쿼리입니다.

SELECT *
FROM `datarian.analytics_234259439.events_20231204`
     CROSS JOIN UNNEST(event_params) AS ep
WHERE ep.key = 'page_title'
  AND ep.value.string_value = 'https://datarian.io/lecture'
SELECT * FROM `datarian.analytics_234259439.events_20231204`      CROSS JOIN UNNEST(event_params) AS ep WHERE ep.key = 'page_title'   AND ep.value.string_value = 'https://datarian.io/lecture'

피봇테이블을 응용하여 좀 더 보기 쉬운 데이터 형식으로 가공하기

조금 더 익숙한 형태는 page_title 등 이벤트 파라미터들이 컬럼으로 구성되어있는 형태이겠지요. 그런 모양은 테이블 피봇을 응용한 쿼리를 이용해 만들 수 있습니다. SQL 데이터 분석 캠프 | 실전반에서 연습용 GA 데이터를 가지고 퍼널 분석과 세션 정의하기를 배우는데요. solvesql의 플레이그라운드에 업로드 되어있는 이 연습용 GA 데이터들은 다 이런 가공 과정을 통해 만들어졌습니다.

SELECT event_date
     , event_timestamp
     , event_name
     , MAX(CASE WHEN ep.key = 'page_title' THEN ep.value.string_value END) AS page_title
FROM `datarian.analytics_234259439.events_20231204`
     CROSS JOIN UNNEST(event_params) AS ep
GROUP BY event_date, event_timestamp, event_name

마무리

Google BigQuery의 가장 큰 특징으로 여겨지는 RECORD에 대해서 얘기해봤습니다. 막연하게 복잡하다고 생각하고 계셨다면 이 글이 막연함을 해소해주는 역할을 했길 바랍니다. 좀 더 자세한 설명은 참고 자료에 넣어둔 Google Cloud 공식 문서에 있으니 관심이 있으시다면 꼭 읽어보길 바랍니다.

심화 학습을 원하시는 분들은 'ARRAY', 'STRUCT'에 대해서 공부하시는게 좋습니다. Google Cloud의 배열 작업 이라는 문서가 매우 잘 쓰여져있으니 참고해주세요.