R에서 DB 사용하기 - SQLite

 

 

 

R에서 DB 사용하기 - SQLite

How to use DB in R

R로 분석할 때 엑셀이나 csv 파일 외에 DB를 구축해서 DB 내 테이블에서 데이터를 불러오고 싶다면 간단하게 SQLite를 사용하는 방법이 있다.

SQLite란?

SQLite는 MySQL나 PostgreSQL와 같은 데이터베이스 관리 시스템이지만, 서버가 아니라 응용 프로그램에 넣어 사용하는 비교적 가벼운 데이터베이스이다. 영어권에서는 '에스큐엘라이트(ˌɛskjuːɛlˈlaɪt)'또는 '시퀄라이트(ˈsiːkwəl.laɪt)'라고 읽는다.

일반적인 RDBMS에 비해 대규모 작업에는 적합하지 않지만, 중소 규모라면 속도에 손색이 없다. 또 API는 단순히 라이브러리를 호출하는 것만 있으며, 데이터를 저장하는 데 하나의 파일만을 사용하는 것이 특징이다. 버전 3.3.8에서는 풀텍스트 검색 기능을 가진 FTS1 모듈이 지원된다. 컬럼을 삭제하거나 변경하는 것 등이 제한된다.

구글 안드로이드 운영 체제에 기본 탑재된 데이터베이스이기도 하다.

(출처: 위키백과)

SQLite는 위의 설명과 같이 데이터를 저장하는데 하나의 파일만을 사용하며, 그 하나의 sqlite 파일 안에 여러 개의 테이블을 가질 수 있다. 그러면 R에서 SQLite DB를 구축하고, 테이블을 만들고(Create), 데이터를 넣고(Insert), 수정하고(Update), 삭제하고(Delete), 테이블을 삭제하는(Drop) 방법을 알아보자.

 

 

-

(1) SQLite DB 구축하고 연결/연결해제 하기

(2) 테이블 생성하기

(3) 테이블에 데이터 넣기 - 한번에 1행(Row)씩

(4) 테이블에서 데이터 가져오기

(5) 테이블에 데이터 넣기 - 한번에 여러 행

(6) 테이블에 있는 데이터 수정하기

(7) 테이블에 있는 데이터 삭제하기

(8) 테이블 삭제하기

-

(1) SQLite DB 구축하고 연결/연결해제 하기

R에서 SQLite DB를 사용하려면 DBI와 RSQLite 라이브러리가 필요하다. 만약 두 라이브러리가 없다면 패키지를 설치해주자.

# 패키지가 없는 경우 설치하기
install.packages(c("DBI", "RSQLite"))

# DB 관련 함수를 사용하기 위한 라이브러리
library(DBI)

# SQLite 관련 함수를 사용하기 위한 라이브러리
library(RSQLite)

이제 ~/Desktop/R 폴더 경로에 test_db라는 DB를 만들어보자.

# SQLite DB 구축/연결하기
con <- dbConnect(SQLite(),
                 dbname = "C:/R/test_db.sqlite")

위 코드를 실행하면 해당 경로의 R 폴더 안에 test_db.sqlite 파일이 생성된 것을 볼 수 있다. 위에 설명했다시피 SQLite는 하나의 파일로 이루어진 DB이기 때문에 파일이 하나 생성된 것이다.

DB에 연결할 때에도 동일한 코드를 사용하면 된다. 위 코드는 기존에 test_db.sqlite라는 파일이 없다면 새로 생성하고, 이미 파일이 존재하는 경우에는 해당 DB에 연결한다.

이제 DB 연결을 해제하는 코드를 알아보자.

# SQLite DB 연결 해제
dbDisconnect(con)

DB 사용이 끝나면 꼭 DB 연결을 해제해 주어야 한다. 해제한 이후 다시 DB를 사용하려면 다시 dbConnect 함수를 사용하여 연결해주어야 한다.

 

 

(2) 테이블 생성하기

우선 R 샘플 데이터 iris를 사용하여 테스트 데이터를 생성해보자. DB에서는 컬럼명에 점( . )이 들어갈 수 없기 때문에 iris의 컬럼명에 있는 점( . )을 언더바( _ )로 변경하고 모두 소문자로 컬럼명을 변경한 iris_rename을 생성한다.

iris_rename <- iris
names(iris_rename) <- c("sepal_length", "sepal_width", "petal_length", "petal_width", "species")

내가 만들려는 테이블의 이름은 sample_iris 이고 총 6개 컬럼이 있는 테이블로, 아래와 같은 형태로 이루어져 있는 테이블이다.

먼저 DB에 연결해주고 dbSendQuery 함수를 이용해 테이블을 생성하는 쿼리(Query)를 날린다. 아래 쿼리에서 irisid는 Primary Key 컬럼으로 데이터가 입력될 때마다 integer로 1부터 자동 증가 입력되는 컬럼으로 정의했다. (Autoincrement)

# test_db DB에 연결하기
con <- dbConnect(SQLite(),
                 dbname = "C:/R/test_db.sqlite")

# sample_iris 테이블 생성하기
rs <- dbSendQuery(con,
                  "CREATE TABLE IF NOT EXISTS sample_iris 
                  (irisid INTEGER PRIMARY KEY AUTOINCREMENT, 
                  sepal_length NUMERIC,
                  sepal_width NUMERIC,
                  petal_length NUMERIC,
                  petal_width NUMERIC,
                  species TEXT)")
dbClearResult(rs)

쿼리를 날릴 때 사용하는 함수는 크게 2가지 종류가 있다.

먼저 위에서 사용한 dbSendQuery는 DB에 쿼리를 보낼 때 사용한다. 이 함수는 데이터를 가지고 오지는 못한다. 또한 dbSendQuery의 경우 dbClearResult 함수를 이용하여 보낸 쿼리의 처리결과를 제거해주어야 한다.

DB에 쿼리를 보내서 데이터를 가져오는 것은 dbGetQuery 함수만이 가능하다. 따라서 SELECT 하여 DB에서 데이터를 가져오는 쿼리인 경우 dbGetQuery를 사용하고, 그 외 테이블을 만들거나 데이터를 입력/수정/삭제하는 쿼리인 경우는 dbSendQuery를 사용하면 된다.

이제 sample_iris 테이블이 잘 생성되었는지 확인해보자.

dbExistsTable(con, "sample_iris")

 

만약 해당 테이블이 존재하면 위와 같이 TRUE, 존재하지 않으면 FALSE 결과가 나오게 된다.

 

 

(3) 테이블에 데이터 넣기 - 한번에 1행(Row)씩

이제 생성한 sample_iris 테이블에 데이터를 넣어보자.

rs <- dbSendQuery(con,
                  "INSERT INTO sample_iris
                  (sepal_length, sepal_width, petal_length, petal_width, species)
                  VALUES
                  (\'5.1\',
                  \'3.5\',
                  \'1.4\',
                  \'0.2\',
                  \'setosa\')")
dbClearResult(rs)

만약 특정 변수에 할당된 데이터를 가져오고 싶다면 아래와 같이 paste0( ) 함수를 사용하여 쿼리문을 만들어준다.

new_species <- "virginica"
rs <- dbSendQuery(con,
                  paste0("INSERT INTO sample_iris
                         (sepal_length, sepal_width, petal_length, petal_width, species)
                         VALUES
                         (\'5.1\',
                         \'3.5\',
                         \'1.4\',
                         \'0.2\',
                         \'", new_species ,"\')"))
dbClearResult(rs)

1행씩 넣는 경우는 대부분 다른 데이터에서 업데이트가 발생한 경우에 데이터를 넣어주는 경우가 많다. 하지만 처음에 세팅을 위해 한번에 여러 건의 데이터를 넣어주거나 한번의 업데이트로 여러 줄의 데이터를 넣어야 하는 경우에는 아래에 '(5) 테이블에 데이터 넣기 - 한번에 여러 행'을 참고하자.

 

 

(4) 테이블에서 데이터 가져오기

방금 넣은 두 줄의 데이터가 잘 들어갔는지 쿼리를 날려 확인해보자.

sample_iris <- dbGetQuery(con, "SELECT * FROM sample_iris")

 

 

위와 같이 데이터가 잘 들어간 것을 확인할 수 있다. 만약 조건을 넣어 가져오고 싶다면 WHERE를 사용한 쿼리를 작성하면 된다.

sample_iris <- dbGetQuery(con, "SELECT * FROM sample_iris WHERE species = 'virginica'")

이처럼 SQL문 형식 안에서 자유롭게 사용하여 쿼리를 작성할 수 있다.

 

(5) 테이블에 데이터 넣기 - 한번에 여러 행

만약 1행이 아니라 여러 건의 데이터를 한번에 넣고 싶다면 아래와 같이 dbWriteTable 함수를 사용한다.

# iris_rename 150건 데이터를 한번에 sample_iris 테이블에 넣기
dbWriteTable(con, "sample_iris", iris_rename, row.names = FALSE, append=TRUE)

# sample_iris 테이블에 잘 들어갔는지 확인
sample_iris <- dbGetQuery(con, "SELECT * FROM sample_iris")
dim(sample_iris)

이렇게 총 152건의 데이터가 들어가 있음을 확인할 수 있다. (1건, 1건, 150건)

 

 

(6) 테이블에 있는 데이터 수정하기

이미 DB 테이블에 들어간 데이터 중 일부를 수정하려면 쿼리에서 INSERT 구문을 활용하면 된다. 예제로 species가 virginica인 경우 species를 versicolor로 변경하는 코드를 작성해보자. 당연히 DB에 연결된 상태여야 함을 잊지말자!

# species를 virginica -> versicolor 변경
rs <- dbSendQuery(con, "UPDATE sample_iris SET species = 'versicolor' WHERE species = 'virginica'")
dbClearResult(rs)

# species가 virginica인 데이터가 없으면 성공
sample_virginica <- dbGetQuery(con, "SELECT * FROM sample_iris WHERE species = 'virginica'")

# sample_virginica의 행수 열수 출력
dim(sample_virginica)

sample_virginica는 0건임을 확인할 수 있다. 즉 virginica가 모두 versicolor로 데이터가 잘 수정되었다는 뜻이다.

-

-  

 

 

(7) 테이블에 있는 데이터 삭제하기

먼저 특정 조건에 있는 데이터를 삭제하는 경우를 살펴보자. 예제로 species가 versicolor인 행을 모두 삭제해보자.

# species가 versicolor인 행 삭제
rs <- dbSendQuery(con, "DELETE FROM sample_iris WHERE species = 'versicolor'")
dbClearResult(rs)

# species가 versicolor인 데이터가 없으면 성공
sample_versicolor <- dbGetQuery(con, "SELECT * FROM sample_iris WHERE species = 'versicolor'")

# sample_versicolor의 행수 열수 출력
dim(sample_versicolor)

# sample_iris를 가져와서 행수 열수 출력
sample_iris <- dbGetQuery(con, "SELECT * FROM sample_iris")
dim(sample_iris)

위와 같이 sample_iris가 51건으로 줄은 것을 확인할 수 있다.

이제 전체 데이터를 삭제해보자. 일반적으로는 쿼리에서 TRUNCATE 구문을 많이 사용하는데 SQLite의 경우 TRUNCATE 구문을 지원하지 않는다고 한다. 대신 DELETE 구문을 사용하면 된다. (참고로 PostgreSQL의 경우 TRUNCATE 구문을 지원한다.)

# sample_iris 테이블 내 전체 데이터 삭제
rs <- dbSendQuery(con, "DELETE FROM sample_iris")
dbClearResult(rs)

# sample_iris를 가져와서 행수 열수 출력
sample_iris <- dbGetQuery(con, "SELECT * FROM sample_iris")
dim(sample_iris)

 

 

(8) 테이블 삭제하기

이제는 테이블 자체는 삭제해보자. 테이블을 삭제하기 전에 테이블 내 데이터를 먼저 삭제해주는 것이 좋으니 '(7) 테이블에 있는 데이터 삭제하기'를 먼저 수행한 후 이 단락으로 돌아오기를 바란다. 내용이 모두 삭제되어 빈 껍데기만 있는 sample_iris 테이블을 삭제해보자.

# sample_iris 테이블 자체를 삭제
rs <- dbSendQuery(con, "DROP TABLE sample_iris")
dbClearResult(rs)

# sample_iris 테이블이 없기 때문에 에러 메시지 출력
sample_iris <- dbGetQuery(con, "SELECT * FROM sample_iris")

sample_iris가 존재하지 않기 때문에 위와 같은 에러 메시지가 출력된다.

에러 메시지를 보지 않고 테이블이 존재하는지 여부를 확인하기 위해서는 dbExistsTable 함수를 사용한다. sample_iris가 존재하는지 확인하는 코드는 아래와 같다.

# sample_iris 테이블이 있는지 확인 : 있으면 TRUE, 없으면 FALSE 출력
dbExistsTable(con, "sample_iris")

 

sample_iris 테이블이 삭제되었으므로 FALSE가 출력된다. if else 구문을 이용해 테이블이 존재하는 경우에만 SELECT 쿼리를 날리도록 코드를 작성하는 것도 에러를 줄일 수 있는 방법이다.

R에서 DB 사용 관련 다음 포스팅에서는 PostgreSQL에 대한 내용을 다뤄보도록 하겠다.

#dbclearresult#dbconnect#dbgetquery#dbi#dbsendquery#rquery#r#rstudio#rsqlite#sqlite#r코드#통계분석#데이터분석