R에서 MySQL 사용하기: dplyr 패키지 활용 방법

흔히 R에서 MySQL을 사용하려면 RMySQL 패키지를 활용하는 방법이 일반적으로 채택된다. 그러나 이 방법은 복잡한 SQL 구문을 사용해야 해서 매우 귀찮다. 당신이 SQL도 잘 모르겠고 RMySQL의 속도도 별로 마음에 들지 않는데다 경우에 따라서 쿼리를 여러 가지로 파생해서 사용해야 한다면 어떻게 할까?

내가 권장하는 정답은 dplyr이다.

사실, 이 포스트는 ICIS2015에 가서 어떤 분이 한 질문 때문에 쓴다. 이 분께서 나에게 R에서 데이터베이스를 어떻게 쓰냐고 질문하셨는데 당시 불친절하게도 DBI기반의 패키지나 dplyr을 쓰시면 됩니다라고 했다. -_-;;; 생각해 보니 dplyr을 사용하는 방법에 대해 블로그에 올려두지 않았다는 것이 불현듯 오늘 아침 떠올라 긴급 포스팅을 한다.

dplyr 설치하기

R> install.packages("dplyr")

설치가 되면 R에서 MySQL을 사용하기 위해 필요한 준비가 다 끝난다.

이제 여러분 컴퓨터에 MySQL이 설치되어 있고, 서버가 실행 중이며, localhost를 사용하고, user의 ID가 user 패스워드는 password라고 가정하자. 이때 데이터베이스 이름은 test_db라고 하자.

MySQL 연결하기/끊기

R> con = src_mysql(dbname="test_db",

user="user",password="password",host="localhost")

함수 src_mysql (source from mysql)을 실행하여 con에 할당한다.

연결을 끊는 방법은 con을 메모리에서 해제하는 것이다.

R> rm(con)

MySQL 쿼리

dplyr 패키지는 chain rule을 이용한다. 보다 자세한 것은 이 블로그의 다른 글을 참조.
바로가기

R> test_table_ref <- con %>% tbl("test_table ")

위의 코드는 test_table이라는 이름의 table의 reference를 집어오는 과정을 보여준다. dplyr은 lazy loading을 하기 때문에(실제 데이터를 사용하기 전에는 쿼리를 실행하지 않음) test_table 전체 데이터를 loading하라고 시켰지만 속도는 대단히 빠르다.

실제 데이터를 loading하기 위해서는 collect() 함수를 실행하거나 test_table_ref의 데이터 일부를 사용해야 한다(head()함수나 sum()혹은 min() 등등).

R> test_table_data <- test_table_ref %>% collect()

이 코드는 다음 SQL과 동등하다.

SELECT * FROM test_table;

쿼리 파생

dplyr을 활용하여 MySQL을 사용하면 파생 쿼리를 손쉽게 만들 수 있는 장점이 있다.

만약 test_table이 다음과 같다고 하자.

test_table

  ind_id NUMBER PRIMARY KEY,

  name TEXT,

  age NUMBER,

  score NUMBER

이해를 위해서 가상의 코드를 만들었으니 참고하시길 바란다. 대문자는 데이터형이거나 primary key를 나타내는 키워드이다.

이제 test_table_ref에서 ind_id와 name만을 가지는 data.frame과 ind_id와 age만을 가지는 data.frame을 파생시켜 보겠다.

R> test_table_only_name_ref <- test_table_ref %>% select(ind_id,name)

R> test_table_only_age_ref <- test_table_ref %>% select(ind_id,age)

더불어 20대 미만과 이상을 분리해보자.

R> test_table_only_age_below20_ref <- test_table_only_age_ref %>% filter(age < 20)

R> test_table_only_age_over20_ref <- test_table_only_age_ref %>% filter(age >= 20)

lazy loading을 하니까 아무리 복잡하게 파생을 가해도 속도 저하는 없다. 당연히 파생쿼리를 collect()로 실행하거나 함수에 데이터를 넣거나 하면 쿼리를 실행한다.

마무리

이 외에도 inner_join()이나 left_join(), group_by(), summary(), mutate() 함수를 사용하거나 filter()안에 function을 집어 넣어 아주 복잡한 조건부 쿼리를 만들 수 있다. 보다 더 자세한 것은 dplyr을 공부해 보면 잘 알 수 있지만, MySQL을 사용하는 아주 강력한 방법임에는 틀림없다.

한 가지 당부할 점은, 한글 윈도우즈 시스템에서 R의 기본 문자셋이 CP949이기 때문에 UTF-8로 된 데이터베이스를 사용할 때는 주의해야 한다는 점이다. 물론 쿼리 자체는 잘 되지만 데이터가 변한되어 온다. 이를 다시 CP949로 만들기 위해서 iconv()함수를 사용해야 한다는 것을 잊지말자.