Google BigQuery를 쓰다보면 아래와 같이 생긴 데이터를 만나게 됩니다.<a href="https://datarian.io/blog/3ways-to-connect-data-to-google-bigquery" target="_blank">이전 글</a>을 통해 Google Analytics를 Google Bigquery에 연결하고나서 데이터를 보려고 했더니 일반적인 데이터 형태가 아니라서 당황한 분들도 있을거예요. MySQL 등 다른 데이터베이스에서는 한 행에 데이터가 1개씩 있는데 이 데이터는 어딘가 이상하게 생겼죠.<figure data-type="image-block" data-src="https://attachments.datarian.io/production-659d2663-583b-496f-ac5e-d32620e51911-KZ9CgapP1QjWQ3Vz7RKwQloEVnY0D0tWJmNy5IGBElk.png" data-align="center" data-width="768" data-show-caption="true" style="width: 768px; max-width: 100%"><img src="https://attachments.datarian.io/production-659d2663-583b-496f-ac5e-d32620e51911-KZ9CgapP1QjWQ3Vz7RKwQloEVnY0D0tWJmNy5IGBElk.png" width="768" /><figcaption>데이터리안 웹사이트 Google Analytics 로그 일부</figcaption></figure>맨 앞에 있는 컬럼부터 살펴보면 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’ 페이지에서 발생한 이벤트만 필터링하고 싶다면 어떻게 해야할까요?일반적으로 이렇게 생각하실텐데요. 이 쿼리는 동작하지 않습니다.<pre><code class="language-sql">SELECT *
FROM data
WHERE event_params.key = &#039;page_location&#039;
 AND event_params.value.string_value = &#039;https://datarian.io/lecture&#039;</code></pre><code>UNNEST</code>를 사용해야 원하는 결과를 얻을 수 있습니다.<pre><code class="language-sql">SELECT *
FROM `datarian.analytics_234259439.events_20231204`
 CROSS JOIN UNNEST(event_params) AS ep
WHERE ep.key = &#039;page_location&#039;
 AND ep.value.string_value = &#039;https://datarian.io/lecture&#039;</code></pre><h1 id="e4ca6d8d1b844562ae5932f072e2c8e0">왜 이게 필요한거야?</h1>Google BigQuery는 왜 이렇게 특이한 문법을 요구하는 데이터를 만들었을까요.좀 더 힌트를 얻기 위해 이 데이터의 스키마를 살펴보겠습니다. event_params라는 컬럼의 데이터 유형은 ‘RECORD’입니다. Google BigQuery에서는 중첩 또는 반복이 있는 데이터 유형을 ‘RECORD’라고 지정할 수 있습니다. 모드에 ‘REPEATED’는 데이터의 반복이 있다는 것을 의미합니다. 여기에서는 여러개의 key, value 쌍이 있을 수 있다는 것을 의미합니다.<figure data-type="image-block" data-src="https://attachments.datarian.io/production-e98ad62c-6415-4768-b73d-246a58dcb17a-6DcTr1d8aQq01XJllH29xfPjYTxcw4t7AFkvLfemtQo.png" data-align="center" data-width="288" data-show-caption="true" style="width: 288px; max-width: 100%"><img src="https://attachments.datarian.io/production-e98ad62c-6415-4768-b73d-246a58dcb17a-6DcTr1d8aQq01XJllH29xfPjYTxcw4t7AFkvLfemtQo.png" width="288" /><figcaption>데이터리안 웹사이트 Google Analytics 로그 스키마</figcaption></figure>이 ‘RECORD’라는 데이터 유형이 왜 필요한지 더 잘 이해하기 위해서 일단 예시 데이터를 좀 더 자세히 볼 필요가 있습니다. 이 데이터는 데이터리안 웹사이트의 Google Analytics 로그입니다. Google Analytics는 사용자들의 웹사이트 내 페이지 뷰, 클릭 등 이벤트 데이터를 저장하고 분석하는 도구인데 <a href="https://datarian.io/blog/3ways-to-connect-data-to-google-bigquery" target="_blank">이 데이터를 Google BigQuery에 연결</a>하여 원본 데이터를 SQL로 조회하여 볼 수 있습니다. 아래와 같은 데이터들이 수집됩니다.<ul><li>event_date: 이벤트 발생 날짜</li><li>event_timestamp: 이벤트 발생 시각</li><li>event_name: 이벤트 이름 (예. page_view, 25% Scroll)</li><li>event_params: 이벤트 파라미터이벤트 파라미터는 이벤트 종류마다 다릅니다. page_view의 경우 아래와 같은 파라미터들을 수집합니다.<ul><li>page_title</li><li>page_location</li><li>batch_page_id</li><li>session_engaged</li><li>ga_session_id</li><li>ga_session_number</li><li>engaged_session_event</li><li>batch_ordering_id</li></ul></li><li>… 등</li></ul>이와 같이 하나의 이벤트에는 여러 이벤트 파라미터, 즉 추가적인 정보들이 붙습니다. 이벤트가 발생한 페이지의 제목이 뭔지, 페이지 주소는 어디인지, 어떤 세션에서 발생한 것인지 등 ‘페이지 뷰라는 이벤트가 발생했다’는 정보와 함께 저장해야 하는 것들을 이벤트 파라미터로 저장하는 것이죠. 그런데 이 이벤트 파라미터는 어떤 이벤트냐에 따라 종류가 달라집니다. 예를 들어, scroll이라는 이벤트에는 page_view와 달리 percent_scrolled라는 이벤트 파라미터가 붙고 페이지에서 몇 % 까지 스크롤을 했는지를 저장합니다. 그러니까 이벤트 파라미터 정보들을 모두 테이블 컬럼으로 만드는 것 보다 유연하게 저장할 방법이 필요합니다.아래처럼 모든 이벤트 파라미터를 컬럼으로 만드는 경우 사용하지 않는 컬럼이 많아집니다. 또한 새로운 파라미터를 추가하고 싶을 때 새로운 컬럼을 추가하는 등 테이블 구조 자체를 변경해야하기 때문에 비효율적입니다.<table><tbody><tr><th>event_date</th><th>event_timestamp</th><th>event_name</th><th>page_location</th><th>percent_scrolled</th></tr><tr><td>20231204</td><td>1701684701833010</td><td>page_view</td><td>https://datarian.io/lecture</td><td>page_view 이벤트일 경우에는 쓸모없는 컬럼</td></tr><tr><td>20231204</td><td>1701684701833010</td><td>scroll</td><td>https://datarian.io/lecture</td><td>90</td></tr></tbody></table>아래처럼 좀 더 유연한 데이터 구조인 ‘RECORD’가 각 이벤트마다 다른 파라미터를 설정하기에 더 적합합니다. <table><tbody><tr><th>event_date</th><th>event_timestamp</th><th>event_name</th><th>event_params</th></tr><tr><td>20231204</td><td>1701684701833010</td><td>page_view</td><td>[{page_location: ‘https://datarian.io/lecture’}]</td></tr><tr><td>20231204</td><td>1701684701833010</td><td>scroll</td><td>[{page_location: ‘https://datarian.io/lecture’}, {scroll: 90}]</td></tr></tbody></table>각주: 여기에서는 event_params 컬럼의 값을 이해하기 쉽도록 JSON 형식으로 표현했습니다. 또 실제로는 value의 데이터 타입이 여러가지이기 때문에 실제 Google Analytics 로그 데이터는 이보다 좀 더 복잡합니다. 여기에서는 ‘RECORD 데이터 유형이 있기 때문에 컬럼을 추가하는 것처럼 테이블 구조 변경을 하는 것보다 훨씬 더 유연하게 대처할 수 있겠구나’ 정도를 이해하시면 충분합니다.<h1 id="848a675a21ce427c9b8f617984764c2e">UNNEST 사용법</h1>이제 데이터를 저장하는 입장에서 이 구조가 필요한 것은 이해가 되셨나요? 제가 이 구조를 처음 봤을 때 가장 길게 한 고민이 ‘도대체 이걸 왜 이렇게 저장해놓은거지? 그냥 컬럼으로 저장해주면 안됐던건가?’였기 때문에 자세하게 얘기를 해보았어요.자 이제, 이렇게 생긴 데이터를 꺼내서 쓰려면 어떻게 해야할까요?<figure data-type="image-block" data-src="https://attachments.datarian.io/production-5087c002-ead3-44b4-9f8b-b1ee08c54735-Ssshjks5bN_J65EzHRrdSI5Rgb0V8QmBenwjeL2D3W4.png" data-align="center" data-width="768" data-show-caption="true" style="width: 768px; max-width: 100%"><img src="https://attachments.datarian.io/production-5087c002-ead3-44b4-9f8b-b1ee08c54735-Ssshjks5bN_J65EzHRrdSI5Rgb0V8QmBenwjeL2D3W4.png" width="768" /><figcaption>데이터리안 웹사이트 Google Analytics 로그 일부</figcaption></figure><h2 id="3099f9da76a34bb586fad434a03c6d31">UNNEST로 데이터 평면화하기</h2>일단 event_params 라는 컬럼에 있는 데이터들을 꺼내서 펼쳐주는 작업을 해야합니다. 이걸 평면화(Flatten)이라고 합니다. <code>CROSS JOIN</code> 과 <code>UNNEST()</code> 를 조합해서 위 데이터를 아래 이미지처럼 펼칠 수 있습니다.<pre><code class="language-sql">SELECT *
FROM `datarian.analytics_234259439.events_20231204`
 CROSS JOIN UNNEST(event_params) AS ep</code></pre><code>CROSS JOIN</code>은 명시를 안해줘도 동작하기 때문에 아래와 같이 쓸 수도 있습니다.<pre><code class="language-sql">SELECT *
FROM `datarian.analytics_234259439.events_20231204`, UNNEST(event_params) AS ep
WHERE ep.key = &#039;page_title&#039;
 AND ep.value.string_value = &#039;https://datarian.io/lecture&#039;</code></pre><figure data-type="image-block" data-src="https://attachments.datarian.io/production-95103b8e-b785-4af0-bcd5-f17bf1f74f53-mPeGPZDcrpeKUTGU48IGI9zW-CYMY8pRBbv9V0t1Vn8.png" data-align="center" data-width="100%" data-show-caption="true" style="width: 100%; max-width: 100%"><img src="https://attachments.datarian.io/production-95103b8e-b785-4af0-bcd5-f17bf1f74f53-mPeGPZDcrpeKUTGU48IGI9zW-CYMY8pRBbv9V0t1Vn8.png" /><figcaption>‘RECORD’ 유형인 event_params 컬럼을 평면화하는 쿼리 결과</figcaption></figure>데이터 평면화를 이미지로 잘 보여주는 자료가 있어 아래에 첨부합니다.<figure data-type="image-block" data-src="https://attachments.datarian.io/production-1e894d56-48d4-4147-9a19-d2e1ede15b62-txFqBJw3cBLcVprTVZO4T4dh-cyOEAmDvSQL9ZNcL_c.gif" data-align="center" data-width="552" data-height="338" data-show-caption="true" style="width: 552px; max-width: 100%"><img src="https://attachments.datarian.io/production-1e894d56-48d4-4147-9a19-d2e1ede15b62-txFqBJw3cBLcVprTVZO4T4dh-cyOEAmDvSQL9ZNcL_c.gif" width="552" height="338" /><figcaption>이미지 출처: <a href="https://medium.com/firebase-developers/using-the-unnest-function-in-bigquery-to-analyze-event-parameters-in-analytics-fb828f890b42" target="_blank">How to use the UNNEST function in BigQuery to analyze event parameters in Analytics</a></figcaption></figure>이제 드디어 한 행에 여러 데이터가 들어가있는 이상한 구조의 데이터가 아니라 한 행에는 데이터가 1개 들어가있는 우리 눈에 익숙한 데이터가 되었습니다! <h2 id="33b70fd2dec74a90816df641a2b0e41c">평면화 한 데이터 필터링하기</h2>이렇게 평면화 한 데이터를 이용해 WHERE 절에서 데이터를 필터링 할 수도 있습니다. 아래 쿼리는 특정 페이지에서 발생한 이벤트 목록을 필터링하는 쿼리입니다.<pre><code class="language-sql">SELECT *
FROM `datarian.analytics_234259439.events_20231204`
 CROSS JOIN UNNEST(event_params) AS ep
WHERE ep.key = &#039;page_title&#039;
 AND ep.value.string_value = &#039;https://datarian.io/lecture&#039;</code></pre><h2 id="26d658d689314922b3b6f05c80e88edb">피봇테이블을 응용하여 좀 더 보기 쉬운 데이터 형식으로 가공하기</h2>조금 더 익숙한 형태는 page_title 등 이벤트 파라미터들이 컬럼으로 구성되어있는 형태이겠지요. 그런 모양은 테이블 피봇을 응용한 쿼리를 이용해 만들 수 있습니다. <a href="https://datarian.io/bootcamp/sql-advanced" target="_blank">SQL 데이터 분석 캠프 | 실전반</a>에서 연습용 GA 데이터를 가지고 퍼널 분석과 세션 정의하기를 배우는데요. <a href="https://solvesql.com/playground/" target="_blank">solvesql의 플레이그라운드</a>에 업로드 되어있는 이 연습용 GA 데이터들은 다 이런 가공 과정을 통해 만들어졌습니다.<pre><code class="language-sql">SELECT event_date
 , event_timestamp
 , event_name
 , MAX(CASE WHEN ep.key = &#039;page_title&#039; 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</code></pre><figure data-type="image-block" data-src="https://attachments.datarian.io/production-e33e0fd1-1517-4da0-96ad-afe2a9e5fb5d-pcHmupbFX0tCCYqkwzxCcbDELE__kkxTbyK3_S1jnhI.png" data-align="center" data-width="100%" style="width: 100%; max-width: 100%"><img src="https://attachments.datarian.io/production-e33e0fd1-1517-4da0-96ad-afe2a9e5fb5d-pcHmupbFX0tCCYqkwzxCcbDELE__kkxTbyK3_S1jnhI.png" /></figure><h1 id="422de38c9b0a4f94878098a5ea629392">마무리</h1>Google BigQuery의 가장 큰 특징으로 여겨지는 RECORD에 대해서 얘기해봤습니다. 막연하게 복잡하다고 생각하고 계셨다면 이 글이 막연함을 해소해주는 역할을 했길 바랍니다. 좀 더 자세한 설명은 참고 자료에 넣어둔 Google Cloud 공식 문서에 있으니 관심이 있으시다면 꼭 읽어보길 바랍니다. 심화 학습을 원하시는 분들은 <code>ARRAY</code>, <code>STRUCT</code>에 대해서 공부하시는게 좋습니다. Google Cloud의 <a href="https://cloud.google.com/bigquery/docs/arrays?hl=ko" target="_blank">배열 작업</a>이라는 문서가 매우 잘 쓰여져있으니 참고해주세요.<h1 id="61cf73f50c834f9685f612d6c08caa2f">참고 자료</h1><ul><li>Google Cloud 공식 문서: <a href="https://cloud.google.com/bigquery/docs/nested-repeated?hl=ko#sql" target="_blank">테이블 스키마에 중첩 및 반복 열 지정</a></li><li>Google Cloud 공식 문서: <a href="https://cloud.google.com/bigquery/docs/arrays?hl=ko" target="_blank">배열 작업</a></li><li><a href="https://medium.com/firebase-developers/using-the-unnest-function-in-bigquery-to-analyze-event-parameters-in-analytics-fb828f890b42" target="_blank">How to use the UNNEST function in BigQuery to analyze event parameters in Analytics</a></li></ul>

상황에 적합한 방법을 찾아 차근차근 따라 해보시면 아주 쉽게 빅쿼리에서 데이터를 볼 수 있어요.

빅쿼리(Google BigQuery)에 데이터를 연결하는 3가지 방법

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

빅쿼리(Google BigQuery) 필수 문법, UNNEST 사용법

Contents

데이터 분석 소식을
매월 받아보세요

Google BigQuery 모아보기

함께 읽어보면 좋은 글

윈도우에 MySQL 설치하기

감이 아닌 데이터로, 사회 문제를 더 정확하게 바라보아요

예체능 출신의 비전공자, 마케터에서 데이터 분석가로 직무 전환했어요

데이터 분석 소식을
매월 받아보세요데이터 분석 소식을 받아보세요