SQLite

SQLite는 MySQL나 PostgreSQL와 같은 데이터베이스 관리 시스템이지만, 서버가 아니라 응용 프로그램에 넣어 사용하는 비교적 가벼운 데이터베이스이다. 영어권에서는 ‘에스큐엘라이트(ˌɛskjuːɛlˈlaɪt)’[3]또는 ‘시퀄라이트(ˈsiːkwəl.laɪt)’[4]라고 읽는다. 일반적인 RDBMS에 비해 대규모 작업에는 적합하지 않지만, 중소 규모라면 속도에 손색이 없다. 또 API는 단순히 라이브러리를 호출하는 것만 있으며, 데이터를 저장하는 데 하나의 파일만을 사용하는 것이 특징이다. 버전 3.3.8에서는 풀텍스트 검색 기능을 가진 FTS1 모듈이 지원된다. 컬럼을 삭제하거나 변경하는 것 등이 제한된다. 구글 안드로이드 운영 체제에 기본 탑재된 데이터베이스이기도 하다. (출처: 위키백과(SQLITE))

그럼 이제 R에서 Sqlite DB를 구축하고, 사용해보도록 하자.

SQLite 자료형

그전에, SQLite의 자료형을 알아보도록 하자. R에서 쓰는 자료형과 다르기 때문에 이를 미리 파악해 두어야할 필요가 있다. 미리 알아두어야 할게 있는데, SQLite에는 Date타입이 존재 하지 않다. 그래서 DB에 집어 넣기 위해서는 TEXT로 변환 시켜줘야 한다.

• 참고: SQLite - Data Type
• 참고: SQLite 자료형

패키지 설치

우선 RSQLITE, DBI패키지를 설치 한다. RSQLITE는 R에서 Sqlite 인터페이스를 제공해주며, DBI는 R과 DB와의 인터페이스 함수를 제공해준다.

library(DBI)
library(RSQLite)

DB 생성

dbConnet() 함수를 통해 다음의 코드를 사용해준다. dbConnect()안에 SQLite()는 드라이버명이고, dbname에는 우리가 만들 DB이름을 적어 준다. 다음의 코드를 사용하고 확인해보면 test_sqlite.sqlte라는 파일이 생성된걸 확인할 수 있다.

con <- dbConnect(SQLite(), 
                 dbname = "test_sqlte.sqlite")

Table 생성

간단하게 iris 데이터를 생성해보자. 테이블 생성방법은 두가지인데 dbSendQuery()함수를 사용해서 쿼리문을 사용해서 생성하던가, dbWriteTable()함수를 사용해 생성과 동시에 통째로 집어 넣는 방법이 있다. dbWriteTable()는 마지막에 해보고 우선 쿼리문을 사용해서 테이블을 생성해보자. dbSendQuery()를 사용한다. 기본형식은 dbSendQuery(con, “query”)이며 con은 앞서 해두었던 dbConnect에 만들어 둔걸 사용한다. iris데이터를 예로 들어보자. 쿼리문은 구글을 통해 참고 하도록 하자. 후에 시간이 된다면 정리 하도록 하겠다.

dbSendQuery(con, "CREATE TABLE iris_db (
            Sepal_Length REAL,
            Sepal_Width REAL, 
            Petal_Length REAL, 
            Petal_Width REAL, 
            Species TEXT
            )")

## <SQLiteResult>
##   SQL  CREATE TABLE iris_db (
##             Sepal_Length REAL,
##             Sepal_Width REAL, 
##             Petal_Length REAL, 
##             Petal_Width REAL, 
##             Species TEXT
##             )
##   ROWS Fetched: 0 [complete]
##        Changed: 0

테이블 확인

DB가 잘 생성 되었는지 확인해보자. dbExistsTable(con, “db 이름”) 의 기본 형식을 띄며 TRUE/FALSE로 확인이 가능하다.

dbExistsTable(con, "iris_db")

## Warning: Closing open result set, pending rows

## [1] TRUE

테이블 리스트 확인

두번째 방법으로는 테이블의 리스트를 확인 해보는것이다. dbListTables()를 통해 모든 테이블리스트 조회가 가능하다.

dbListTables(con)

## [1] "iris_db" "mytable"

테이블에 데이터 넣기

이번엔 테이블에 데이터를 넣어보자. INSERT문을 사용하며 dbSendQuery함수에 적용시켜 주면 된다. INSERT문으로 데이터를 집어 넣으면 한줄씩 밖에 넣을수 없다.

dbSendQuery(con, "INSERT INTO iris_db
                  (Sepal_length, Sepal_width, Petal_length, Petal_width, Species)
                  VALUES
                  (5.1, 3.5, 1.4, 0.2, 'setosa')")

## <SQLiteResult>
##   SQL  INSERT INTO iris_db
##                   (Sepal_length, Sepal_width, Petal_length, Petal_width, Species)
##                   VALUES
##                   (5.1, 3.5, 1.4, 0.2, 'setosa')
##   ROWS Fetched: 0 [complete]
##        Changed: 1

테이블 데이터 확인

이제 집어 넣은 데이터를 확인 해보자. dbGetQuery()함수안에 SELECT문을 사용해서 Insert한 데이터를 확인 해보자.

dbGetQuery(con, "SELECT * FROM iris_db")

## Warning: Closing open result set, pending rows

##   Sepal_Length Sepal_Width Petal_Length Petal_Width Species
## 1          5.1         3.5          1.4         0.2  setosa

한번에 데이터 집어넣기

이번에는 한번에 데이터 전부를 넣어 보자. dbWriteTable()함수를 사용해 데이터 전체를 테이블에 집어 넣어 보려 한다. 기본 형식은 dbWriteTable(con, “db 이름”, db)

dbWriteTable(con, "iris_db_2", iris)

dbGetQuery(con, "SELECT * FROM iris_db_2") %>% 
  datatable(options = list(dom = 't'))

테이블 삭제

dbRemoveTable(con, "iris_db")
dbRemoveTable(con, "iris_db_2")

연결 종료

dbDisconnect(con)

총평

이렇게 R과 SQLite를 사용해 간단하게나마 DB에 대해 알아보았다. 한동안 기술 블로그 글이 뜸했는데, 스스로 생각에 많이 잠기게 되었었다.

• 새로운걸 공부하고 어떤 내용을 블로그에 옮겨야 하는가
• 이미 알고 어느정도 당연해진건 어떻게 블로그에 옮겨야 하는가

이런 저런 생각들이 많아 지다보니 블로깅을 하는데 몸이 멈칫하게 되기도 하더라. 생각해보니 블로그를 시작할때 공부했던걸 정리하는데 큰 초점을 두었던거 같다.

구글링을 하는데있어서, 제가 스스로 제 블로그를 참고 하도록 계속 정리하자. 이제야 맘이 조금 편해진다.

참고

• Data Carpentry contributors: R과 SQL
• 꼬낄콘의 분석일지: R에서 DB 사용하기 - SQLite
• Lovetoken: Sqlite DB를 R에서 만들어 보기