[R 프로그래밍 언어] RODBC를 이용한 MySQL DB 접근 및 데이터 불러오기 + WordCloud, ggplot2 사용

티스토리 뷰

Software/Data Analytics

[R 프로그래밍 언어] RODBC를 이용한 MySQL DB 접근 및 데이터 불러오기 + WordCloud, ggplot2 사용

Arc Lab. 2016. 10. 25. 13:56

[업데이트 2016.11.23 15:08]

R 언어에서 ODBC를 통해 MySQL이 접근 가능하며, 기존에 사용하던 DB의 데이터 분석시 유용한 것 같습니다. 특히 SQL Query를 통해 불러오게 되는 데이터 형태가 data.frame이라 바로 그래프나 Word Cloud 같은 기능에 적용하기 편리한 것 같습니다.

1) MySQL ODBC for Windows 설치

저는 R Studio를 Windows에서 사용중이기 때문에, MySQL ODBC를 Windows용으로 설치하였습니다. 아래의 주소로 접속하여 Windows용 최신 버전 MySQL ODBC를 다운로드 및 설치합니다.

참고: https://dev.mysql.com/downloads/connector/odbc/

2) MySQL ODBC 설정

MySQL ODBC 설치가 완료된 후, Control Panel > Administrative Tools > Data Source(ODBC) 로 이동합니다. (Windows 7기준)

User DSN에서 Add를 통해 MySQL ODBC 5.3 Unicode Driver를 선택하여 추가합니다.

제가 테스트해본 MySQL DB는 UTF-8기반으로 되어 있어, Unicode Driver를 선택하였습니다.

다수의 한글/중문 데이터 포함된 MySQL DB이므로 Unicode Driver를 사용하였습니다.

이제 추가가 완료되었으면, 해당 User Data Source를 선택후 Configure버튼을 클릭합니다.

이제 기존에 사용하던 MySQL 정보를 입력합니다.

1. Data Source Name(DSN)

localhost와 같은 DSN을 입력합니다.(Mandatory)

2. Description

해당 DSN에 대한 설명을 입력하면 됩니다.(Optional)

3. TCP/IP Server,Port

MySQL DB가 위치하는 서버의 IP 주소/포트를 입력합니다.(Mandatory)

4. User ID

MySQL User ID를 입력합니다.(Mandatory)

5. Password

MySQL Password를 입력합니다.(Mandatory)

6. Database

MySQL DB의 DB 이름을 입력합니다.(Mandatory)

위의 6가지 정보를 입력후, Details << 버튼을 누른후 Connection 탭에서 Character Set를 utf8로 선택합니다. R Studio에서 계속 한글 등의 데이터가 ????나와서 많이 헤맸는데, 알고 보니 이 부분 설정이 utf8으로 안되어 있어서 발생한 문제였습니다.

이제 Test버튼을 눌러 정상적으로 Connection되었는지 확인합니다.

3) R Studio에서 RODBC 라이브러리 다운로드 및 설치

이제 R Studio에서 MySQL DB와 연동하기 위해 RODBC 라이브러리를 설치합니다.

1

> install.packages('RODBC')

이제 설치가 완료된 후, RODBC를 사용하기 위해 선언합니다.

1

> library(RODBC)

이로써 RODBC를 사용하기 위한 준비가 완료되었습니다.

4) RODBC를 통한 MySQL DB 연결 및 데이터 Query

아래와 같이 odbcConnect를 통해 MySQL DB를 연결합니다. 여기서 DBMSencoding을 꼭 UTF-8으로 해주어야 한글 등의 데이터가 정상적으로 표시되니 유의하시기 바랍니다.

1

> conn <- odbcConnect(dsn="localhost", uid = "root", pwd = "123456",DBMSencoding="UTF-8")

연결이 정상적으로 완료된 후, conn을 입력해보면 아래와 같이 연결된 정보가 표시됩니다.

1

> conn
Details:
  case=tolower
  DSN=localhost
  UID=root
  PWD=******

이제 SQL Query문을 만든 후, 데이터를 가져옵니다.

1

> data <- sqlQuery(conn, "SELECT * FROM tbl_fruits")

과일 종류별로 어떤 과일 데이터가 많은지 분포도를 알기 위해 가져온 데이터에서 name field를 가지고 count를 구하여 data.frame으로 만듭니다. 이제 만들어진 data.frame을 보면 아래와 같이 각 과일 종류별 데이터 레코드 개수를 보여줍니다.

1 
2

> res <- data.frame(fruits=c(names(summary(data$name))), num_of_fruits=c(summary(data$name))) 
> res
                         fruits        num_of_fruits
apple                    apple      49
apricot                 apricot      4
Banana                 Banana     7
blackberry         blackberry     2

이제 Word Cloud 함수를 이용해 분포도를 확인해봅니다.

1 
2

> set.seed(1)
> wordcloud(words=res$fruits, freq=res$num_of_fruits, min.freq = 1, max.words = 200, random.order = FALSE, rot.per = 0.3,colors=brewer.pal(8, "Dark2"))

아래의 결과와 같이 가장 많은 레코드를 가지는 과일 종류가 더 크게 표시되고 있음을 확인 할 수 있습니다.

마지막으로 다 사용한 ODBC Connection은 odbcClose() 함수를 통해 종료시킵니다.

1

> odbcClose(conn)

최종 R스크립트 소스는 다음과 같습니다.

> library(RODBC)
> library(NLP)
> library(tm)
> library(SnowballC)
> library(RColorBrewer)
> library(wordcloud)  
> conn <- odbcConnect(dsn="localhost", uid = "root", pwd = "123456",DBMSencoding="UTF-8")
> data <- sqlQuery(conn, "SELECT * FROM tbl_fruits")
> res <- data.frame(fruits=c(names(summary(data$name))), num_of_fruits=c(summary(data$name)))
> res$fruits <- toupper(res$fruits)
> set.seed(1)
> wordcloud(words=res$fruits, freq=res$num_of_fruits, min.freq = 1, max.words = 200, random.order = FALSE, rot.per = 0.3,colors=brewer.pal(8, "Dark2"))
> odbcClose(conn)

5) ggplot2 라이브러리 사용하여 막대 그래프 그리기

추가로 ggplot2 라이브러리를 사용하여 막대 그래프로 표시해봅니다. data.frame 데이터를 내림차순으로 정렬 및 상위 5개 데이터를 가져와서 표시합니다. data.frame에서 데이터 정렬을 위해 arrange 함수가 사용되는데, 이를 위해 plyr 라이브러리를 선언합니다.

desc함수 인자로는 data.frame의 컬럼명이 사용되며, 내림차순 정렬이 수행됩니다.

다음으로 ggplot 함수의 인자로 다음과 같이 설정합니다.

- data : top5 data.frame 데이터 지정

- aes() : x,y축 컬럼 데이터 지정

막대 그래프 사용을 위한 geom_bar함수의 인자입니다.

- stat: "identity" 기본적으로 identity를 지정하는데, 어떤 의미인지는 추가로 찾아봐야할 것 같습니다.

- color: 막대 그래프의 외각선 색상을 지정합니다.

- fill: 막대 그래프의 내부 영역 색상을 지정합니다.

> library(RODBC)
> library(NLP)
> library(ggplot2) 
> library(plyr) 
> conn <- odbcConnect(dsn="localhost", uid = "root", pwd = "123456",DBMSencoding="UTF-8")
> data <- sqlQuery(conn, "SELECT * FROM tbl_fruits")
> res <- data.frame(fruits=c(names(summary(data$name))), num_of_fruits=c(summary(data$name)))
> res <- arrange(res, desc(num_of_fruits))
> res_top5 <- head(res, 5)
> ggplot(data=res_top5, aes(x=fruits, y=num_of_fruits, group=1)) + geom_bar(stat="identity", color="black", fill="#0072B2")
> odbcClose(conn)

위의 R 스크립트를 실행 결과는 아래와 같습니다.

추가로 아래와 같이 막대 그래프의 색상 지정, 각 과일별 개수를 막대 label로 표시 및 오른쪽에 과일에 대한 legend 출력을 적용한 결과입니다.

1

> ggplot(data=res_top5, aes(x=fruits, y=num_of_fruits, group=1)) + geom_bar(stat="identity", aes(fill=fruits), position=position_dodge(), colour="black")  + theme(legend.position="right", legend.direction="vertical") + geom_text(data=res_top5, aes(label=res_top5$num_of_fruits), position=position_identity(), vjust=1.5)