R과 MySQL 데이터베이스 연결 방법
페이지 정보
본문
데이터베이스에 연결된 R 환경은 데이터베이스의 저장 용량과 R의 계산능력 사용할 수 있습니다. 이번 포스팅에서는 MySQL과 R을 서로 연결하는 방법을 정리해 보겠습니다.
MySQL과 R을 연결하기 위해서는 RMySQL
패키지를 이용합니다. 이 패키지는 R 환경을 위한 데이터베이스 인터페이스와 MySQL 드라이버를 포함한 패키지로 주로 다음과 같은 상황에서 사용할 수 있습니다.
- 데이터가 MySQL 데이터베이스에 저장되어 있고 R 환경에서 이 데이터를 추출해 분석하고 싶을 때
- R에서 작성한 데이터를 MySQL의 데이터베이스에 저장하고 싶을 때
MySQL에 ODBC드라이버를 인스톨하면 RODBC
을 이용해도 같은 기능을 실현할 수 있습니다. 최근 버전의 RMySQL
는 DBI
패키지에 구현된 데이터베이스 인터페이스 정의를 따르고 있습니다.
MS Windows
R 2.12 이후로는 RMySQL의 바이너리 파일이 배포되지 않기 때문에 사용자가 컴파일해서 설치해야 합니다.
필수 소프트웨어
- Rtools (R의 버전에 대응하는 것)
- MySQL Client C API library (MySQL Community Server 에도 포함되어있음)
- MySQL Community Server를 PC에 디폴트로 설치한 경우에는 MySQL Client C API library가 이미 들어가 있기 때문에 별도로 설치할 필요는 없습니다. 단, 별도의 호스트에 설치된 MySQL Server를 이용하는 경우에는 MySQL Client C API library를 별도로 설치해야 합니다.
RMySQL 컴파일 순서
R의 환경변수 MYSQL_HOME에 MySQL 설치 디렉터리를 8.3형식으로 지정합니다.
MySQL Community Server 5.6의 MSI Installer판을 디폴트로 설치한 경우C:\Program Files\R\설치한 R 버전\etc
디렉터리에Renviron.site
라는 이름으로 텍스트 파일을 작성하여 이하의 내용을 입력합니다.MYSQL_HOME=C:/PROGRA~1/MySQL/MYSQLS~1.6
MySQL이 설치된 디렉터리에 있는
lib\libmysql.lib
을lib\opt\libmysql.lib
로 복사합니다.R에서 다음 명령을 실행합니다. 이때 R 콘솔에서
\Rtools\bin
,\Rtools\gcc-4.6.3\bin
이하의 실행 파일들을 사용하게 되므로 두 디렉터리의 Path가 지정되어 있어야 합니다(윈도즈에서 Path를 추가하는 방법은 이곳을 참조하세요). 만약 RStudio에서 컴파일을 한다면 Path 지정 필요 없이 그냥 알아서 해 줍니다.
> install.packages("DBI")
> install.packages("RMySQL", type = "source")
리눅스 (Ubuntu)
다음 명령을 실행하여 R과 MySQL을 설치합니다.
sudo apt-get update
sudo apt-get install r-base
sudo apt-get install r-dev
sudo apt-get install r-cran-rmysql
sudo apt-get install r-cran-dbi
sudo apt-get install mysql-server my-client
sudo apt-get install libmysqlclient-dev
윈도즈의 삽질에 비하면 정말 간단하죠? R의 태생이 그런지라 불편 없이 R을 사용하고자 한다면 리눅스 환경을 강력 추천합니다.
사용법
R과 MySQL이 같은 호스트에서 실행되고 있고, test_db라는 데이터베이스에 test_user라는 사용자가 접근권한을 가지고 있다고 가정할 때, MySQL의 test_db 안의 test_table이라는 테이블의 내용을 쿼리를 이용하여 R의 데이터프레임 test.table로 저장하기 위해서는
> library(RMySQL)
> con <- dbConnect(m, dbname = "test_db", user = "test_user")
> query.result <- dbSendQuery(con, "SELECT * FROM test_table")
> test.table <- fetch(query.result)
> dbDisconnect(con)
여기서 dbConnect()
는 데이터베이스와 접속을 개시하는 함수, dbDisconnect()
는 접속을 종료하는 함수입니다. dbSendQuery()
은 SQL문을 MySQL서버에 보내는 함수이며 그 응답을 받아 R의 데이터프레임으로 저장해 주는 함수가 fetch()
입니다.
dbSendQuery()
와 fetch()
를 동시에 수행하는 dbGetQuery()
함수를 사용하여도 무방합니다.
> library(RMySQL)
> con <- dbConnect(dbDriver("MySQL"), dbname = "test_db", user = "test_user",
+ password = "password")
> dbListTables(con) #DB abc에 있는 테이블목록 확인
> test.table <- dbGetQuery(con, "SELECT * FROM test_table")
> dbDisconnect(con)
R에서 작성된 result라는 데이터프레임을 MySQL 데이터베이스에 test_table2
라는 테이블로 저장하기 위해서는 dbWriteTable()
함수를 이용합니다.
> dbWriteTable(con, "test_table2", result, overwrite = TRUE)
만약, MySQL 서버가 R과 같은 호스트에 있지 않고 dbserver.domain라는 호스트에서 운영되고 있다면、
> library(RMySQL)
> con <- dbConnect(dbDriver("MySQL"), host = "dbserver.domain", dbname = "test_db",
+ user = "test_user", password = "password")
>
R 언어에서 명시적으로 데이터베이스 사용자 및 암호를 표시할 때 보안상의 문제점을 일으킬 수 있으므로 로컬 디렉터리 구성 파일 (~/.my/cnf)에서 MySQL을 사용하는 사용자 그룹을 정의 할 수 있습니다.
[local]
user = root
password = ultra_secret
host = localhost
[nerds]
user = supernerd
password = galaxy
host = dbserver.domain
그런 다음 R 사용자 그룹을 사용하여
> library(RMySQL)
> con <- dbConnect(MySQL(), group = "nerds", dbname = "test_db")
> test.table <- dbGetQuery(con, "SELECT * FROM test_table")
> dbDisconnect(con)
지금까지 R과 MySQL을 연결하는 방법을 방법에 대해 정리해 보았습니다. 적절한 SQL 쿼리를 이용해 필요한 데이터를 만들 수 있다면 데이터 전체를 R로 불러와 데이터 조작을 하는 것보다 메모리의 효율적인 사용에 매우 큰 도움이 될 수 있습니다.
관련링크
댓글목록
등록된 댓글이 없습니다.