5 장 R에서 MySQL데이터의 접근방법

DBI (R Database Interface)
RODBC, odbc

5.1 DBI를 이용한 데이터베이스 접근

DBI

R 과 DBMS를 연결하는 인터페이스 역할을 하는 R 패키지로 DBI는 대략 30개의 DBMS 지원하며 다음은 주요 DBMS와 backend R패키지임

MySQL (RMySQL)
MariaDB (RMariaDB)
Postgres (RPostgres)
SQLite (RSQLite)

DBI classes
1. connect/disconnect to the DBMS
2. create and execute statements in the DBMS
3. extract results/output from statements
4. error/exception handling
5. information (meta-data) from database objects
6. transaction management (optional)

5.1.1 DBI 함수

함수	기능설명
dbConnect()	DBMS 연결, 결과 연결정보(DBIConnection)가 생성됨
dbDisconnect(conn, …)	DBMS 연결 종료
dbGetQuery(conn, statement)	statement에 SQL을 입력, 결과는 데이터프레임
dbSendQuery(conn, statement)	statement에 SQL을 입력, 결과 class는 `DBIResult`
dbFetch(res, n), fetch(res, n)	dbSendQuery()의 결과에서 n개의 레코드를 가져와 데이터프레임을 만듦
dbClearResult(res)	dbGetQuery의 결과로 생성된 리소스가 점유하는 메모리 해제
dbListTables(conn)	연결된 DBMS의 테이블 목록
dbListFields(conn, name)	테이블(name)의 컬럼정보 조회
dbColumnInfo(res,…)	dbGetQuery의 결과 테이블의 컬럼 정보
dbReadTable(conn, name)	테이블(name)에서 데이터프레임 생성
dbWriteTable(conn, name, value)	데이터프레임(value)를 DB 테이블(name)에 저장
dbRemoveTable(con, name)	테이블(name) 삭제

5.1.2 DBI 예제: SQLite

# DB connect
library(DBI)
con1 = dbConnect(RSQLite::SQLite(), dbname = ":memory:")
con2 = dbConnect(RSQLite::SQLite(), dbname = "SQLite/test.sqlite")

# 테이블 목록 조회: `dbListTables()`
dbListTables(con2)
# character(0)

# 데이터프레임을 DB테이블에 복사
dbWriteTable(con2, "mtcars", mtcars)
dbListTables(con2)
# [1] "mtcars"
# 테이블의 컬럼 정보 조회
dbListFields(con2, "mtcars")
# [1] "mpg"  "cyl"  "disp" "hp"   "drat" "wt"   "qsec" "vs"   "am"   "gear" "carb"

# 테이블을 R데이터프레임으로 가져오기: `dbReadTable()`
df1 = dbReadTable(con2, "mtcars")

# SQL을 이용한 검색: `dbGetQuery()`
df2 = dbGetQuery(con2, "SELECT * FROM mtcars WHERE cyl = 4")
head(df2)
#    mpg cyl  disp hp drat    wt  qsec vs am gear carb
# 1 22.8   4 108.0 93 3.85 2.320 18.61  1  1    4    1
# 2 24.4   4 146.7 62 3.69 3.190 20.00  1  0    4    2
# 3 22.8   4 140.8 95 3.92 3.150 22.90  1  0    4    2
# 4 32.4   4  78.7 66 4.08 2.200 19.47  1  1    4    1
# 5 30.4   4  75.7 52 4.93 1.615 18.52  1  1    4    2
# 6 33.9   4  71.1 65 4.22 1.835 19.90  1  1    4    1

# SQL을 이용한 검색: `dbSendQuery() + dbFetch()`
res <- dbSendQuery(con2, "SELECT * FROM mtcars WHERE cyl = 4")
df <- dbFetch(res, n=3)
df
#    mpg cyl  disp hp drat   wt  qsec vs am gear carb
# 1 22.8   4 108.0 93 3.85 2.32 18.61  1  1    4    1
# 2 24.4   4 146.7 62 3.69 3.19 20.00  1  0    4    2
# 3 22.8   4 140.8 95 3.92 3.15 22.90  1  0    4    2

# dbSendQuery의 결과를 삭제
dbClearResult(res) 

# close the DB connection: `dbDisconnect()`
dbDisconnect(con2)

5.1.3 DBI 예제: RMySQL

library(RMySQL)
# DB connect
con = dbConnect(
  RMySQL::MySQL(), # DBMS backend driver (MySQL Driver)
  dbname = "jskim", # 접속 DB명
  username = "jskim1", # 접속ID
  password = "*******", # 접속암호
  host = "localhost",
  port = 3306
)

dbGetInfo(con) # 접속 정보 출력
# $host
# [1] "localhost"
# 
# $user
# [1] "jskim1"
# $dbname
# [1] "jskim"
# $conType
# [1] "Localhost via UNIX socket"
# $serverVersion
# [1] "5.7.39-0ubuntu0.18.04.2"
# $protocolVersion
# [1] 10
# $threadId
# [1] 502
# $rsId
# list()

#. 테이블 목록 조회: `dbListTables()`
dbListTables(con)
#  [1] "I1"        "P1"        "baseball"  "code"      "customers" "income"   
#  [7] "items"     "orders"    "pet"       "pop"       "t1"        "t2"       
# [13] "tab1"      "tab2" 

# 테이블 목록 조회: `dbListTables(..., append, overwrite, row.names = FALSE)`
dbWriteTable(con, "mtcars", mtcars, overwrite  = T, row.names = FALSE)
dbListTables(con)
 # [1] "I1"        "P1" ...   "items"     "mtcars"   
 # [9] ...   "tab1"      "tab2"    

# 한글처리
dbSendQuery(con, "SET NAMES utf8;") 
dbSendQuery(con, "SET CHARACTER SET utf8;") 
dbSendQuery(con, "SET character_set_connection=utf8;") 

# 테이블의 컬럼 정보 조회: `dbListFields()`
dbListFields(con, "pet")
# [1] "name"    "owner"   "species" "sex"     "birth"   "death"  

# 테이블을 R데이터프레임으로 가져오기: `dbReadTable()`
pet = dbReadTable(con, "pet")
head(pet)
#     name  owner species sex      birth      death
# 1 Bowser Dianne     dog   m 1998-08-31 1995-07-29
# 2  Buffy Harold     dog   f 1989-05-13       <NA>
# 3 Chirpy   Gwen    bird   f 1998-09-11       <NA>
# 4  Claws   Gwen     cat   m 1994-03-17       <NA>
# 5   Fang  Benny     dog   m 1990-08-27       <NA>
# 6 Fluffy Harold     cat   f 1993-02-04       <NA>

# SQL을 이용한 검색: `dbGetQuery()`
df <- dbGetQuery(con, "SELECT 회원등급,2012구매건수, 2012구매금액, addr1 FROM customers")
head(df)
#      회원등급 2012구매건수 2012구매금액 addr1
# 1  다이아몬드           87      5106270  경기
# 2        일반            5       206970  대전
# 3        골드            7       108340  서울
# 4        골드           10       359700  전북
# 5        골드            6       192490  경기
# 6  다이아몬드          132      5246980  경북

# SQL을 이용한 검색
res <- dbSendQuery(con, 
          "SELECT pid AS 상품ID, SUBSTRING(pname, 1, 10) AS 상품명 FROM items")
dbColumnInfo(res)
# <MySQLResult:1305731584,0,20>

df1 <- dbFetch(res, n=10) # n=10 (처음부터 10개의 레코드를 불러옴)
df1
#    상품ID              상품명
# 1       1 필립스아벤트 아벤트
# 2       2      2012년 하기스 
# 3       3      2012년 하기스 
# 4       4     2012년 하기스매
# 5       5     2012년 하기스매
# 6       6   리콜 후 새로나온 
# 7       7     2010년형 바운서
# 8       8 최저가 강화플라스틱
# 9       9     KPSKC자율안전인
# 10     10         Lansinoh 란

df2 <- dbFetch(res, n=10) # n=10 (앞에서 읽은 레코드 이후 다시 10개의 레코드를 불러옴)
df2
#    상품ID          상품명
# 1      11      Nestle sti
# 2      12      Swaddle Me
# 3      13  1300K 안전용품
# 4      14 2013년형 하기스
# 5      15 2013년형 하기스
# 6      16 2013년형 하기스
# 7      17 2013년형 하기스
# 8      18 2013년형 하기스
# 9      19 2013년형 하기스
# 10     20 2013년형 하기스
dbHasCompleted(res) # 테이블의 마지막 레코드까지 읽었는지 확인
# [1] FALSE

dbClearResult(res) # dbSendQuery의 결과를 삭제
# [1] TRUE

# close the DB connection: `dbDisconnect()`
dbDisconnect(con)
# [1] TRUE

5.2 ODBC를 이용한 데이터베이스 접속

ODBC (Open DataBase Connectivity)

마이크로소프트가 만든 데이터베이스에 접근하기 위한 소프트웨어의 표준 규격

사용자는 소스 데이터의 DBMS에 상관없이 ODBC에 정해진 순서에 따라 데이터에 접근
윈도우즈에는 기본적으로 OS를 설치할 때 함께 ODBC가 기본 설치
ODBC는 데이터베이스 뿐만 아니라 엑셀파일의 접근에도 이용

RODBC

RODBC는 R에서 ODBC를 이용하여 데이터베이스에 접근할 수 있도록 Michael Lapsley와 Brian Ripley가 만든 R패키지, R-Studio에서 만든 유사 패키지로 odbc가 있음

5.2.1 RODBC 함수

RODBC함수	DBI함수	기능설명
odbcConnect()	dbConnect()	DBMS 연결, 결과 연결정보(DBIConnection)가 생성됨
odbcClose(channel), odbcCloseAll()	dbDisconnect(conn, …)	DBMS 연결 종료
sqlGquey(channel, query,…)	dbGetQuery(conn, statement)	statement에 SQL을 입력, 결과는 데이터프레임
	dbSendQuery(conn, statement)	statement에 SQL을 입력, 결과 class는 `DBIResult`
sqlFetch(channel, sqtable,…)	dbFetch(res, n), fetch(res, n)	dbSendQuery()의 결과에서 n개의 레코드를 가져와 데이터프레임을 만듦
	dbClearResult(res)	dbGetQuery의 결과로 생성된 리소스가 점유하는 메모리 해제
sqlTables(channel,…)	dbListTables(conn)	연결된 DBMS의 테이블 목록
	dbListFields(conn, name)	테이블(name)의 컬럼정보 조회
	dbColumnInfo(res,…)	dbGetQuery의 결과 테이블의 컬럼 정보
	dbReadTable(conn, name)	테이블(name)에서 데이터프레임 생성
sqlSave(channel, dat, tablename = NULL,…)	dbWriteTable(conn, name, value)	데이터프레임(value)를 DB 테이블(name)에 저장
sqlDrop(channel, sqtable,)	dbRemoveTable(con, name)	테이블(name) 삭제

5.2.2 RODBC 설치

ODBC connector설치 환경
1. Windows 10 Home (64bit)
2. R version 4.0.2 (2020-06-22): R consol에서 R.version.string을 치면 나옴
3. MySQL: 8.0.21 MySQL Community Server - GPL
MySQL ODBC 드라이버의 설치
1. Windows
  - 다운로드: https://dev.mysql.com/downloads/connector/odbc/ : 다운로드
  - Maria DB: https://downloads.mariadb.org/connector-c/3.1.10/
  - ODBC connector 다운로드
2. Ubuntu
  1. 다운로드 및 압축해제: https://downloads.mysql.com/archives/c-odbc/
  2. lib에 있는 라이브러리 파일들(libmyodbc5w.so)을 /usr/lib/x86_64-linux-gnu/odbc/에 복사
  3. 기타 설치에 필요한 프로그램들 찾아서 설치
ODBC connector/ODBC설치 (윈도우즈 환경)
1. 설치 프로그램 시작
2. 라이센스 동의/셋업환경 설정
3. 설치 시작/완료
MySQL DSN(data set name)설정
1. 윈도우즈의 검색창에서 [ODBC데이터 원본 관리자] 탐색 후 실행
2. [ODBC데이터 원본 관리자]의 [시스템DSN] 탭 선택 후 [추가] 버튼 클릭
3. 데이터 원본을 설정할 드라이버 선택 (MySQL …)
4. 데이터 원본 주요 항목 설정

ODBC를 이용한 MySQL 서버 접속

R command

# install.packages("odbc")
library("DBI")
# MySQL 서버 접속
channel <- dbConnect(odbc::odbc(), 'mysql_unicode', database="jskim", uid='****', pwd='*****')
# 접속 종료
# dbDisconnect(channel) # ODBC connection 종료

R Studio GUI