데이터프레임 불러오기 3 - 데이터베이스에서 직접 불러오기: DBI & RSQLite

 

지난 글에서 RODBC패키지를 사용한 데이터프레임 불러오기를 알아봤다. 만약 데이터베이스가 MS SQL(SQL Server), MySQL, Oracle 등 RDBMS가 아니고 SQLite라면 어떻게 해야 할까? 

 

RDBMS 

RDBMS는 Relational Database Management System이고, 일반 DBMS의 단점을 보완하기 위해 만들어졌다. 즉, 그냥 파일 형태로 데이터를 저장하기만 하면 각 파일들이 어떻게 서로 영향을 주고받는지 확인하기 힘드니, 방법을 생각해 낸 게 RDBMS이다. RDBMS는 데이터를 테이블 형태로 저장하고, 각 테이블에 primary key를 만들어서 테이블 간 공통으로 사용될 수 있는 key값을 만들어 서로 연결할 수 있게 해 준다. RDBMS의 예로는 Oracle, MySQL, MS SQL(SQL Server) 등이 있고, DBMS는 XML이 있다.

회사마다 사용하고 있는 서비스가 다르지만 Oracle > MySQL > MS SQL 순으로 많이 쓰인다. 지난번에 DBngin을 통해 인스턴스를 생성했던 MySQL의 경우는 무료이므로 일반인에게 많이 사용되고, Oracle이나 MS SQL은 기업용으로 사용된다. 

 

SQLite

SQLite는 대규모 데이터 관리를 위한 DBMS와는 달리 대부분 로컬에서 사용되는 경량화된 데이터베이스이다. 로컬에서 작업되다 보니 데이터 입출력이 매우 빠르고, 보통 모바일 기기에서 많이 사용된다. DBMS는 아니지만 거의 유사한 개념이고, SQL 쿼리도 동일하게 사용할 수 있다. 앞선 다른 글에서 DBeaver를 맥북에 설치하며 Sample Database를 다운로드했었다. 그때 받은 샘플데이터가 바로 SQLite이다. 인터넷에서도 쉽게 찾아볼 수 있는 대표적인 SQLite의 샘플데이터가 'chinook.db'라는 데이터베이스인데, DBeaver도 같은 데이터베이스를 샘플로 제공했다. 이번 글에서는 DBeaver가 제공한 데이터베이스가 아닌, SQLite의 공식 웹사이트에서 제공하고 있는 'chinook.db'를 다운로드 받아 사용하겠다. 

샘플데이터베이스 chinook.db 다운로드

https://www.sqlitetutorial.net/sqlite-sample-database/

SQLite Sample Database And Its Diagram (in PDF format)

 

위의 웹사이트에서 샘플데이터베이스를 다운로드 받고, chinook.db 파일을 R의 현재 프로젝트 폴더에 붙여 넣기 해준다. 여기서 주의할 점은, 반드시 DBI부터 library로 올려놓고, 그 후에 RSQLite를 해야 한다는 점이다. 이렇게 하지 않으면 불필요한 에러가 계속 발생할 수 있다. 

 

SQLite 샘플 데이터베이스 연결하기

chinook.db파일을 폴더에 넣어 두었으면, 아래와 같이 dbConnect() 함수를 통해서 연결하면 된다. 참고로 파일의 경로 또는 파일의 이름을 직접 큰 따옴표 안에 입력할 수도 있지만, 큰 따옴표만 만들어 놓고 Tab키를 눌러도 현재 프로젝트가 저장되어 있는 폴더 안에 있는 파일들이 보이므로, 거기에서 선택할 수도 있다. 이런 이유로 프로젝트와 같은 폴더에 파일을 붙여 넣는 게 편하다.

• dbConnect() 함수: DBI 패키지에 있는 함수이며, 데이터베이스와 연결
• odbcConnect() 함수: RODBC 패키지에 있는 함수이며, 데이터베이스와 연결

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

con <- odbcConnect("instance_name", uid ="", pwd = "" )

 

dbGetQuery() 함수 통한 SQL쿼리문 사용

위의 과정도 그렇지만, 이후의 과정도 이전 글인 RODBC에서 다뤘던 내용과 거의 비슷하다. RODBC 패키지에서는 sqlQuery() 함수를 사용해서 SQL 쿼리문을 입력하였지만, RSQLite 패키지에서는 dbGetQuery() 함수를 통해서 입력한다. 아래는 그 예시이다. 

• dbGetQuery() 함수: DBI 패키지에 있는 함수이고, 쿼리문 사용가능
• sqlQuery() 함수: RODBC 패키지에 있는 함수이고, 쿼리문 사용가능

## Test for qeurying 
customer_invoice_tbl <- dbGetQuery(conn = db,
           statement = '
           SELECT il.InvoiceId 
	,il.UnitPrice 
	,il.Quantity 
	,SUBSTRING(i.InvoiceDate,1,4)
	,i.BillingCountry 
	,i.BillingCity 
	,c.City 
	,c.Country 
	,SUM(i.Total)
FROM invoices i
	LEFT JOIN invoice_items il ON i.InvoiceId  = il.InvoiceId 
	LEFT JOIN customers c ON i.CustomerId  = c.CustomerId 
	
GROUP BY il.InvoiceId 
	,il.UnitPrice 
	,il.Quantity 
	,i.InvoiceDate
	,i.BillingCountry 
	,i.BillingCity 
	,c.City 
	,c.Country
	
ORDER BY i.InvoiceDate DESC 
           
           ')

customer_invoice_tbl %>% glimpse()

 

SQL이 익숙한 사람이라면 한 번에 적을 수 있겠지만, 그렇지 않은 사람은 쉽지 않을 수 있다. 우선 데이터베이스에 어떤 테이블이 있는지도 잘 모르겠고, 각 테이블에 어떤 항목들이 있는지도 모르지 않은가. 물론 RODBC처럼 RSQLite 패키지도 관련된 함수를 제공하고 있다. 

• dbListTable() 함수: 데이터베이스 안에 있는 테이블 목록 확인
• dbReadTable() 함수: 특정 테이블 안에 있는 항목들 확인

## Checking Tables in 'chinook.db'
dbListTables(db)

## Checking Variables and values in 'chinook.db'
dbReadTable(db, "invoice_items") %>% glimpse()

 

DBeaver 부가 기능 설명: 데이터베이스에 대한 이해

위에서 언급했듯이, SQL 쿼리를 R에서 직접 입력하는건 쉽지 않다. 실제 입력할 SQL 쿼리는 SQL편집기에서 더 정확하게 사용가능하며, 에러가 발생했을 때 찾기도 쉽다. 다른 글에서 소개한 SQL편집기인 DBeaver를 예로 들어보자.

DBeaver에서는 손쉽게 각 데이터 테이블 간의 관계를 확인할 수 있는 기능이 있다. 이런 관계도를 Entity Relation Diagram(ERD)라고 한다. ERD에서는 각 테이블에 어떤 항목들이 있고, 각 항목들이 다른 테이블의 항목과 어떻게 관련이 있는지를 알려준다. 

DBeaver의 ERD

예를 들어, Invoice테이블에 있는 Invoiceid를 통해 InvoiceLine테이블과 연결되고, Customerid를 통해 Customer테이블과 연결된다. 

참고로, DBeaver가 제공하는 샘플 데이터베이스 chinook.db와 SQLite 공식 웹사이트에서 제공하는 chinook.db는 테이블 이름이 약간 다르다. 따라서 DBeaver의 EDR은 그냥 참고로만 보고, DBeaver에서 작성한 쿼리도 R에서는 수정해야 한다.

 

RSQLite 공식문서

다른 R 패키지와 마찬가지로 R cran에서는 RSQLite 관련 공식문서를 제공하고 있다. 아래는 해당 문서의 링크이며, 관련된 함수들을 예제와 함께 확인할 수 있다.

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