학창시절부터 실질적인 데이터를 접근하기 위해 SQL을 익히는 것은 중요하다는 이야기를 실무를 하시는 분들에게 공통적으로 들어왔었다.
데이터를 분석하기 위해서는 분석가가 분석범위로 설정한 데이터를 손에 쥐고 있어야 가능하고, 내 입맛에 맞게 손에 집기 위한 스킬이라는 것이기에 이 말을 매우매우 공감하였었다.
하지만 그 당시에 통계적인 학문 자체에 대해 집중적으로 공부하던 ’전공’이라는 틀 안에서 노력을 했던 터라 데이터를 굳이 Database 에서 가저와야 할 정도의 필요성을 느낄만한 동기를 가질 수 없었던게 아니었나 회고해 본다.
DBMS(DataBases Management System) 안에 있는 데이터를 만나볼 기회는 큰 규모의 프로젝트나, 데이터를 소유하고 있는 회사에서 일을 해 보지 않는 이상 접하기 조차 어려웠던것 아닐까 싶다. (Excel과 같은 스프레드시트로 관리하는게 가장 대중적이고 효율적일테니까)
실제로 필자도 위의 생각을 가지고 노력을 했다고 생각했지만, 지금 돌이켜 보면 그 노력은 맹목적이었고
저 첫기회를 찾아 접하고 경험하기 까지 걸린 시간이 대학생 막학기 시절 때 이었으니 말이다.

모순적이게도 직장을 얻고 관련분야 일을 하면서 SQL을 이용해야하는 참된 이유를 알게 되었고,
그 전까지 익히던 SQL이 목적없이 진도빼기 식으로 공부한 것 아닌가 느껴졌다.

지금이라도 뒤늦었다 생각하고 목적성 있게 주체적으로 공부해 보기 위해 이런저런 시도들을 해 보고 있다.
그 시도중 학창시절당시 DB 를 경험해 보지 못했던 나의 사각지대 환경에 대해서 조금 더 생각들이 많아졌고
서버같은 것 없이 로컬상에서도 손쉽게 DB 환경을 구축하여 SQL 을 직접 이용해 볼 수 있는 가벼운 환경을 만들 수 있다면 좋지 않을까 했고, 이 생각을 후배 등에게 전파하기도 용이하겠다는 생각을 덤으로 하면서
위의 제목을 주제로한 글을 작성하게 되었다.

SQLite

다양한 종류의 데이터베이스들이 있는데
관리하여야 할 데이터의 스케일과 특성에 따라 특화되고 강점들이 각기 다른 DB 들이 생기며 현존하는 것 같다.
그 중 본 글의 특성(시작하기 쉽고 가벼운)에 맞는 SQLite 이란 DB를 소개하고자 한다.

사람들이 컴퓨터를 사용할 때 가장 많이 보고 접하는 것 중 하나가 파일(File)인데
이 파일은 컴퓨터에서 정보를 담는 그릇의 가장 대중적인 단위라고 생각한다.
때문에 가장 직관적이고 이해하기 쉬운 단위라고 본다.
SQLite 라는 DB 는 눈에 보이는 파일형태로 관리가 되기 때문에 거부감이 덜할 것이다.
필자는 약 1년전 rdevteam 활동을 하면서 SQLite 를 공유받게 되었고 그때 처음 알게되었다.

그리하여
본 글에서는 SQLite 라는 파일DB 를 RSQLite 패키지를 이용해 만들어 보는 것을 주제로 한다.

RSQLite package install

RSQLite 패키지는 R 에서 SQLite 인터페이스를 제공해주는 패키지이다.

install.packages("RSQLite")
library(RSQLite)

위의 코드를 통해 패키지를 설치하고 로드한다.
또한 R에서 DB 와의 인터페이스 함수를 제공하는 DBI 패키지도 로드한다.

library(DBI)

SQLite 가지고 놀아보기

DB 를 만들기위한 놀이터를 만들것이다. 현 워킹디렉토리에 data 디렉토리를 만든다.
R에서 폴더를 만들수도 있는데 아래 코드를 실행하면 워킹디렉토리를 기준으로 data 폴더를 생성시킨다.

dir.create("data")

그다음 아래 코드를 실행하게 되면 방금만든 data 폴더 안에 SQLiteDB.sqlite 라는 파일이 생성될 것이다.

con <- dbConnect(SQLite(), "data/SQLiteDB.sqlite")

이 파일이 파일 DB 이고 이 파일에 연결정보는 con 으로 관리된다.
con 을 명시하여 데이터테이블을 생성시키고, 삭제하는 실습을 곧 하게 될 것인데
그렇게 되면 모두 커넥션이 맺어진 data/SQLiteDB.sqlite 파일에 적용된다는점을 미리 말해 둔다.

테이블 생성하기

가지고 놀아볼 데이터를 R에서 생성시켜보자 정말 별거 아닌 데이터지만 쓸데없이 크게 (n = 100000) 만들어보는 코드이다.

library(stringi)
n = 100000
data <- data.frame(id = stri_rand_strings(n, 12), col1 = rnorm(n), col2 = runif(n)) %>% tbl_df
data

## # A tibble: 100,000 x 3
##    id             col1  col2
##    <chr>         <dbl> <dbl>
##  1 rIE8F9sSaYTg -0.584 0.794
##  2 VdURMrRBzJnK  1.76  0.444
##  3 XPalm4OzhTqg  0.680 0.543
##  4 rDfHVMnc2UxG -0.262 0.804
##  5 xuRKywMF02vA  0.323 0.938
##  6 GDsDVNFjHApd -0.818 0.508
##  7 1zuKONsUMLTw -0.737 0.480
##  8 VlCzuSv2zIwv  0.320 0.157
##  9 X4H0KoPkb3Ou  0.821 0.199
## 10 GbDJhiPZpHJC -0.337 0.456
## # ... with 99,990 more rows

위의 데이터는 지금 R session 에 있는 장난감 데이터이다.
이 장난감이 R 이라는 놀이터 공간에만 있을 뿐이다.
우리는 이 장난감을 그대로 SQLite 데이터베이스 라는 놀이터에도 복사해 장난감을 납두고 싶다고 하자.
데이터베이스 라는 놀이터는 보통 Database 혹은 Schema 를 상위 단위로 하고, 그 하위 개념을 Table 로 둔다.
우리가 R 에서 만든 장난감은 data.frame 형식인데 이것이 Table 과 매우 유사한 형태로 호환이 가능하므로 결국 데이터베이스 하위개념인 Table 안에 얼마든지 복사가 가능하다.
SQLite DB안에 테이블 단위로 이 데이터를 저장하고 싶은 경우
DBI 패키지에서 제공하는 dbWriteTable() 함수를 이용해 연결정보가 담긴 con 을 안에 명시해주고
저장하고 싶은 대상과 명칭을 입력하면 된다.

즉 아래 코드를 실행하면 된다.

dbWriteTable(con, "data", data)

이것이 실행되면 현 상황은 SQLite DB 안에 data 라는 테이블이 생성된 상태이다.

Database 에는 다수의 테이블을 입력할 수 있다.
R의 빌트인 데이터 cars 와 iris 를 더 저장해보자.

dbWriteTable(con, "cars", cars)
dbWriteTable(con, "iris", iris)

저장이 정말 잘 되었는지 확인해 보고싶은데
그전에 테이블 하나 삭제만 더 해보고 그다음에 확인해 보겠다.

테이블 삭제하기

마지막에 실행하여 추가한 iris 데이터를 삭제해보자
dbRemoveTable() 함수가 테이블 삭제를 해 준다.

dbRemoveTable(con, "iris")

SQL 구문 실행하여 데이터 읽어들이기

위의 실습으로 data, cars, iris 3개의 테이블이 저장되었다가 iris 테이블은 삭제되었을 것이다.

dbListTables(con)

## [1] "cars" "data"

그렇다 이처럼 두개테이블만이 확인된다.

각 테이블의 헤더 샘플을 각각 SQL 구문을 통해 읽어들여 보겠다.
SQL 의 LIMIT 을 통해 10개만 확인해 보았다.

dbGetQuery(con, "SELECT * FROM data LIMIT 10;")

##              id        col1      col2
## 1  1jnj6MuXod5e  0.66293612 0.7739772
## 2  Vlm62uuOkaSw -0.59463920 0.4615983
## 3  aYnQ6gTp3PiU  0.02231377 0.5409450
## 4  Waf4r9O3ZFwT -1.27770275 0.6610312
## 5  vpHSpkLLGhaZ -0.05661095 0.6884008
## 6  RX0hmr2dsIu5 -0.02835238 0.9461622
## 7  vS6zAovPydaw -0.38382133 0.3351209
## 8  ltAX54IAsIq9  1.69635626 0.9901561
## 9  3N1AUeZb4JNa  0.37225877 0.9484985
## 10 RBnWtY5PKoHG  0.40751041 0.8362601

dbGetQuery(con, "SELECT * FROM cars LIMIT 10;")

##    speed dist
## 1      4    2
## 2      4   10
## 3      7    4
## 4      7   22
## 5      8   16
## 6      9   10
## 7     10   18
## 8     10   26
## 9     10   34
## 10    11   17

iris 테이블을 확인해 볼 경우

dbGetQuery(con, "SELECT * FROM iris LIMIT 10;")

Error in result_create(conn@ptr, statement) : no such table: iris

삭제가 되었기 때문에 테이블을 찾을 수 없다는 에러가 확인된다.

실감을 위해 지금까지 실습한 R 환경에서 잠깐 벗어나
데이터베이스 관리도구등으로 data/SQLiteDB.sqlite 에 연결하여 확인하면 이런모습이다. (DBeaver 이라는 SQL 클라이언트를 예제로 한 사진이다)

이처럼 R상에서 SQL 구문을 실행해 볼 수 도 있고
위 사진처럼 SQL 클라이언트에서도 (당연히)얼마든지 SQL ‘질의’를 통해 원하는 데이터를 ’답변’ 받을 수 있다.

덧붙여

DBI package 는 R상에서 DB 간 연결을 돕는 표준화된 함수를 제공한다.
그리고 이 틀 안에서 DB의 종류별로 connection 방법이 각기 조금씩 다른부분만 커스터마이징 하는 방식이다.
본 글의 예제 DB 는 SQLite 이었다.
SQLite 간 연결은 “RSQLite” package 의 도움을 받고 연결 후 연결객체¹를 사용한 DB 간 통신은 모두 DBI 표준함수를 이용하는 방식이다.
위의 실습에서 사용했던 dbWriteTable(), dbRemoveTable(), dbListTables(), dbGetQuery() 함수들 모두 DBI 패키지 함수들이다.

만약 SQLite 가 아닌 다른 DB 이면 그 DB 에 맞는 패키지가 보통 존재한다.
예를 들면
Impala 로 연결해야할 땐 “RImpala” 라는 패키지를,
Presto DB 에 연결해야 할 땐 “RPresto” 패키지를,
MySQL 과 연결해야할 땐 “RMySQL” 패키지를 이용했었다.
odbc 혹은 jdbc 규격으로 연결해야할 경우 각각 “RODBC”, “RJDBC” 의 패키지를 이용해 각기 다른 방식으로 커넥션을 맺고, 그 커넥션 정보가 담긴 연결객체를 DBI 패키지와 조합하여 사용하는 방식 또한 마찬가지이다.

이를 볼 때 DBI package 는 여러가지 데이터베이스 메니지먼트 시스템(DBMS)들에 대해서 표준화 되고 동일기능을 가진 함수로 실행되기 위한 방향성이 아주 오래전 부터 있던 패키지인 것 같다.