R에서 RDBMS와 연결하는 3가지 방법

안녕하세요. 이제 2018년도 이틀밖에 남지 않았네요. 오늘은 R에서 관계형 데이터베이스 서버와 연결하는 3가지 방법에 대해 설명드리고자 합니다.

R을 사용하여 데이터 처리를 할 때는 이를 보관할 보조 기억 장소가 필요합니다. 물론 R에서 제공하는 RDS 포맷의 파일 형식이 있지만 이는 싱글 형태로 사용하거나 혹은 R 내에서만 사용할 수 있는 아주 제한적인 요소를 담고 있습니다.

그에 반해 관계형 데이터베이스에 보관하게 되면 다른 프로그래밍 언어에서도 자유로이 호환이 제공되고 여러 사람이 공유해서 사용할 수가 있죠. 오늘은 R에서 처리한 데이터를 RDBMS에 어떻게 insert하고 read할 수 있는지에 대해 알아보도록 하겠습니다.

R에서 제공하는 DB Connector

R에서 Database와 연결해주는 패키지는 상당수 존재합니다. 하지만 처음 사용하는 개발자 혹은 분석가 입장에서 어떤 패키지가 효율적이고 나에게 맞는 패키지인지는 사실 잘 모릅니다.

그래서 먼저 R에서 데이터베이스와 연결할 수 있는 패키지를 나열해보겠습니다.

DBI
RJDBC
DatabaseConnector

물론 여러분들이 사용하는 데이터베이스 미들웨어가 어떤 것이냐에 따라 RSQLite 등 부가적인 패키지를 설치해야 할 수도 있을 것입니다. 하지만 저는 이러한 부가적인 패키지 설치 없이 가능한한 개발자의 입장에서 보편적으로 사용되는 ODBC, JDBC 드라이버의 연결 방법에 대해 설명하고, 또 이의 드라이버를 맞춰 제공해주는 대표적인 패키지에 대해서 추가적으로 이야기해보며 이 글을 마치도록 하겠습니다.

DBI 패키지를 사용한 연결

먼저 DBI 패키지를 보도록 하겠습니다. DBI는 Perl 개발자라면 잘 알고 있는 Perl의 데이터베이스 연결 형식입니다. 그의 이름을 따서 R에서 제공하는 DBI 패키지는 r-dbi라는 이름을 가지고 있습니다.

1	install.packages('DBI')

먼저 공개된 DB 소프트웨어에 한하여 아래와 같은 방법이 존재합니다.

install.packages('RMySQL')			# MySQL/MariaDB를 사용하는 경우.
install.packages('RpostgreSQL')			# PostgreSQL을 사용하는 경우.
install.packages('RSQLite')			# SQLite를 사용하는 경우.

drv <- DBI::dbDriver('MySQL')			# 자신이 사용하고자 하는 DBMS 이름 입력.
dbname <- 'database'
user <- 'user'
password <- 'password'

con <- DBI::dbConnect(drv = drv, dbname = dbname, user = user, password = password)

DBI 패키지에서 제공하는 dbDriver 함수를 사용해 앞에 R이 붙은 혹은 붙지 않은 DBMS의 패키지를 검색하여 드라이버를 자동으로 찾아 연결해줍니다.

그렇다면 오픈 소스의 공개된 DBMS 외에 다른 DBMS의 연결은 어떻게 할까요?

이 때는 R에서 제공하는 odbc 패키지를 사용하여 DBI 패키지와 연동하여야 합니다.

# ODBC
con <- DBI::dbConnect(odbc::odbc(),
                      Driver = "[Driver name]",
                      Host = "[Server name or hostname]",
                      SVC = "[Schema name]",
                      UID = "[Username]",
                      PWD = "[Password]",
                      Port = "[PORT number]")

ODBC는 Microsoft의 ODBC 기술을 말하며 현재는 리눅스, 윈도우를 포함하여 어떤 운영체제에서든지 드라이버만 있다면 자유롭게 사용할 수 있는 DB 연결 방법입니다.

각 운영체제별로 ODBC 드라이버를 설치하여 사용하는 방법이 존재합니다. 사실상 설정이 매우 까다롭고 드라이버가 없으면 연결할 수 없기 때문에 사전에 이 설정을 해주는 것이 매우 중요합니다.

리눅스에서 unixODBC 사용하는 방법

위 글을 참고하여 ODBC를 먼저 설정해주는 작업이 필요합니다. 설정 작업을 먼저 진행한 후, DBI에서 ODBC를 사용하는 방법을 계속 진행해주세요.

설정이 완료되었다면, ODBC를 구성할 수 있는 방법이 2가지 방법이 존재한다는 것을 아셨을 것입니다. 1번쨰 방법은 위의 코드처럼 직접 odbc.ini의 내용을 코드에 담는 방법입니다. 대신 사전에 미리 odbcinst.ini 파일에 드라이버의 정보가 입력되어있는 상태여야겠죠?

1	con <- DBI::dbConnect(odbc::odbc(), "DSN name")

2번째 방법은 odbc.ini에 직접 서버 정보를 입력시켜놓고, 프로그래밍 코드에서는 DSN 이름만을 입력하여 연결하는 방법입니다. 이 방법을 사용하면 좀 더 서버 정보 유출을 방지할 수 있는 시큐어 코드를 생성할 수 있습니다.

1 2	# read table df <- DBI::dbReadTable(con, "tableName")

con 변수는 위에서 사용한 연결 객체입니다. 해당 연결 객체가 제대로 연결되어 있는 상태이어야 하며 연결되지 않은 경우 함수는 동작하지 않습니다. 그 다음 인자에 테이블 이름을 넣어주시면 됩니다.

그럼 이제 query를 사용하여 나온 결과를 R에서 받아보도록 하겠습니다.

1
2
3

# Use custom query
query <- "SELECT * FROM tableName where tbC > 10"
df <- DBI::dbGetQuery(con, query)

사용자 정의된 쿼리에서 자신이 원하는 부분만 가져오고 싶을 때는 dbGetQuery 메소드를 사용하여 가져올 수 있습니다.

1
2
3

# Use write table
df <- data.frame()
res <- DBI::dbWriteTable(conn = con, value = df, name = "tableName")

dbWriteTable 함수를 사용하여 원하는 테이블 이름을 입력하고 넣고자 하는 값이 들어간 데이터 프레임 형태를 넣으면 해당 DBMS로 테이블이 들어갑니다.

RJDBC를 사용한 연결

RJDBC는 Java의 데이터베이스 연결 방식인 JDBC를 R에서 사용할 수 있도록 만든 패키지입니다. 이와 비슷한 이름으로 RODBC 패키지도 존재하지만 ODBC는 DBI 패키지에서 사용할 수 있는 방법이 있으므로 바로 RJDBC를 사용하는 방법으로 넘어가보겠습니다.

RJDBC는 Java 코드를 사용하기 때문에 반드시 OS에 Java가 설치되어 있어야 하며 추가로 R에서 Java 코드를 사용하도록 해주는 rJava 패키지도 추가로 설치해줘야 합니다.

1	install.packages(c('rJava', 'RJDBC', 'JDBC'))

복수개의 패키지를 설치할 때는 R에서 제공하는 Vector 자료구조를 사용할 수 있습니다.

RJDBC는 공개형 데이터베이스든 상용 데이터베이스든 각 DBMS에 맞는 JDBC 드라이버를 가지고 있어야 합니다.

이 외에도 많은 DBMS들이 존재하지만 그나마 자주 사용되는 JDBC들만 모아봤습니다. 추가로 자신이 사용하는 JDBC에 대해서는 DBMS 공식 홈페이지에서 구할 수 있습니다.

# include JDBC
# example SQL Server
drv <- JDBC::JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver", "driverPath")
con <- RJDBC::dbConnect(drv, "jdbc:sqlserver://127.0.0.1;databaseName=dbName")

저는 예제로 Microsoft SQL Server를 연결하는 방법에 대해 적었습니다. JDBC는 JDBC의 프로토콜 주소 형식이 존재하기 때문에 dbConnect 함수에서 JDBC 주소 형태로 데이터베이스 이름과 호스트이름을 설정할 수 있습니다.

1 2	# read table df <- RJDBC::dbReadTable(conn = con, name = "tableName")

DBI 패키지와 동일하게 RJDBC 패키지에서도 같은 함수를 사용하여 table 이름을 정의하고 해당 테이블의 내용을 가지고 올 수 있습니다.

1
2
3

# Use custom query
query <- "SELECT * FROM tableName where tbC > 10"
df <- RJDBC::dbGetQuery(con, query)

Query를 사용하는 것도 DBI 패키지와 동일합니다.

1
2
3

# Use write table
df <- data.frame()
res <- RJDBC::dbWriteTable(conn = con, value = df, name = "tableName")

WriteTable도 동일하게 이루어집니다.

DatabaseConnector를 사용한 연결

DatabaseConnector 패키지는 OHDSI (Observational Health Data Sciences and Informatics) 라는 기관에서 개발한 R 언어용 데이터베이스 연결 패키지입니다.

1	install.packages(c('DatabaseConnector', 'rJava'))

이 패키지 또한 JDBC를 사용하기 때문에 rJava 패키지와 같이 설치해주어야 합니다. 어? 그렇다면 RJDBC랑 별반 다를 게 없잖아요?

이 패키지는 JDBC 드라이버를 사용하지만 개발자나 분석가가 일부 데이터베이스 소프트웨어에 한하여 직접 JDBC 드라이버 다운로드 받아 설치하지 않아도 된다는 장점이 있습니다. 이 패키지는 해당 기관에서 사용하기 위해 개발되어 공식적으로 PostgreSQL, Oracle, Microsoft SQL Server, Impala, RedShift의 JDBC 드라이버를 제공합니다.

https://github.com/OHDSI/DatabaseConnector

따라서 위 명시된 데이터베이스에 한해서는 여러분들이 JDBC 드라이버를 별도로 다운로드 받거나 경로를 지정해주지 않아도 됩니다. 물론 RJDBC와 마찬가지로 직접 다운로드 받아 사용할 수도 있습니다. JDBC와 다른 점이 있다면 insert, batch 작업 등이 직접 Java 코드와 연동하여 사용된다는 점입니다.

dbms <- 'sql server'		# I want using microsoft sql server
user <- 'username'
pw <- 'password'
host <- '127.0.0.1'

details <- DatabaseConnector::createConnectionDetails(
    dbms = dbms,
    user = user,
    password = pw,
    server = host
)

con <- DatabaseConnector::connect(details)

연결하는 방법은 위에서 사용한 RJDBC나 DBI와 동일합니다. 다만 details라는 객체가 별도로 존재하여 해당 객체에 connection을 붙인다는 점의 프로그래밍적인 내용은 조금 차이가 있습니다.

1 2	# reate table df <- DatabaseConnector::dbReadTable(conn = con, name = 'tableName')

1
2
3

# custom query
query <- "SELECT * FROM tableName where tbC > 10"
df <- DatabaseConnector::dbGetQuery(conn = con, statement = query)

ReadTable이나 Query를 동작하는 것 또한 다른 패키지와 다르지 않습니다.

1
2
3

# write table
df <- data.frame()
res <- DatabaseConnector::dbWriteTable(conn = con, value = df, name = 'tableName')

WriteTable도 동일하게 진행되는데, DatabaseConnector에서는 이 외에 좀 더 새로운 기능이 존재합니다.

# insert table
df <- data.frame()
res <- DatabaseConnector::insertTable(
    connection = con,
    tableName = 'tableName',
    data = df,
    dropTableIfExists = FALSE,
    createTable = TRUE,
    progressBar = TRUE
)

WriteTable은 기존 테이블에 새로운 데이터를 넣는 작업이라면, insertTable은 현재 존재하지 않는 새로운 테이블을 새로 삽입하는 함수입니다. 병렬 처리를 지원하며 큰 데이터를 넣는 데도 문제가 없었습니다.

다만 몇 가지 아쉬운 점이 있다면, PRIMARY KEY나 NOT NULL 등의 속성에 대해서는 별도의 executeQuery 함수를 써서 진행하여야 한다는 점입니다.

마치며…

여기까지 R언어에서 DBMS를 연결할 수 있는 방법에 대해 알아봤습니다. 이 외에도 여러가지 방법이 있지만 많은 방법 중에서도 가장 보편적이고 사용하기 쉬워보이는 패키지들만 모아봤습니다. Python, Java에서도 Database를 연결하는 방법은 여러가지 존재하지만 R에서는 Data frame 등의 큰 자료 구조를 지원하면서 데이터베이스와의 연결은 필수일 것입니다.

다음 글에서는 R 언어에서 반복문을 병렬 처리하는 방법에 대해 포스트하도록 하겠습니다.