학습목표
이번 학습을 마치게 되면 학습을 완료하신 분은 다음을 익히게 됩니다 :
- 기존 데이터베이스에 R을 연결하는 방법을 알게 된다.
- R 내부 SQL 구문을 사용해서 데이터베이스에 질의를 던져 데이터 일부 부분집합을 뽑아낸다.
- R과 SQL을 사용해서 기존
.csv파일로부터 데이터베이스를 신규로 만들어낸다.- 스크립트를 활용한 인터랙션 작업을 수행하게 되면 분석에 대한 재현성을 증진시키게 되는 것을 이해한다.
스크립트 언어로 데이터베이스와 상호작용하는 것은 GUI 인터페이스를 경유하여 데이터베이스에 질의문을 던지는 것과 비교하여 여러 장점을 제공한다. 데이터베이스에 GUI 인터페이스를 사용하는 방법은 사용하기 쉽고, 임의 질의문에 대한 결과를 재빠르게 확인할 수 있다. 프로그래밍 인터페이스(이번 경우 R, 하지만 임의 어떤 언어도 가능)로 데이터베이스에 질의문을 던지는 것은 다소 복잡할 수 있다. 하지만, 주고받는 상충관계가 존재하는데 데이터 조작 이력이 코드에 담겨진다는 점이다. 집계, 요약, 기타 데이터베이스 연산도 코드로 보관된다. 이렇게 함으로써, 데이터 조작 사전 작업이 망실되지 않고 보존되어 본인 뿐만 아니라 다른 작업자도 나중에 재현할 수 있게 된다.
sqlite 데이터베이스에 연결다양한 팩키지를 사용하여 다양한 데이터베이스에 R을 연결시킬 수 있다. 이번 경우에 RSQLite 팩키지를 사용해서 기존에 작업한 SQLite3 데이터베이스에 연결시킨다. 하지만, 동일한 메커니즘을 통해 JDBC 혹은 ODBC를 경유해서 R을 연결시킬 수도 있고, MySQL 같은 특정 데이터베이스에 붙일 수 있다.
이번 연습과제로 방금전에 생성시킨 mammal 데이터베이스에 R을 연결시킨다. RSQLite 팩키지가 필요한데, 이를 위해서 먼저 설치작업이 필요하다. install.packages('RSQLite').
library(RSQLite)## Loading required package: DBI## Warning: package 'DBI' was built under R version 3.2.5## 편의상 먼저 dbname 과 driver를 설정한다.
myDB <- "data/portal_mammals.sqlite"
conn <- dbConnect(drv = SQLite(), dbname= myDB)이제, 데이터베이스와 연결되었다. 데이터베이스 테이블 구조를 탐색하자. SQLite 파이어폭스 GUI에서 살펴봤던 모든 테이블 목록을 기억한다. R에서는 SQL 명령어를 사용한다.
다음 질의문을 던져 전체 테이블 목록을 포함한 데이터베이스 구조에 대한 정보를 확인할 수 있다:
dbGetQuery(conn, "SELECT type, tbl_name FROM sqlite_master")RSQLite 팩키지도 테이블과 테이블 내 필드 목록을 살펴보는데 사용되는 함수를 제공한다. 데이터베이스에 포함된 테이블과 테이블에 포함된 필드명과 레코드 갯수를 다음 질의문으로 확인한다.
dbListTables(conn)## [1] "plots" "species" "surveys"#get list of fields in the surveys table
dbListFields(conn,"surveys")## [1] "record_id" "month" "day" "year"
## [5] "plot_id" "species_id" "sex" "hindfoot_length"
## [9] "weight"dbGetQuery(conn,"SELECT count(*) FROM surveys")## count(*)
## 1 35549항상 R에서 열린 연결(connection)d을 닫는 것은 좋은 습관이자 관행이다. 질의문을 던져 작업을 완료했기 때문에, 연결을 닫아보자. 주목할 점은 연결을 끊게 되면, 새로운 연결을 열어 데이터를 다시 가져오고 질의문을 날려야 한다.
dbDisconnect(conn)## [1] TRUE지금까지 데이터베이스 연결에 대한 기본적인 사항과 더불어 기본 구조에 대해 탐색했다. 이제부터 데이터베이스 내부에 담긴 데이터 부분집합에 접근하는 질의문을 작성하는데, 앞서 SQLite에서 사용한 동일한 방식을 사용한다.
이전 학습에서 사용한 기본 질의문부터 시도해보자. 단순 질의문은 입력값으로 데이터베이스 연결과 더불어 질의문을 인자로 넘기게 되면 결과를 데이터프레임에 담아 반환한다.
질의문을 넘기기 전에 데이터베이스 연결을 다시 채결한다.
## Set dbname and driver out of convenience
myDB <- "data/portal_mammals.sqlite"
conn <- dbConnect(drv = SQLite(), dbname= myDB)다음으로, 질의문을 작성한다. dbGetQuery 함수를 사용해서 질의문을 실행하고 데이터를 data.frame 객체로 반환해서 R에서 접근한다.
q <- 'SELECT DISTINCT year, species_id FROM surveys'
result <- dbGetQuery(conn, q)
head(result)## year species_id
## 1 1977 NL
## 2 1977 DM
## 3 1977 PF
## 4 1977 PE
## 5 1977 DS
## 6 1977 PP도전과제
plot_id테이블에genus갯수를 반환하는 질의문을 작성한다.외래키(foreign key)가
species_id같은 유일무이한 유니크 id를 참조하는 다음 구문을 사용해서, 다수 테이블을 병합할 수도 있다:SELECT table.col, table.col FROM table1 JOIN table2
ON table1.key = table2.key
JOIN table3 ON table2.key = table3.key
species,plot,survey테이블을 모두 병합하는 질의문을 작성한다. 질의문 실행 결과plot유형별로genus갯수를 뽑아낸다. 그리고 결과값을 R 막대그래프(barplot)로 생성하시오.
q <- "SELECT d.plot_type , c.genus, count(*)
FROM
(SELECT a.genus, b.plot_id
FROM species a
JOIN surveys b
ON a.species_id = b.species_id) c
JOIN plots d
ON c.plot_id = d.plot_id
GROUP BY d.plot_type,c.genus"
result <- dbGetQuery(conn,q)
head(result)## d.plot_type c.genus count(*)
## 1 Control Ammodramus 1
## 2 Control Ammospermophilus 125
## 3 Control Amphispiza 76
## 4 Control Baiomys 1
## 5 Control Calamospiza 2
## 6 Control Callipepla 2지금까지 작업한 것은 GUI를 사용해서도 쉽게 할 수 있던 동일한 유형의 질의문을 실행시킨 것이다. 이제 스크립트 질의문(scripted query)의 진정한 힘을 활용해보자. 2년 마다 년도별로 얼마나 많은 설치류(rodent)가 발견되는지 알고자 하는 상황을 상상해 보자. 이런 결과를 얻기 위해서, 데이터베이스에서 날짜 범위를 2년 마다 순열을 생성하고 나서 질의문을 새로 작성한다.
yearRange <- dbGetQuery(conn,"SELECT min(year),max(year) FROM surveys")
years <- seq(yearRange[,1],yearRange[,2],by=2)paste() 함수를 사용해서 질의문에 들어가는 문자열을 만들어낸다.
q <- paste("
SELECT a.year,b.taxa,count(*) as count
FROM surveys a
JOIN species b
ON a.species_id = b.species_id
AND b.taxa = 'Rodent'
AND a.year in (",
paste(years,collapse=",")
,")
GROUP BY a.year, b.taxa",
sep = "" )
rCount <- dbGetQuery(conn,q)
head(rCount)## year taxa count
## 1 1977 Rodent 487
## 2 1979 Rodent 658
## 3 1981 Rodent 1394
## 4 1983 Rodent 1594
## 5 1985 Rodent 1398
## 6 1987 Rodent 1618전체 년도를 모두 타이핑하지 않고도, paste 명령어를 중첩하여 질의문을 프로그램적으로 생성할 수 있다. 특히 작성해야 되는 질의문이 복잡해지면, for 루프를 활용해서 질의문을 작성하는 것도 가능하다.
R을 사용해서 기존 .csv 파일로부터 데이터베이스와 연관된 구조(스키마라고도 부름)를 생성할 수 있다. R로 지금까지 작업한 mammals 데이터베이스를 다시 생성하자. .csv 파일을 먼저 불러 읽어온다.
지금까지는 기존에 존재하는 데이터만 가지고 작업을 진행했다. 하지만, R을 활용하여 기존 일반적인 파일을 데이터베이스로 만들어낼 수 있다. 일반적인 파일을 사용해서 mammals 데이터베이스로 만들어 보자. 먼저 파일을 읽어들인다.
species <- read.csv("data/species.csv")
surveys <- read.csv("data/surveys.csv")
plots <- read.csv("data/plots.csv")다음으로 데이터베이스를 생성하고 나서 테이블을 추가한다.
myDB <- "portalR.db"
myConn <- dbConnect(drv = SQLite(), dbname= myDB)
dbListTables(myConn)## character(0)데이터베이스에 연결을 채결해서 데이터베이스를 새로 생성했다. 데이터베이스에 포함된 테이블 목록을 불러보면 데이터베이스가 텅빈 것이 확인된다. 다음으로 방금전에 일반 파일로 불러온 데이터프레임을 데이터베이스에 추가하자.
dbWriteTable(myConn,"species",species)## [1] TRUEdbListTables(myConn)## [1] "species"dbGetQuery(myConn,"SELECT * from species limit 10")## species_id genus species taxa
## 1 AB Amphispiza bilineata Bird
## 2 AH Ammospermophilus harrisi Rodent
## 3 AS Ammodramus savannarum Bird
## 4 BA Baiomys taylori Rodent
## 5 CB Campylorhynchus brunneicapillus Bird
## 6 CM Calamospiza melanocorys Bird
## 7 CQ Callipepla squamata Bird
## 8 CS Crotalus scutalatus Reptile
## 9 CT Cnemidophorus tigris Reptile
## 10 CU Cnemidophorus uniparens Reptile데이터베이스 위치를 점검하면, 자동으로 데이터가 로컬디스크에 저장된 것이 확인된다. R과 RSQLite 모두 기존 데이터베이스에 질의문을 던져 결과를 얻을 수 있는 쉬운 방법을 제공하는 것과 더불어, 일반적인 파일을 기반으로 데이터베이스를 생성하는 것도 가능케 한다.
도전과제
나머지 테이블을 기존 데이터베이스에 추가하시오. 파이어폭스 SQLite 관리자를 사용해서, 데이터베이스가 성공적으로 생성되었는지 검증한다.
추가 도전과제
앞선 수업에서 작성한 질의문을 실행시켜
mammals데이터베이스가 다시 재생성되었는지 검증하시오.
모든 작업이 완료되면, 데이터베이스 연결을 끊는 것은 꼭 기억한다!