Mindful Analytics - R에서 구글빅쿼리 데이터 추출하기

언제 읽으면 좋을까?

구글빅쿼리 데이터를 RStudio에서 받아오고 싶을 때
구글빅쿼리와 RStudio 연결 방법을 모를 때
bigrquery 패키지 사용법을 알고 싶을 때

1. 구글 빅쿼리(BigQuery)란?

구글 빅쿼리는 대용량 데이터를 효율적으로 처리하고 분석할 수 있도록 구글에서 제공하는 온라인 데이터베이스 서비스 입니다. 특히 온라인 고객 행동을 모니터링하기 위해 사용하는 GA4(구글애널리틱스4) 데이터를 빅쿼리로 바로 연동하도록 설계되어 있습니다. 이 때문에 GA4 데이터 분석에서는 빅쿼리 사용 점점 늘어나고 있기도 합니다.

빅쿼리는 많은 양의 데이터를 온라인에서 분석할 수 있도록 쥬피터노트북과 연계되어 있기 때문에 데이터를 다운로드 하는 것 보다는 온라인에서 직접 분석하는 게 더 효율적일 수 있습니다. 그럼에도 불구하고 데이터를 다운로드 받아서 로컬에서 분석해야 하는 경우, bigrquery 패키지를 활용하여 데이터를 다운로드 할 수도 있습니다.

2. 빅쿼리 시작하기

빅쿼리 설정 먼저

구글 빅쿼리에 계정과 데이터를 추가한 뒤에 R에서 연결합니다.
* 계정 만들기
* 계정에 공개 데이터셋 추가하기

(1) 빅쿼리 계정 만들기

빅쿼리 홈페이지를 방문하여 본인의 구글 계정에서 빅쿼리를 사용할 수 있도록 가입합니다. 빅쿼리 시작 시에는 무료이며, 300$의 무료 크레딧이 제공됩니다. 데이터를 불러올 때 용량에 따라 크레딧이 차감되고, 크레딧을 소진하는 경우 추가 비용이 발생할 수 있습니다.

빅쿼리 계정을 만들면 콘솔(대시보드)가 생성됩니다.

(2) 빅쿼리 계정에 공개 데이터셋 추가하기

bigrquery 설치 후 예제를 수행하기 위해서는 본인의 구글 계정에 테스트용 공개 데이터셋를 먼저 추가해야 합니다. 추가하는 방법은 다음과 같습니다.

빅쿼리 콘솔 > 탐색기(Explorer) > +추가 버튼 클릭
새 창에서 “public data”로 검색 > Public datasets 선택
필터에서 분석, 데이터, 무료 선택 > ‘Google’s diversity annual report data’ 선택: 이 때 무엇을 선택해도 상관없이 나의 데이터 목록에 bigquery-public-data 프로젝트가 추가 됩니다.
bigquery-public-data 프로젝트를 클릭하면 수 많은 데이터 목록이 보이는데, 그 중에 “samples”라는 데이터셋을 테스트 하겠습니다.

(3) 빅쿼리 데이터 구조

빅쿼리는 프로젝트 > 데이터셋 > 테이블 3단 구조로 설계되어 있습니다. 분석용 데이터는 테이블에 저장되어 있습니다. 우리에게 친숙한 엑셀과 비교하자면, 데이터셋은 파일, 테이블은 시트와 유사한 구조입니다.

빅쿼리 접속할 경우에 이 3가지 정보를 정확히 입력해야 원하는 데이터를 받을 수 있습니다.

3. bigrquery 패키지 활용법

bigrquery 패키지는 가벼운 API 연동으로 빅쿼리에 저장된 데이터 정보(메타데이터)를 빠르게 받아올 수 있습니다.

(1) 패키지 설치하기

install.packages("bigrquery")
library(bigrquery)

(2) 계정 인증

다음 명령을 실행하면 웹 브라우저가 열리면서 구글 계정에 로그인하도록 안내가 됩니다. 계정 연결은 최초 1회만 필요합니다.

bigrquery::bq_auth()

(3) 공개 데이터셋 정보 확인

공개 데이터셋 정보는 다음과 같습니다.

프로젝트: “bigquery-public-data”
데이터셋: “samples”

데이터셋 안에 어떤 테이블이 존재하는지 모를 경우, bq_dataset("프로젝트명", "데이터셋명") 명령을 실행하여 확인 합니다.

ds_sample <- bigrquery::bq_dataset("bigquery-public-data", "samples")
print(ds_sample)

<bq_dataset> bigquery-public-data.samples

데이터에 관한 정보, 즉 메타데이터를 추출하기 때문에 “프로젝트명.데이터셋명”으로 정의된 데이터가 있다고 확인 메세지를 출력합니다.

데이터셋에 어떤 테이블이 들어 있는지 확인하려면, bq_dataset_tables("프로젝트명", "데이터셋명") 명령을 실행 합니다. 여기서는 해당 정보를 ds_samples_tables 변수에 저장합니다.

ds_samples_tables에서 메타데이터 정보를 추출하면 총 7개의 테이블이 있는 것을 알 수 있습니다. 구글 빅쿼리 홈페이지에서 확인했던 7개 테이블과 동일한 정보 입니다.

테이블 이름은 “프로젝트.데이터셋.테이블”으로 표시됩니다.

ds_samples_tables <- bigrquery::bq_dataset_tables(ds_sample) 
print(ds_samples_tables)

[[1]]
<bq_table> bigquery-public-data.samples.github_nested

[[2]]
<bq_table> bigquery-public-data.samples.github_timeline

[[3]]
<bq_table> bigquery-public-data.samples.gsod

[[4]]
<bq_table> bigquery-public-data.samples.natality

[[5]]
<bq_table> bigquery-public-data.samples.shakespeare

[[6]]
<bq_table> bigquery-public-data.samples.trigrams

[[7]]
<bq_table> bigquery-public-data.samples.wikipedia

(4) 분석 데이터 선택

7개 테이블 중 natality 테이블을 선택합니다. bq_table(프로젝트, 데이터셋, 테이블) 명령을 실행하면 해당 테이블에 대한 메타 정보를 출력합니다.

natality <- bigrquery::bq_table("bigquery-public-data", "samples", "natality")
print(natality)

<bq_table> bigquery-public-data.samples.natality

(5) 빅쿼리 비용 주의사항

메타데이터는 실제 해당 테이블이 가지고 있는 데이터를 포함하지 않고 단순히 어떤 프로젝트, 데이터셋, 테이블 인지를 정의한 정보 입니다. 데이터를 출력하려면 bq_table_download()를 사용합니다. 이 때 서버에 얼마나 많은 양의 데이터가 있는지 알 수 없기 때문에 모든 데이터를 출력하지 않아야 합니다. 자칫 실수로 전체 데이터를 받을 경우 과도한 비용과 서버 과부하가 발생합니다.

또한 빅쿼리는 데이터를 “열(column)”을 기준으로 저장합니다. 따라서 열을 조회하는 순간 행(row) 갯수와 관계없이 비용이 부과됩니다.

빅쿼리는 데이터 저장 비용은 매우 저렴하지만, 데이터를 조회(SELECT)하거나 추출할 때 비용이 발생하기 때문에 sql 쿼리 설계를 잘 해야 운영 비용이 적게 발생합니다.

따라서 다음과 같이 테이블이 가진 첫 6개 행을 출력하는 것은 비용 절감에 도움이 되지 않습니다.

첫 6개행 출력하기

natality_df <- bigrquery::bq_table_download(natality, n_max = 6)
print(natality_df)

차라리 출력하는 열 갯수를 줄이는 것이 비용을 많이 절약할 수 있습니다. R에서는 실제 sql 문구를 적용하기 전에는 데이터 양이 얼마나 될 지 미리 알 수가 없습니다. 따라서 빅쿼리 초보 사용자라면 구글 빅쿼리 콘솔에서 실제로 SQL 구문을 수정해 보면서 데이터 양을 비교해 보는 것을 추천합니다.

natality 테이블 모든 열을 조회하는 경우: 데이터 용량 21.94GB

natality 테이블 중 3개 열에 한정하여 조회하는 경우: 데이터 용량 2.11GB (1/10 정도로 줄었습니다)

natality 테이블 중 3개 열의 행 추출 수를 제한하지 않아도(LIMIT 1000 삭제) 데이터 용량은 2.11 GB로 동일합니다.

오늘은 R에서 빅쿼리 데이터에 접근해서 데이터를 다운로드하는 기초적인 방법을 알아 봤습니다. 다음 글에서는 필요한 열(column) 정보와 행(row) 정보를 선택할 수 있도록 SQL 코드를 R dplyr 패키지와 유사하게 구현할 수 있는 dbplyr 패키지를 활용해서 효율적인 빅쿼리 데이터 다운로드 방법에 관해 소개하겠습니다.

빅쿼리에 저장된 GA4 데이터를 가장 효율적인 분석 언어 R로 추출해 보세요.