[R] 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를 실행해주세요

windows

Window에서 ODBC연결하기(3)

  • 해당 항목을 실행하시면 다음과 같은 창이 뜨게 됩니다.

odbc1

  • 추가를 실행합니다.

Window에서 ODBC연결하기(3)

  • MySQL ODBC 3.51 Driver를 선택해주시면 됩니다.

odbc2

Window에서 ODBC연결하기(4)

  • 그럼 다음과 같은 창이 뜨게 되는데, Data Source Name 같은 경우는 나중에 R에서 사용하게 되는 dsn, 즉 channel 이름이므로 기억해 주세요.

odbc3

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)

  • 이제 적당한 쿼리를 실행해보겠습니다
  • 실행할 쿼리는 다음과 같습니다
    select JobCode,MinSalary,MaxSalary
    from jobs
    order by MaxSalary desc;
 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

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다