R로 DB 연결해서 쿼리를 실행하기까지
R로 DB 연결해서 쿼리를 실행하기까지
Hyukjun Cho
Application과 DB 연결하기
- JDBC
- JDBC(Java Database Connectivity)를 풀어 보면 직관적으로도 이해할 수 있다. 자바와 데이터베이스를 연결하기 위한 자바API를 말한다. 내가 사용하는 데이터베이스는 주로 MySQL이기 때문에 MySQL용 드라이버를 다운받아서 사용할 수 있다. (드라이버란 하드웨어를 제어하기 위해 필요한 컴퓨터 프로그램을 말한다.)
- ODBC
- ODBC(Open Database Connectivity)는 마이크로소프트사에 의해 만들어진, 데이터베이스에 접근하기 위한 소프트웨어의 표준 규격으로, 각 데이터베이스의 차이는 ODBC 드라이버에 의해서 흡수되기 때문에 유저는 ODBC에 정해진 순서에 따라서 프로그램을 쓰면 접속처의 데이터베이스가 어떠한 데이터베이스 관리 시스템에 관리되고 있는지 의식할 필요 없이 접근할 수 있다.
즉, JDBC와 ODBC의 차이점은 한다면 JDBC의 경우는 자바와 데이터베이스와 연결해주는 것이고, ODBC는 윈도우용 어플을 데이터베이스와 연결시켜준다는 것이다. JDBC는 자바에서만 가능한 반면 ODBC는 자바, C, C++과 같이 언어에 상관없이 사용할 수 있다.
출처: http:\ery221.tistory.com
Window에서 ODBC연결하기(1)
- 해당 위치에 있는 파일을 실행합니다.
- 이것은 MySQL ODBC드라이버로 윈도우즈 기본 ODBC는 MSSQL용이라 설치를 해주셔야 합니다.
- 여러 버전이 나오긴 했습니다만, 한글 문제가 있을 수 있어서 구 버전으로 다운 받았습니다.
Window에서 ODBC연결하기(2)
- Window키를 누르시고 아래와 같이 ODCB를 치시면 다음과 같은 목록이 나옵니다.
- ODBC를 실행해주세요
Window에서 ODBC연결하기(3)
- 해당 항목을 실행하시면 다음과 같은 창이 뜨게 됩니다.
Window에서 ODBC연결하기(3)
- MySQL ODBC 3.51 Driver를 선택해주시면 됩니다.
Window에서 ODBC연결하기(4)
- 그럼 다음과 같은 창이 뜨게 되는데, Data Source Name 같은 경우는 나중에 R에서 사용하게 되는 dsn, 즉 channel 이름이므로 기억해 주세요.
R에서 RODBC를 이용해 DB에 접속해보기
- 이제 R에서 MySQL에 접속해보겠습니다.
- 다음은 필요한 패키지들입니다.
take<-function (x)
{
for (i in x) {
if (!is.element(i, .packages(all.available = TRUE))) {
install.packages(i)
}
library(i, character.only = TRUE)
}
}
take(c("DBI","RODBC"))
- take는 함수로 package가 있는지 확인한 후에 콜링하는 함수입니다.
- 저희는 이번에
DBI패키지와 RODBC 패키지를 사용할 것입니다.
R에서 RODBC를 이용해 DB에 접속해보기(2)
- 앞서 말씀드렸던 data source name으로 접속해보겠습니다.
# ODBC접속하기
con_maria <- odbcConnect(dsn="test")
#쿼리 저장
sql_maria = "select * from countries"
#쿼리 실행
sqlQuery(con_maria,sql_maria)
## CountryISOCode CountryName RegionID
## 1 AR Argentina 2
## 2 AU Australia 3
## 3 BE Belgium 1
## 4 BR Brazil 2
## 5 CA Canada 2
## 6 CH Switzerland 1
## 7 CN China 3
## 8 DE Germany 1
## 9 DK Denmark 1
## 10 EG Egypt 4
## 11 FR France 1
## 12 HK HongKong 3
## 13 IL Israel 4
## 14 IN India 3
## 15 IT Italy 1
## 16 JP Japan 3
## 17 KW Kuwait 4
## 18 MX Mexico 2
## 19 NG Nigeria 4
## 20 NL Netherlands 1
## 21 SG Singapore 3
## 22 UK United Kingdom 1
## 23 US United States of America 2
## 24 ZM Zambia 4
## 25 ZW Zimbabwe 4
jobs <- sqlQuery(con_maria,"select * from jobs")
print(jobs)
## JobCode JobTitle MinSalary MaxSalary
## 1 AC_ACCOUNT Public Accountant 4200 9000
## 2 AC_MGR Accounting Manager 8200 16000
## 3 AD_ASST Administration Assistant 3000 6000
## 4 AD_PRES President 20000 40000
## 5 AD_VP Administration Vice President 15000 30000
## 6 FI_ACCOUNT Accountant 4200 9000
## 7 FI_MGR Finance Manager 8200 16000
## 8 HR_REP Human Resources Representative 4000 9000
## 9 IT_PROG Programmer 4000 10000
## 10 MK_MAN Marketing Manager 9000 15000
## 11 MK_REP Marketing Representative 4000 9000
## 12 PR_REP Public Relations Representative 4500 10500
## 13 PU_CLERK Purchasing Clerk 2500 5500
## 14 PU_MAN Purchasing Manager 8000 15000
## 15 SA_MAN Sales Manager 10000 20000
## 16 SA_REP Sales Representative 6000 12000
## 17 SH_CLERK Shipping Clerk 2500 5500
## 18 ST_CLERK Stock Clerk 2000 5000
## 19 ST_MAN Stock Manager 5500 8500
R에서 RMySQL과 dplyr패키지를 이용해 DB에 접속해보기(1)
- R에서
RODBC 말고 RMySQL 과dplyr을 이용해서 접속하는 방법도 있습니다.
- 테스트 해본 결과, ODBC가 필요가 없는 것 같습니다.
- 다음은 접속 방법입니다.
take(c("RMySQL","dplyr"))
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
con = src_mysql(DBNAME,localhost,port,ID,PW)
R에서 RMySQL과 dplyr패키지를 이용해 DB에 접속해보기(2)
- RODBC와 같은 결과가 나오도록 해보겠습니다.
- 다만 직접 쿼리를 날리지 않고, dplyr을 사용해서 가져오겠습니다.
jobs = con %>% tbl("jobs") %>% as.data.frame()
print(jobs)
## JobCode JobTitle MinSalary MaxSalary
## 1 AC_ACCOUNT Public Accountant 4200 9000
## 2 AC_MGR Accounting Manager 8200 16000
## 3 AD_ASST Administration Assistant 3000 6000
## 4 AD_PRES President 20000 40000
## 5 AD_VP Administration Vice President 15000 30000
## 6 FI_ACCOUNT Accountant 4200 9000
## 7 FI_MGR Finance Manager 8200 16000
## 8 HR_REP Human Resources Representative 4000 9000
## 9 IT_PROG Programmer 4000 10000
## 10 MK_MAN Marketing Manager 9000 15000
## 11 MK_REP Marketing Representative 4000 9000
## 12 PR_REP Public Relations Representative 4500 10500
## 13 PU_CLERK Purchasing Clerk 2500 5500
## 14 PU_MAN Purchasing Manager 8000 15000
## 15 SA_MAN Sales Manager 10000 20000
## 16 SA_REP Sales Representative 6000 12000
## 17 SH_CLERK Shipping Clerk 2500 5500
## 18 ST_CLERK Stock Clerk 2000 5000
## 19 ST_MAN Stock Manager 5500 8500
R에서 RMySQL과 dplyr패키지를 이용해 DB에 접속해보기(3)
data <- con %>% tbl('jobs') %>% select(JobCode,MinSalary,MaxSalary) %>% arrange(desc(MaxSalary))%>% as.data.frame()
print(data)
## JobCode MinSalary MaxSalary
## 1 AD_PRES 20000 40000
## 2 AD_VP 15000 30000
## 3 SA_MAN 10000 20000
## 4 AC_MGR 8200 16000
## 5 FI_MGR 8200 16000
## 6 PU_MAN 8000 15000
## 7 MK_MAN 9000 15000
## 8 SA_REP 6000 12000
## 9 PR_REP 4500 10500
## 10 IT_PROG 4000 10000
## 11 MK_REP 4000 9000
## 12 AC_ACCOUNT 4200 9000
## 13 HR_REP 4000 9000
## 14 FI_ACCOUNT 4200 9000
## 15 ST_MAN 5500 8500
## 16 AD_ASST 3000 6000
## 17 PU_CLERK 2500 5500
## 18 SH_CLERK 2500 5500
## 19 ST_CLERK 2000 5000
