Mindful Analytics - dbplyr로 bigquery 데이터 추출하기

누가, 언제 읽으면 좋을까?

tidyverse 문법에 익숙한 R 사용자가
SQL 구문을 사용하지 않고 빅쿼리 데이터를 추출하고 싶을 때

이 글에서 다루지 않는 내용

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

1. dbplyr 소개

dbplyr은 tidyverse 패키지 중 하나로, 데이터베이스에서 데이터를 추출하는 패키지입니다. dbplyr은 R사용자에게 익숙한 dplyr 문법으로 사용하여 *SQL을 사용하지 않고도 데이터베이스에서 데이터를 추출할 수 있게 해 줍니다.

dbplyr 패키지에서 가장 많이 활용되는 함수는 tbl() 함수로, 데이터베이스에서 테이블을 불러올 때 사용합니다. tbl() 함수를 사용하면 데이터베이스에서 테이블을 불러올 때 사용하는 SQL문을 사용하지 않고도 데이터를 불러올 수 있습니다.

(1) 사용 패키지 설치

# dbplyr을 포함한 tidyverse 패키지를 설치하고 실행합니다. 
install.packages("tidyverse")
library(tidyverse)

# 구글빅쿼리 접속을 위한 bigrquery 패키지를 설치하고 실행합니다.
install.packages("bigrquery")
library(bigrquery)

(2) 빅쿼리 계정 연결하기

구글 빅쿼리에 접속하여 이 글에서 사용하는 공개 데이터셋을 활용하려면 빅쿼리 계정이 필요합니다. 빅쿼리 계정 설정 방법은 ‘R에서 구글빅쿼리 데이터 추출하기’를 참고하세요. 계정 설정이 끝났다면 아래 코드를 실행하여 빅쿼리 접속 권한을 설정합니다.

# 계정 연결하기 
bigrquery::bq_auth()

(3) 데이터베이스 연결하기

bigrquery 패키지를 사용하여 구글 빅쿼리에 연결합니다. 연결을 위해서는 dbConnect() 함수를 사용합니다.

빅쿼리 계정이 준비되었다면, dbConnect() 함수를 사용하여 데이터베이스에 연결합니다.

# 데이터베이스 연결하기 
con <- DBI::dbConnect(
  bigrquery::bigquery(),
  project = "bigquery-public-data",
  dataset = "samples",
  billing = "여기에 본인 계정 프로젝트 이름을 넣습니다"
)

데이터베이스 연결이 성공하면 con 객체에 데이터베이스 연결 정보가 저장됩니다. 연결이 되었다면, 다음 이미지처럼 RStudio > Connections 탭에서 연결된 데이터베이스가 갖고 있는 테이블 정보를 확인할 수 있습니다.

이 글에서는 shakespeare 테이블을 사용하겠습니다.

(4) 테이블 불러오기

tbl() 함수를 사용하여 데이터베이스에서 테이블을 불러올 수 있습니다. shakespeare 테이블을 불러 오겠습니다. 이 때 테이블 데이터가 실제 다운로드 되지 않고, 데이터베이스에 있는 테이블 메타 정보만을 불러옵니다. 이렇게 tbl() 함수를 사용하여 데이터 정보를 보는 것은 데이터 다운로드 비용 발생 없이 테이블 내용을 살펴보기 용이한 빅쿼리 활용 방법입니다.

# 테이블 불러오기    
db_table <- tbl(con, "shakespeare")
class(db_table)

[1] "tbl_BigQueryConnection" "tbl_dbi"                "tbl_sql"               
[4] "tbl_lazy"               "tbl"

실행된 결과를 보면 db_table 객체는 tbl_lazy 클래스로 정의되어 있습니다. 이는 데이터가 실제로 다운로드 되지 않았다는 것을 의미합니다. 이 상태에서 테이블 열 정보를 확인할 수 있습니다.

이제 샘플 데이터가 준비되었으니 dbplyr 패키지와 dplyr 문법을 사용하여 데이터를 추출해 보겠습니다.

2. dbplyr로 bigquery 데이터 추출하기

shakespeare 데이터 정보가 저장된 db_table 객체를 사용하여 dbplyr 패키지를 활용하여 데이터를 추출해 보겠습니다.

(1) dplyr 문법으로 테이블 요약하기

# 테이블 열 정보 확인하기
db_table %>% glimpse()

Rows: ??
Columns: 4
Database: BigQueryConnection
$ word        <chr> "LVII", "augurs", "dimm'd", "plagues", "treason", "surmise…
$ word_count  <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
$ corpus      <chr> "sonnets", "sonnets", "sonnets", "sonnets", "sonnets", "so…
$ corpus_date <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…

실행된 결과를 보면 db_table 테이블에는 4개의 열이 있습니다. 아직 전체 데이터 다운로드가 실행되지 않았기 대문에 행 갯수는 ??으로 표시됩니다.

(2) dplyr 문법으로 테이블 조작 쿼리 작성하기

dplyr 패키지를 사용해서 tibble 형태 데이터를 조작하듯 쿼리를 작성할 수 있습니다. 쿼리를 작성하는 것은 실제 데이터를 추출하는 것이 아닙니다.

# shakespeare 테이블에서 작품별로 사용된 단어 수를 계산합니다.
# 단어 수가 가장 많은 상위 10개 단어를 추출합니다. 
word_count_query <- db_table %>% 
  group_by(word) %>% 
  summarise(count = n()) %>% 
  arrange(desc(count)) %>% 
  head(10)

dbplyr로 작성한 쿼리를 SQL로 확인하고 싶다면 show_query() 함수를 사용합니다.

# SQL 쿼리 확인하기
show_query(word_count_query)

<SQL>
SELECT `word`, count(*) AS `count`
FROM `shakespeare`
GROUP BY `word`
ORDER BY `count` DESC
LIMIT 10

이렇게 SQL 문법을 몰라도 dplyr 문법을 사용하여 SQL 쿼리를 작성할 수 있음을 확인할 수 있습니다.

(3) 데이터 추출하기

collect() 함수를 사용하여 실제 데이터를 추출합니다. collect() 함수를 실행하는 것으로 실제 비용이 발생하게 됩니다. 이 쿼리를 실행하는 경우 10.49MB 데이터를 다운로드 하게 됩니다. 빅쿼리에서는 월 1TB를 무료로 제공하고 있어, 이 글에서 진행하는 실습으로는 추가 비용을 부담하지 않습니다.

# 데이터 추출하기
word_count <- collect(word_count_query)

(4) 데이터 확인하기

셰익스피어 테이블에서 가장 많이 사용된 단어 10개를 추출한 결과를 확인합니다.

# 데이터 확인하기
print(word_count)

# A tibble: 10 × 2
   word     count
   <chr>    <int>
 1 She         42
 2 comes       42
 3 thousand    42
 4 fall        42
 5 late        42
 6 hard        42
 7 us          42
 8 himself     42
 9 There       42
10 last        42

(5) 데이터베이스 연결 끊기

데이터 추출이 완료 되었다면 다음 명령을 실행하여 빅쿼리 데이터베이스와 연결을 끊습니다.

# 데이터베이스 연결 끊기
DBI::dbDisconnect(con)

3. 마무리

이렇게 데이터베이스에 대한 지식이 부족한 R 사용자에게 유용한 패키지 dbplyr를 소개해 드렸습니다. dbplyr 패키지를 사용하여 SQL을 사용하지 않고도 빅쿼리 데이터를 추출할 수 있습니다. 빅쿼리에 저장된 데이터가 너무 많아서 간단하게 데이터 형태를 살펴보고 요약해 보고 싶다면 전체 데이터를 다운로드하지 않아도 되기 때문에 시간과 비용이 크게 절감되니 꼭 활용해 보시기 바랍니다.