데이터프레임 불러오기 2 - 데이터베이스에서 직접 불러오기: RODBC

 

이전 글에서 데이터프레임 또는 데이터세트를 불러오는 방벙에 대해서 다루었다. 가장 기본적인 CSV파일을 직접 하나하나 불러오는 방법, CSV파일이 들어있는 폴더를 한꺼번에 불러와서 하나의 리스트에 넣어놓고 사용하는 방법, R이 기본적으로 제공하고 있는 데이터세트를 사용하는 방법 등을 Sean Lahman의 Baseball Database를 예로 설명하였다. 

https://double-d.tistory.com/27

R: 데이터프레임 불러오기 1 (CSV파일, R기본 데이터세트)

 

이번 글에서는 데이터베이스를 통해서 불러오는 방법을 알아보도록 하자. 논문을 쓰거나, 학습하는 목적이 아니고 회사에서 데이터분석 업무를 맡게 되었다고 가정하자. 대부분의 경우 회사의 데이터베이스를 통해 직접 대용량 데이터세트를 가져와서 분석해야 할 것이다. 데이터의 크기가 클 경우 엑셀은 물론이고 CSV 파일조차도 따로 저장했다가 불러와서 사용하면 에러가 발생하기도 한다. 개인적인 경험으로도 1백만 개 이상의 기록에 대해서 CSV파일로 저장하면 점점 버벅거리기 시작하면서 2~3백만 개가 넘어가면 어디에선가 에러가 발생한다. SQL 편집기를 사용해서 파일을 받는 데에는 큰 문제가 없는데, 파일을 저장했다가 다시 R로 불러오는 과정에서 문제가 생기는 것이다. 

시간을 줄이고, R Studio에서 한꺼번에 처리할 수 있도록 해주는 R Package들이 있다. 해당 패키지들에 대해서 간단히 소개해 보려고 한다. 

 

RODBC 패키지를 통해 불러오기

우선 ODBC(Open Database Connectivity) 드라이버가 있어야 사용할 수 있다. 회사 PC에 대부분 기본으로 설치되어 있고, 설정도 완료되어 있을 가능성이 크다. ODBC는 1992년에 마이크로소프트와 협력관계에 있는 회사가 개발하였다. 이런 이유로 윈도우에서는 쉽게 사용 가능하지만, 맥에서는 조금 더 번거로운 방법으로 사용하게 된다. 

ODBC 드라이버 설치하기

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16

Install the Microsoft ODBC driver for SQL Server (macOS) - ODBC Driver for SQL Server

혹시 ODBC드라이버가 설치되어 있지 않다면, 마이크로소프트의 공식 웹사이트에 가보자. 위의 마이크로소프트 웹사이트에 가면 맥에서 ODBC드라이버를 설치하는 방법이 나온다. ODBC 드라이버가 설치되면, Microsoft SQL Server Management Studio에서 해당 드라이버를 연결하여 회사의 DB에 접속할 수 있다.

RODBC 패키지 설치하기

ODBC 드라이버가 설치되었으면, 'RODBC'패키지를 설치하자. install.packages() 함수로 해당 패키지를 먼저 설치하고, library() 함수를 통해 R Studio의 현재 프로젝트에 해당 패키지를 Load 해주면 된다. 주의할 점은, 설치할 때의  패키지 이름은 큰따옴표가 반드시 있어야 한다. library()의 경우 있어도 되고, 없어도 된다.

 

install.packages("RODBC")
library(RODBC)

 

R Studio에서 SQL 서버 연결하기

패키지의 설치 및  loading이 끝났으면 odbcConnect() 함수를 통해 바로 연결해 볼 수 있다. 여기서 부터는 회사 IT부서의 도움이 필요할 수 있다. 각 회사마다 직원들이 접근할 수 있도록 해둔 서버 인스턴스가 다를 수 있으니, 인스턴스의 이름이 무엇인지 확인해 보자. ODBC 드라이버가 이미 설치되어 있다면 ODBC 드라이버 설정에 들어가면 확인해 볼 수 있다. 또한, 회사의 IT정책에 따라 ID와 Password가 필요한 경우도 있으니 별도로 확인해야 할 수도 있다. 아래는 서버 인스턴스 이름이 'instance_name'인 서버에 ID와 Password 별도 입력 없이 접속하는 경우이다. RODBC패키지에서는 접속 환경 자체를 'con'이라는 항목에 저장해 놓고, 다른 함수들에서 'con'을 불러와 추가적인 명령을 하여 사용한다.   

#MS SQL Server
con <- odbcConnect("instance_name", uid ="", pwd = "" )

만약 사용하고 있는 SQL 서버 서비스가 PostgreSQL이나 MySQL일 경우, 접속하는 방법이 약간 다를 수 있다.

# Source: R- Cran, RODBC package pdf

# MySQL
channel <- odbcConnect("instance_name", uid="ripley", pwd="secret")
# PostgreSQL: 'case' should be detected automatically
channel <- odbcConnect("instance_name", uid="ripley", pwd="secret", case="postgresql")

서버에 연결이 잘 되었는지 확인하려면 다음의 함수를 실행해 보자. 정상적으로 연결이 되었다면 연결한 서버에 대한 정보가 나올 것이다. 만약 연결이 되어 있지 않다면 회사의 IT부서에 문의하자.

odbcGetInfo(con)
odbcDataSources(con)

 

RODBC 패키지를 통해 SQL 쿼리 실행하기

연결이 되었다면 이후의 사용 방법은 간단하다. 'sqlQuery()'함수에 원하는 SQL 쿼리를 같이 입력해주면 된다. 아래는 People이라는 데이터테이블을 가정하고, 상위 10개 기록만 불러와 'test_tbl'에 저장하는 상황이다. 이렇게 간단한 SQL 쿼리의 경우에 직접 입력해도 별 문제가 없다. 하지만 조금만 복잡해져도 실수하기 쉽다. SQL 편집기와는 달리 에러를 쉽게 발견할 수 없고, 쿼리 결과물을 보는 것도 조금 더 번거롭기 때문이다. 결과물이 제대로 나왔는지 확인하려면 'test_tbl'을 선택하고 ctrl+Enter 해봐야 데이터가 들어왔는지 알 수 있다. 

test_tbl <- sqlQuery(con, "SELECT TOP 10 * FROM People")

 

데이터베이스에 있는 데이터테이블 정보 확인하기

접속한 데이터베이스에 어떤 데이터 테이블이 있는지, 그 데이터 테이블에 어떤 항목들이 있는지 확인하려면 아래와 같은 함수를 사용해야 한다. SQL편집기보다는 조금 더 불편할 수 있다. 

# Checking Tables in connected Database
sqlTables(con)

# Checking Columns in Tables
sqlcolumns(con, "table_name")

 

RODBC관련 함수들 더 알아보기

아래 링크의 R cran에 가보면 RODBC 패키지에 대한 더 자세한 내용을 공식 문서를 통해 확인할 수 있다. 공식문서는 지속적으로 업데이트가 된다. 

https://cran.r-project.org/web/packages/RODBC/RODBC.pdf

 

한계점

RODBC와 같이 데이터베이스에 직접 접속해서 사용하는 경우 몇 가지 아쉬운 점이 있다. 이는 RODBC만의 문제가 아니라, 앞으로 다루게 될 DBI, RSQLite 패키지의 경우에도 마찬가지다.

SQL 쿼리문 작성 및 에러 디버깅 불편함

위에서도 간단히 언급했지만, SQL 쿼리문의 편집을 위해서 존재하는 SQL 편집기의 경우, 간단한 문법 오류는 작성하고 있는 중에 표시해 준다. 그리고 결과물에 대해서도 키보드 단축기 한 번으로 바로 확인할 수 있다. 에러가 있는 경우 어디에 에러가 있는지 위치도 표시해 주고, 왜 에러가 났는지에 대해서도 설명해 준다. 하지만 R에서 RODBC 패키지를 사용할 경우, 특히 'con'처럼 변수에 접속 환경을 넣어주는 경우는 추가 함수를 사용하기 전까지 쿼리문에 문제가 있는지 없는지도 발견하기 어려울 때가 있다. 

ERD(Entity Relation Diagram) 확인 불가

데이터베이스에 접속해서 SQL쿼리문을 작성할 때, 각 테이블에 어떤 항목들이 있고, 어떻게 연결이 되어 있는지 알아야 더 쉽게 작성할 수 있다. SQL 편집기들은 기본적으로 ERD를 보여주는 기능이 있는데, R에서는 datamodelr 패키지를 통해 별도로 작성하지 않는 한, 쉽게 보기 어렵다.

위의 이유로 항상 SQL 편집기를 같이 사용해야 한다는 불편함이 있다. 그럼에도 불고하고, 글의 서두에 언급했던 바와 같이, 별도의 파일을 중간에 만들어서 불러들여오는 시간을 절약하고, 에러 발생 가능성을 낮출 수 있기 때문에 기존에 작성된 SQL 쿼리문을 잘 활용하여 사용하는 장점을 포기할 수가 없다. 다루어야 하는 데이터에 따라 다르겠지만, 데이터의 양이 많다면 이 만한 좋은 방법이 없을 것이다.