[R프로그래밍 기초 강좌] MySQL 연동하기, RMYSQL

[R프로그래밍 기초 강좌] MySQL 연동하기, RMYSQL

R프로그램과 MySQL 연동 순서

1. 컴퓨터에 RMySQL 패키지를 설치.

2. dbConnect 함수를 사용해 데이터베이스 연결.

3. dbGetQuer로 SELECT를 시작하고 결과 세트 반환.

4. dbDisconnect를 사용해 데이터베이스 연결 종료.

여기서 소개하는 R프로그래밍 기초 강좌 레시피는 RMySQL 패키지가 컴퓨터에 설치되어 있어야 한다.

그리고 그 패키지는 다시 MySQL 클라이언트 소프트웨어가 있어야 한다. MySQL 클라이언트 소프트웨어가 아직 설치 및 환경 설정되어 있지 않다면 MySQL 문서를 읽거나 시스템 관리자에게 물어보자.

dbConnect 함수는 MySQL 데이터베이스로의 연결을 만든다. 그리고 이후에 쓰이는 R프로그래밍 RMySQL 함수 호출에 사용되는 연결 객체를 반환한다.

library(RMySQL)
con <- dbConnect(
    MySQL(), user="userid", password="pswd",
    host="hostname", client.flag=CLIENT_MULTI_RESULTS)

여러 개의 결과 세트(result set)를 정확하게 다루려면 client.flag=CLIENT_MULTI_RESULTS 설정이 꼭 필요하다.

쿼리들이 하나의 결과 세트만 반환한다고 하더라도, R프로그래밍에선 client.flag를 꼭 이렇게 설정해야 한다. 왜냐하면 MySQL이 여러분의 데이터 뒤에 상태 결과 세트들을 추가로 넣을 수도 있기 때문이다.

사용자 이름, 패스워드, 호스트 매개변수들은 'mysql' 클라이언트 프로그램을 통해 MySQL에 접근할 때 사용되는 것과 같은 매개변수이다. 여기 주어진 예에서는 dbConnect 호출에 이 매개변수들을 손으로 입력해 넣었다.

RGui 32 비트 연동 화면

사실 이는 문제의 소지가 있다. (R프로그래밍 기초 강좌라 일부러 유발)

여러분의 패스워드를 그대로 노출하기 때문에 보안 문제가 생긴다. 또 패스워드나 호스트가 바뀌는 경우 직접 입력해 넣은 값들을 찾아내야 하게 되면서 엄청 골치 아파진다. 그래서 나는 R프로그래밍 기초 강좌 시간에 MySQL의 보안 메커니즘을 사용하기를 강력히 권한다.

앞서 말한 세 개의 매개변수를 MySQL 환경 설정 파일에 넣어 두자. 유닉스에서는 $HOME/.my.cnf에 있고 윈도에서는 C:\my.cnf에 있다. 그 파일은 본인 외에는 누구도 읽을 수 없도록 하자.

그 파일은 [client]와 같은 표시자로 섹션이 구분되어 있다. [client] 부분에 매개변수들을 넣어서, 여러분의 환경 설정 파일을 다음과 같이 만들자.

[client]
user = 사용자 아이디
password = 패스워드
host = 호스트 

일단 매개변수들이 환경 설정 파일에서 정의되면, 더는 dbConnect 호출에는 넣지 않아도 되니 R프로그래밍이 훨씬 간편해진다.

con <- dbConnect(MySQL(), client.flag=CLIENT_MULTI_RESULTS)

dbGetQuery 함수를 써서 SQL을 데이터베이스에 전송하고 결과 세트를 읽는다.

[R프로그래밍 기초 강좌] MySQL 연동하기, RMYSQL, CRAN - Normal Q-Q Plot

그렇게 하려면 데이터베이스 연결이 열려 있어야 한다.

sql <- "SELECT * from SurveyResults WHERE City = 'Chicago'"
rows <- dbGetQuery(con, sql)
Colored by Color Scripter

이것은 단지 예제일 뿐이므로 여러분은 당연히 자신의 SQL 쿼리를 작성해야 한다. 굳이 SELECT 명령문에만 제한할 필요는 없다.

어떤 SQL이든 결과 세트를 만들어내는 거라면 괜찮다. 내 경우에는 일반적으로 CALL 구문을 쓰는데, 내 SQL은 모두 저장 프로시저에 담겨 있고 이 저장 프로시저들에 SELECT 구문이 내장되어 있기 때문이다.

dbGetQuery를 사용하면 결과 세트를 데이터 프레임으로 포장해서 데이터 프레임을 반환해 주어서 편리하다. 이 함수는 SQL의 결과 세트 정보를 완벽하게 담아낸다.

여기서 나오는 결과 세트는 데이터 프레임과 마찬가지로 행과 열로 된 테이블 데이터 구조이다. 결과 세트의 열은 SQL의 SELECT 절에서 주어진 이름을 사용하며, R이 그것을 가져다 데이터 프레임의 열 이름을 만든다.

MySQL은 데이터로 된 첫 번째 결과 세트 뒤에 이어서 상태 정보를 담고 있는 두 번째 결과 세트를 반환할 수도 있다. 상태를 검사해도 되고 무시해도 되지만, 꼭 읽어봐야 함을 R프로그래밍 기초 강좌를 통해 숙지하자.

[R프로그래밍 기초 강좌] MySQL 연동하기, RMYSQL

그렇지 않으면 MySQL이 처리되지 않은 결과 세트가 있다고 불평하면서 일시 정지한다. 필요하다면 dbNextResult를 호출하자.

if (dbMoreResults(con)) dbNextResult(con)

여러 개의 쿼리를 수행할 때는 dbGetQuery를 반복적으로 호출해서, 각 호출 후 결과의 상태를 체크하자(필요하다면 읽기도 하고).

다 끝났다면 dbDisconnect로 데이터베이스 연결을 닫는다.

dbDisconnect(con)

다음은 주식 가격에 관한 내 데이터베이스에서 세 개의 행을 읽고 출력하는, 하나의 완전한 세션이다.

이 쿼리는 2008년의 마지막 3일간 IBM 주식의 가격을 선택한다. 여기서 쿼리는 my.cnf 파일에 사용자 이름, 패스워드, 호스트가 정의되어 있다고 가정한다.

> con <- dbConnect(MySQL(), client.flag=CLIENT_MULTI_RESULTS)
> sql <- paste("select * from DailyBar where Symbol = 'IBM'",
+              "and Day between '2008-12-29' and '2008-12-31'")
> rows <- dbGetQuery(con, sql)
> if (dbMoreResults(con)) dbNextResults(con)
> print(rows)
 
  Symbol        Day       Next OpenPx HighPx LowPx ClosePx AdjClosePx
1    IBM 2008-12-29 2008-12-30  81.72  81.72 79.68   81.52      81.52
2    IBM 2008-12-30 2008-12-31  81.83  83.64 81.25   83.55      83.55
3    IBM 2008-12-31 2009-01-02  83.50  85.00 83.50   84.16      84.16
  HistClosePx Volume OpenInt
1       81.25 6062600    NA
2       83.55 5774400    NA
3       84.16 6667700    NA
> dbDisconnect(con)
 
[1] TRUE

R프로그래밍 기초 강좌, MySQL 더 많은 정보

R은 오라클, Sybase, PostgreSQL, SQLite 등의 다른 RDBMS 시스템에서도 데이터를 읽어올 수 있다.

더 많은 정보는 CRAN(https://cran.r-project.org/doc/manuals/)에서 구할 수 있는 R Data Import/Export 가이드를 참고하라.

출처, R Cookbook, 폴 티터, 인사이트

[R프로그래밍 기초 강좌] MySQL 연동하기, RMYSQL