R 속에서 SQL 사용하기

'SQL 왜 필요한가'를 통해서 간략하게 SQL을 알면 왜 좋은지 정리하였다. 대부분의 데이터베이스가 관계형 데이터베이스이기 때문에 SQL 쿼리를 통해서 데이터를 뽑아내는 것이 가장 효과적인 데이터 추출 방법 중 하나이다. 

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

SQL 왜 필요한가

 

당연하겠지만 데이터를 추출하고 난 후에는 CSV나 엑셀 형태로 받아서 다시 R로 불러오는 수고를 할 필요가 없다. R에 관련 패키지를 설치해서 필요한 함수를 사용하기만 하면, 바로 서버와 연결되어 쿼리를 사용할 수 있다. 

 

SQL 관련 패키지

세 가지 정도의 패키지가 있으며, 데이터베이스에 연결해서 쿼리를 사용하는 방법은 대부분 비슷하다. 

 

• 'odbc' package
  : 데이터베이스와의 연결을 위한 ODBC 드라이버용
• 'DBI' package 또는 'RODBC' package
  : 데이터베이스에 쿼리를 주고 받을 수 있도록 해주는 패키지
  
  ※ ODBC (Open Database Connectivity) : 데이터베이스 관리시스템에 접속하기 위한 API의 일종

위 패키지를 사용하여 쿼리를 이용하는 방법은 아래와 같다. 

 

패키지의 설치

install.package() 함수에 패키지 이름을 변수로 넣고 실행시키면 된다. 설치된 패키지는 library() 함수로 R Studio에 load한다. 여기서 주의할 점은, 패키기 설치를 위한 변숫값은 큰 따옴표 안에 패키지 이름을 넣어야 하고, Load를 위한 변숫값은 큰 따옴표에 넣어도 되고, 넣지 않아도 된다.

install.packages("odbc")
install.packages("DBI")
library(odbc)
library(DBI)

 

 

 

데이터베이스에 연결하기

 

 이 단계에서는 회사 IT팀의 지원이 필요할 수 있다. 회사에 따라 일반 직원이 접속할 수 있게 허용한 데이터베이스가 있을 수 있는데, 그 경우 대부분 ODBC 설정을 위한 매뉴얼이 있으니 문의해보면 된다. 'ODBC Data Source Admnistrator'라는 프로그램은 윈도우나 맥에 대부분 이미 설치되어 있으니, ODBC 설정법 및 로그인을 위한 정보가 필요한지 정도만 알면 쉽게 데이터베이스에 접속할 수 있다. 

 

'driver_name', 'server_url', 'databse_name', 로그인을 위한 id, password 등을 알면 아래와 같이 dbConnect()함수를 사용해서 데이터베이스에  연결할 수 있다.

참고로 'DBI::dbConnect()'는 DBI라는 패키지 안에서 제공하는 dbConnect()함수라는 뜻이다.  

con <- DBI::dbConnect(drv = odbc::odbc(),
	Driver = "driver_name",Server = "server_url",
   	 Database = "database_name",
   	 user = "user_id",
   	 password = "password")

 

연결된 후에는 'RSQL', 'RSQLite' 등의 패키지를 추가로 설치해서 데이터베이스에 추가로 테이블을 만들거나 수정할 수 있다. 본 글에서는 데이터 추출에만 집중하려 한다.

 

 

SQL 쿼리로 데이터 추출하기

 

쿼리를 R에 직접 입력하면 R Studio에서는 잘못된 문법으로 인식한다. 따라서 DBI 패키지가 제공하는 'dbGetQuery()' 함수 속에 변수로 쿼리를 넣어줌으로써 데이터를 추출해야 한다. 

 

아래는 예시이며, 실제 'test.table'이라는 데이터베이스 테이블이 없으므로, 아래 코딩은 설명용으로만 참조하자. 간단히 설명하자면, 'test'라는 이름을 가진 데이터 테이블에 'test.table'이라는 데이터 테이블에 있는 모든 항목들을 전부 불러서 저장해 넣으라는 의미이다.

 

dbGetQuery()함수 안에 있는 변수에는 특정값들이 입력되어야 한다. 'conn' 변수는 위에 ODBC를 통해 연결한 데이터 베이스와의 연결 정보가 있는 'con'을 입력해주고, 'statement'에는 원하는 SQL 쿼리를 그대로 입력해주면 된다. 단, 쿼리를 입력할 때는 큰 따옴표를 반드시 사용해야 한다.

 

test <- DBI::dbGetQuery(conn = con,
	statement = "SELECT *
    	FROM test.table")

 

 

쿼리를 통해서 원하는 데이터를 데이터베이스로부터 받았다면, 이후 부터는 R 패키지/함수를 이용하여 데이터를 다루고, 시각화하면 된다.