library(DBI)
library(dbplyr)
library(dplyr)
library(RSQLite)
library(odbc)tidyverse로 데이터베이스랑 대화하기 - 1편
SQL
R
R을 통해 데이터베이스 관련 작업을 한다면, 꼭 SQL 전문가가 될 필요는 없어요!
실무에서는 Data analyst, Data scientist를 가리지 않고 SQL에 관한 능력을 요구합니다. 우리나라의 채용공고를 둘러보면 Data analyst의 경우 특히 SQL 스킬을 중요하게 요구하는 듯 합니다. 방대한 양의 데이터를 저장하고 관리하기 위해 실무에서는 데이터베이스를 사용합니다. 데이터베이스는 종종 관계형 데이터베이스 시스템1(이하 RDBMS)이라 불리기도 하죠. 그리고, 우리는 SQL2 언어 또는 SQL을 조금 변형한(variant) 언어를 통해 이 데이터베이스에 질의(query)를 합니다. 여기서 변형이라는 말을 사용한 이유는, RDBMS를 제공하는 업체에서 표준화된 SQL을 제공하는 경우도 있지만, 표준화된 SQL을 조금 변형시켜 사용하는 경우도 있기 때문입니다.
만약 이렇게 특정 업체로부터 제공되는 변형된 RDBMS를 사용해야한다면, 해당 업체에서 사용하는 특정 SQL dialect3를 사용해 쿼리를 작성하는 방법을 이해해야 하실겁니다. 변형된 RDBMS를 예로 들어보자면, PostgreSQL, PrestoDB(AWS의 Athena를 위한) 등이 있습니다. PostgreSQL DB의 JSON 필드는 AWS에서 구조화된 중첩 배열로(array) 수집되므로, 동일한 필드를 쿼리하고자 할 때 다른 쿼리문을 사용합니다.
R을 사용하는 여러분 모두 잘 아시다시피, R에서는 {dplyr}4 패키지를 통해 이러한 작업을 데이터에 수행할 수 있습니다. {dplyr}이 select(), group_by(), left_join() 등 SQL 문법을 잘 모방하긴 했지만, SQL 문법과 R 문법 사이를 완벽하게 왔다갔다 하기는 어렵습니다. 예를 들자면, {dplyr}의 filter()를 이용해 특정 행을 뽑아올 때, 우리는 R 문법을 따라야하므로 조건문에 =이 아닌 ==을 사용하죠. 이는 SQL 문법과는 완벽히 다른 부분입니다.
자, 여기서 이러한 상황을 타개할 방법은 무엇일까요. 엄청난 용량의 데이터베이스를 R로 가져올 수는 없습니다. 메모리 베이스인 R에 이 짓을 햇다가는요? 생각도 하기 싫습니다.😰 그럼, RDBMS 환경에서 이러한 무거운 작업을(e.g. computation) 수행하고 필요로 될 때에만 R에다가 가져오면 되지 않을까요? 예를 들면, 집계된 데이터를 가져와서 보고서용 그림을 그린다든지. 이를 가능하게끔 해주는 패키지에 대해 배워보려고 합니다.
본 튜토리얼에서는 {dplyr}의 데이터베이스 백엔드 버전이라 할 수 있는 {dbplyr} 패키지에 대해 배울거에요. {dbplyr}은 당신의 RDBMS에 R의 tidyverse 문법을 사용한 쿼리문을 직접적으로 사용할 수 있게끔 해줄겁니다.😀
1 DB 연결하기
먼저 필요한 패키지를 불러오죠. install.packages("패키지명")을 통해 설치할 수 있습니다.
{DBI}: R의 데이터베이스 인터페이스에 관한 메인 패키지입니다.{dbplyr}:{dplyr}문법을 사용하여 데이터베이스에 질의를 할 수 있게끔 해줍니다.{dplyr}: 데이터베이스에 질의할 때 사용할 패키지입니다.{RSQLite}: 가벼운 단일 유저용 데이터베이스 SQLite DB에 연결할 수 있게끔 해주는 DBI5 호환 패키지입니다. R-SQLite로 이해하시면 편합니다.- 다른 DBI 용 호환 패키지가 필요할 수도 있습니다. 예를 들어,
{RPostgres}는 PostgreSQL RDBMS와 연결을 해주는 패키지입니다.6 {odbc}: odbc 드라이버를 사용해 RDBMS 인터페이스에 인터페이스할 수 있도록 해주는 DBI 호환 인터페이스입니다.7
예제용 토이 DB
Alison Hill이 The Great British Bake off에서 만든 데이터를 사용하려고 합니다. 본 예제에서 다룰 데이터베이스는 여기서 내려받으세요. {bakeoff} 패키지의 데이터를 이용해 연습에 사용할 SQLite DB를 만들었습니다. 이 튜토리얼의 원 저자 Vebash Naidoo님께 감사의 말을 전합니다.
SQLite DB 연결하기
이제 DB를 SQLite DB에 연결해봅시다. DB와 대화를 나누기 위해서, 우선 연결(connection)을 해줘야합니다. 다음의 작업을 해줄겁니다.
DBI 패키지 로딩:
library(DBI)연결하기:
con <- dbConnect(RSQLite::SQLite(), "내려받은 db 경로")
library(DBI) # main DB interface
library(dplyr)
library(dbplyr) # dplyr back-end for DBs
con <- dbConnect(drv = RSQLite::SQLite(), # give me a SQLite connection
dbname = "data/great_brit_bakeoff.db")
summary(con) # What do we have? Length Class Mode
1 SQLiteConnection S4 위와 같은 명령어가 출력되면 DB에 성공적으로 연결된 것입니다.
2 DB 둘러보고 다뤄보기
자, DB 연결도 했으니 이제 몇 가지 DBI 함수를 이용해 연결한 DB를 둘러보고 다뤄봅시다.
DBI 함수
DBI 함수들의 이름은 꽤 직관적입니다.
dbListTables(con) # 연결된 테이블 리스트를 보여줘! [1] "baker_results" "bakers" "bakes"
[4] "challenge_results" "challenges" "episode_results"
[7] "episodes" "ratings" "ratings_seasons"
[10] "results" "seasons" "series" dbListFields(con, # 연결한 DB로 가서
"bakers") # bakes 테이블에 어떤 필드가 있는지 알려줘![1] "series" "baker_full" "age" "occupation" "hometown" DB에 질의는 다음과 같이 수행할 수 있어요.
res <- dbSendQuery(con, "SELECT * FROM bakers LIMIT 3") # 쿼리문 실행
# bakers 테이블에 모든 필드를 가져오는데, 관측치 3개까지만 가져와봐!
dbFetch(res) # 결과 출력해줘 series baker_full age occupation
1 1 Annetha Mills 30 Midwife
2 1 David Chambers 31 Entrepreneur
3 1 Edward "Edd" Kimber 24 Debt collector for Yorkshire Bank
hometown
1 Essex
2 Milton Keynes
3 BradforddbClearResult(res) # 결과 지우기dplyr 함수
이제, 우리가 잘하는 {dplyr}의 함수들을 이용해 마음껏 DB와 이야기해보죠.
dplyr::tbl(con, "테이블명"): 연결한 DB(con)으로 가서SELECT * FROM 테이블명실행해줘.
tbl(con, "bakers")# Source: table<bakers> [?? x 5]
# Database: sqlite 3.39.1 [/Volumes/Essential/Study/Private/Writing/Blog/posts/2022-04-07-talk-with-database-using-tidyverse-part-i/data/great_brit_bakeoff.db]
series baker_full age occupation homet…¹
<dbl> <chr> <dbl> <chr> <chr>
1 1 "Annetha Mills" 30 Midwife Essex
2 1 "David Chambers" 31 Entrepreneur Milton…
3 1 "Edward \"Edd\" Kimber" 24 Debt collector for Yorkshire Ba… Bradfo…
4 1 "Jasminder Randhawa" 45 Assistant Credit Control Manager Birmin…
5 1 "Jonathan Shepherd" 25 Research Analyst St Alb…
6 1 "Lea Harris" 51 Retired Midlot…
7 1 "Louise Brimelow" 44 Police Officer Manche…
8 1 "Mark Whithers" 48 Bus Driver South …
9 1 "Miranda Gore Browne" 37 Food buyer for Marks & Spencer Midhur…
10 1 "Ruth Clemens" 31 Retail manager/Housewife Poynto…
# … with more rows, and abbreviated variable name ¹hometown
# ℹ Use `print(n = ...)` to see more rowstbl(con, "bakers") %>%
head(3) # "SELECT * FROM bakers LIMIT 3"와 동일# Source: SQL [3 x 5]
# Database: sqlite 3.39.1 [/Volumes/Essential/Study/Private/Writing/Blog/posts/2022-04-07-talk-with-database-using-tidyverse-part-i/data/great_brit_bakeoff.db]
series baker_full age occupation homet…¹
<dbl> <chr> <dbl> <chr> <chr>
1 1 "Annetha Mills" 30 Midwife Essex
2 1 "David Chambers" 31 Entrepreneur Milton…
3 1 "Edward \"Edd\" Kimber" 24 Debt collector for Yorkshire Bank Bradfo…
# … with abbreviated variable name ¹hometown데이터베이스와 대화를 나눌 때 마다 초기에 연결해둔 con을 사용한다는 점을 유념해주세요. 초기에 불러왔던 con은 아까처럼 일반적인 SQL 쿼리문을 이용해 질의를 할 때 뿐만이 아닌 {dplyr}을 통해 타이디한 파이프라인으로 원하는 테이블을 가져올 때도 사용됩니다.
자 이제 예시 상황을 하나 들어서 {dplyr}로 원하는 테이블을 가져와보겠습니다. baker_results 테이블에는 각 제빵 대회에 참가한 제빵사(baker)의 세부 정보 필드가 담겨있습니다:
dbListFields(con, "baker_results") [1] "series" "baker_full"
[3] "baker" "age"
[5] "occupation" "hometown"
[7] "baker_last" "baker_first"
[9] "star_baker" "technical_winner"
[11] "technical_top3" "technical_bottom"
[13] "technical_highest" "technical_lowest"
[15] "technical_median" "series_winner"
[17] "series_runner_up" "total_episodes_appeared"
[19] "first_date_appeared" "last_date_appeared"
[21] "first_date_us" "last_date_us"
[23] "percent_episodes_appeared" "percent_technical_top3" 각 제빵대회 우승자의 출신이 영국의 일부 지역에서 나왔는지, 아니면 다양한 지역으로부터 우상자가 배출되었는지 알고싶은 상황이라고 해봅시다. 우선 다음과 같이 관심있는 필드만 불러와주겠습니다.
tbl(con, "baker_results") %>%
select(series, baker, hometown, series_winner)# Source: SQL [?? x 4]
# Database: sqlite 3.39.1 [/Volumes/Essential/Study/Private/Writing/Blog/posts/2022-04-07-talk-with-database-using-tidyverse-part-i/data/great_brit_bakeoff.db]
series baker hometown series_winner
<dbl> <chr> <chr> <int>
1 1 Annetha Essex 0
2 1 David Milton Keynes 0
3 1 Edd Bradford 1
4 1 Jasminder Birmingham 0
5 1 Jonathan St Albans 0
6 1 Lea Midlothian, Scotland 0
7 1 Louise Manchester 0
8 1 Mark South Wales 0
9 1 Miranda Midhurst, West Sussex 0
10 1 Ruth Poynton, Cheshire 0
# … with more rows
# ℹ Use `print(n = ...)` to see more rows24개 열 중 관심있는 4개 열만 불러왔습니다. 이제 제빵대회에 우승한 사람만 골라낸 뒤(filter()) 우승자들이 영국의 어떤 지역으로 부터 왔는지 지역별로 인원을 구하고(count()) 내림차순 정렬(sort())을 해보죠.
tbl(con, "baker_results") %>%
select(series, baker, hometown, series_winner) %>%
filter(series_winner == 1) %>%
count(hometown, sort = TRUE)# Source: SQL [8 x 2]
# Database: sqlite 3.39.1 [/Volumes/Essential/Study/Private/Writing/Blog/posts/2022-04-07-talk-with-database-using-tidyverse-part-i/data/great_brit_bakeoff.db]
# Ordered by: desc(n)
hometown n
<chr> <int>
1 Wigan 1
2 West Molesey, Surrey 1
3 Ongar, Essex 1
4 Market Harborough, Leicestershire 1
5 Leeds / Luton 1
6 Bradford 1
7 Barton-Upon-Humber, Lincolnshire 1
8 Barton-Le-Clay, Bedfordshire 1이 결과에 따르면, 제빵대회 우승자들의 출신 지역은 각기 다르다고 결론을 내릴 수 있겠네요.
dplyr 문법을 SQL 쿼리문으로
앞서 {dplyr}을 이용해 수행한 질의를 SQL 쿼리문으로는 어떻게 작성할까요? 코드 한 줄이면 손쉽게 알 수 있습니다.😀
tbl(con, "baker_results") %>%
select(series, baker, hometown, series_winner) %>%
filter(series_winner == 1) %>%
count(hometown, sort = TRUE) %>%
show_query()<SQL>
SELECT `hometown`, COUNT(*) AS `n`
FROM (
SELECT `series`, `baker`, `hometown`, `series_winner`
FROM `baker_results`
)
WHERE (`series_winner` = 1.0)
GROUP BY `hometown`
ORDER BY `n` DESC멋지지 않습니까? 이제 제가 왜 이 글의 맨 위 요약을 “R을 통해 데이터베이스 관련 작업을 한다면, 꼭 SQL 전문가가 될 필요는 없어요!”라고 적은 지 아시겠나요? {dplyr}로 작업을 수행하고, SQL 쿼리문으로 변환을 수행해보는 작업은 SQL을 배우는 과정에 꽤 큰 도움이 될겁니다. 직장 또는 기관에서 DB를 관리할 때 모두 같은 업체의 SQL DB를 사용하는 건 아니므로, 이렇게 광범위한 업체들로부터 공급되는 SQL을 알고, 읽는 것은 언제나 중요하기 때문입니다.
출력문의 lazy query / ??의 의미
앞서 테이블, 쿼리를 작성하며 출력물에서 Source: table [?? x 5] 또는 Source: lazy query [?? x 4]와 같은 문장을 확인하실 수 있었을 겁니다.
이런 문장이 출력물에 포함되는 이유
먼저, 우리가 직접적인 RDBMS 상에서가 아닌 R이라는 공간을 빌려 뒤에서(behind the scenes) 작성한
dplyr코드는 우리가 연결하려는 DB의 SQL에 해당하는 dialect로 변환됩니다.즉, SQL은 DB에 직접적으로 실행됩니다. 즉, 데이터를 먼저 R로 가져와서 조작하는 것이 아닌 쿼리 자체를 DB에 보내고 DB에서 계산(computation)이 수행됩니다.
정리하면,
dplyr파이프라인을 사용해 DB에서 쿼리를 실행하면, DB에서 계산을 수행하고 실행된 최종 결과의 전체가 아닌 일부를 R에서 보여주는 식입니다.이러한 이유들을 들여다보면 우리는
??를 이해할 수 있습니다.??는 “연결 DBcon에서 이 쿼리(파이프라인을 SQL로 변환시킨 것)를 실행했고, 여기 R에서 출력물을 스니펫(snippet)으로만 가져왔는데, 얼마나 많은 수의 행이 있는지에 관한 메타 정보까진 캐치하진 못했어. 그저 출력물에 몇 개의 열이 있다는 것 정도만 캐치했어”라고 이해할 수 있습니다.이 튜토리얼은 파트 1 입니다. 다음 파트에서 가져온 테이블에 얼마나 많은 행들이 존재하는 지와 같은 메타 정보들을 R로 어떻게 가져오는지에 대해 알아볼 예정입니다.
3 DB 연결 해제하기
작업이 끝나면 연결을 해제하는 것을 잊지마세요!
dbDisconnect(con) # db 연결 닫기연결 해제가 체크는 dbListTable(con)을 실행해보시면 됩니다. 연결해제가 잘 되었다면 에러문이 출력될겁니다.
다음 파트에서 배울 내용
{DBI}: R의 데이터베이스 인터페이스에 관한 메인 패키지입니다.- 데이터 R로 가져오기
세션정보를 보려면 누르세요
─ Session info ───────────────────────────────────────────────────────────────
setting value
version R version 4.2.1 (2022-06-23)
os macOS Monterey 12.6
system aarch64, darwin20
ui X11
language (EN)
collate en_US.UTF-8
ctype en_US.UTF-8
tz Asia/Seoul
date 2022-09-19
pandoc 2.18 @ /Applications/RStudio.app/Contents/MacOS/quarto/bin/tools/ (via rmarkdown)
quarto 1.0.38 @ /usr/local/bin/quarto
─ Packages ───────────────────────────────────────────────────────────────────
package * version date (UTC) lib source
DBI * 1.1.3 2022-06-18 [1] CRAN (R 4.2.0)
dbplyr * 2.2.1 2022-06-27 [1] CRAN (R 4.2.0)
dplyr * 1.0.9 2022-04-28 [1] CRAN (R 4.2.0)
odbc * 1.3.3 2021-11-30 [1] CRAN (R 4.2.0)
RSQLite * 2.2.15 2022-07-17 [1] CRAN (R 4.2.0)
sessioninfo * 1.2.2 2021-12-06 [1] CRAN (R 4.2.0)
[1] /Library/Frameworks/R.framework/Versions/4.2-arm64/Resources/library
──────────────────────────────────────────────────────────────────────────────참고문헌
Naidoo, Vebash. 2020. “Sciencificity’s Blog: Using the Tidyverse with Databases - Part i,” December. https://sciencificity-blog.netlify.app/posts/2020-12-12-using-the-tidyverse-with-databases/.
각주
relational database management system, or RDBMS↩︎
Structured Query Language↩︎
공급업체마다 SQL 쿼리문에 조금씩 차이가 있는 부분을 방언의 의미를 갖는 dialect로 표현한 것 같습니다.↩︎
명확하게 정해진 바는 없어보이나, d-plier라고 흔히 읽는 듯 합니다. 패키지의 스티커 이미지를 보면 플라이어가 그려져 있습니다.↩︎
DataBase Interface, 데이터베이스 인터페이스의 약어↩︎
본 예제에서는 다루지 않지만, Rstudio DB에서 많은 것을 확인할 수 있습니다.↩︎
본 예제에서는 다루지 않지만, Rstudio DB에서 확인할 수 있습니다.↩︎
라이센스
인용
BibTeX 인용:
@online{방태모2022,
author = {방태모},
title = {tidyverse로 데이터베이스랑 대화하기 - 1편},
date = {2022-04-07},
url = {https://taemobang.com/posts/2022-04-07-talk-with-database-using-tidyverse-part-i/},
langid = {kr}
}
인용방법
방태모. 2022. “tidyverse로 데이터베이스랑 대화하기 - 1편.”
April 7, 2022. https://taemobang.com/posts/2022-04-07-talk-with-database-using-tidyverse-part-i/.