SQL SQLite 사용

관계형 데이터베이스로 작업하기

시나리오에 가장 적합한 솔루션은 데이터베이스를 이용하는 것

컴퓨터 메모리를 초과할 수 있는 데이터를 훨씬 쉽게 저장할 수 있음

데이터베이스의 데이터는 사용자가 제공한 조건에 따라 쿼리할 수 있음

기존 레코드를 쉽게 업데이트하고 데이터베이스에 새 레코드를 삽입할 수 있음

관계형 데이터베이스는 테이블과 테이블 관계의 집합 관계형 데이터베이스 테이블은 R의 데이터 프레임과 표현이 동일함

여러 테이블 간 정보를 쉽게 결합할 수 있도록 테이블은 관계를 가질 수 있음

가장 간단한 데이터베이스라고 할 수 있는 SQLite(http://sqlite.org/)에서 시작할 것

SQLite는 이식 가능한 경량 데이터베이스 엔진임

R에서는 SQLite 데이터베이스를 활용하는 데 RSQLite 패키지를 사용함 CRAN에서 설치하려면 다음 코드를 실행함

먼저 SQLite 데이터베이스를 만드는 방법을 알아보자

data/example.sqlite에 예제 데이터베이스를 만들려면 해당 디렉터리를 사용할 수 있어야 함

이 디렉터리가 없다면 만들어야 함 이제 data 디렉터리를 사용할 수 있음

다음으로 RSQLite 패키지를 로드함 데이터베이스 드라이버(SQLite( ))와 데이터베이스 파일(data/example.sqlite)을 제공하여 연결을 생성함 파일은 원래 없었지만, 드라이버가 빈 SQLite 데이터베이스를 나타내는 빈 파일을 생성함

 

SQLite 데이터베이스 만들기

데이터베이스 연결(con)은 사용자와 시스템 사이에 있는 레이어임

관계형 데이터베이스에 대한 연결을 생성함 관계형 데이터베이스로 데이터를 쿼리함 데이터를 가져오거나 업데이트할 수 있음

연결은 연결을 종료하기 전까지 모든 후속 작업에서 사용함

전형적인 관계형 데이터 베이스에서 특정 이름과 데이터 유형으로 테이블에 열을 만듦 레코드를 행으로 삽입하고 기존 레코드를 갱신할 수 있음

관계형 데이터베이스 테이블은 R의 데이터 프레임과 매우 유사함

 

 

install.packages("RSQLite")
if (!dir.exists("data")) dir.create("data")
library(RSQLite)
con <- dbConnect(SQLite(), "data/example.sqlite")

con

# SQLite 데이터베이스 만들기
#데이터 프레임이 준비됨
#이 데이터 프레임을 데이터베이스에 테이블로 저장할 수 있게 dbWriteTable() 함수를
#호출함

example1 <- data.frame(
  id=1:5, type= c("A","A","B","B","C"),
  score = c(8,9,8,10,9), stringsAsFactors = FALSE)

example1

dbWriteTable(con, "example1",example1)


library(nycflights13)

#코드에서 아마 다른 테이블 이름을 사용했어도 같은 데이터를 저장할 것
#마지막으로 dbDisconnect() 함수를 사용하여 데이터베이스 연결을 종료함
#데이터 작업에 더 이상 con을 사용할 수 없음

dbDisconnect(con)

#패키지를 설치했다면 다음과 같이 data() 함수를 호출하여 두 데이터 프레임을 불러옴
data("diamonds", package = "ggplot2")
data("flights", package = "nycflights13")


con <- dbConnect(SQLite(), "data/datasets.sqlite")

dbWriteTable(con, "diamonds", diamonds, row.names=FALSE)
dbWriteTable(con, "flights", flights, row.names=FALSE)
dbDisconnect(con)

class(diamonds)
class(flights)

#diamonds와 flights는 단순히 data.frame 클래스가 아니라 좀 더 복잡한 형태임
#과거에는 이 객체들을 데이터베이스에 저장할 때 다음 코드처럼 as.data.frame() 
#함수를 사용하여 평범한 data.frame 객체로 변형해야 했음
#최근 업데이트에서 tbl_df 객체를 지원하면서 더 이상 그럴 필요가 없어짐

con <- dbConnect(SQLite(), "data/datasets2.sqlite")
dbWriteTable(con, "diamonds", as.data.frame(diamonds), row.names = FALSE)
dbWriteTable(con, "flights", as.data.frame(diamonds), row.names = FALSE)
dbDisconnect(con)

con <- dbConnect(SQLite(), "data/example2.sqlite")

chunk_size <- 10
id <- 0
for (i in 1:6) {
  chunk <- data.frame(id = ((i - 1L) * chunk_size):(i * chunk_size - 1L),
                      type = LETTERS[[i]],
                      score = rbinom(chunk_size,10, (10-i)/10),stringsAsFactors = FALSE)
dbWriteTable(con, "products", chunk, append = i > 1, row.names = FALSE)
}

#각 데이터 덩어리(chunk)는 일부 고정된 데이터와 랜덤한 숫자가 섞여 있는 데이터
#프레임 객체임
#매번 이 레코드를 products 테이블에 추가함
#이 예제와 이전 예제는 다음 차이점이 있음
#dbWriteTable() 함수를 호출할 때 첫 번째 chunk에는 append = FALSE를 사용하여
#데이터베이스에 해당 테이블을 만듦
#이후의 각 chunk에는 append = TRUE를 사용하여 기존 테이블에 추가한다는 것임


dbDisconnect(con)

#SQLite 데이터베이스를 만들면 테이블에 저장된 데이터뿐만 아니라 모든 테이블의
#이름이나 열같은 일부 메타데이터에 접근할 수 있음
#이를 확인하고자 이전에 만든 SQLite 데이터베이스에 연결해 보자


con <- dbConnect(SQLite(), "data/datasets.sqlite")
con

dbExistsTable(con, "diamonds")
dbExistsTable(con, "mtcars")

#이전에 만든 datasets.sqlite에는 diamonds와 flights만 입력했기 때문에
#dbExistsTable() 함수는 올바른 결과를 보여줌
#테이블 존재 여부를 알려 주는 대신 dbListTables() 함수를 사용하여 데이터베이스에
#있는 모든 테이블 목록도 출력할 수 있음

dbListTables(con)
#dbListFields() 함수를 사용하면 특정 테이블에 있는 모든 열(필드) 이름의 목록을 얻을 수 있음
dbListFields(con, "diamonds")
#dbWriteTable() 함수와 반대로 dbReadTable() 함수는 테이블을 데이터 프레임 객체로 읽어 올 수 있음
db_diamonds <- dbReadTable(con, "diamonds")
dbDisconnect(con)

 

#dbListFields() 함수를 사용하면 특정 테이블에 있는 모든 열(필드) 
#이름의 목록을 얻을 수 있음
head(db_diamonds,3)

#bWriteTable() 함수와 반대로 dbReadTable() 함수는 테이블을 데이터 프레임
#객체로 읽어 올 수 있음
head(diamonds,3)

 

#두 데이터 프레임 안의 데이터는 완전히 동일하게 보임
#함수를 사용하여 비교하면 실제로 이 둘은 동일하지 않음
identical() 
identical(diamonds, db_diamonds)

 

#str()을 호출하여 두 데이터 프레임의 구조가 어떤 차이점이 있는지 살펴보자
#첫째, 데이터베이스에서 나온 데이터 프레임의 구조는 다음과 같음
str(db_diamonds)

 

con <- dbConnect(SQLite(), "data/datasets.sqlite")
dbListTables(con)

db_diamonds <- dbGetQuery(con, "select * from diamonds")

head(db_diamonds,3)

#데이터에 있는 모든 유일한 경우를 선택하려면 select distinct를 사용함
#예를 들어 다음 코드는 diamonds에서 가능한 모든 cut의 유일한 값을 구함
dbGetQuery(con, "select distinct cut from diamonds")

#dbGetQuery()는 때로 열이 하나만 있을 때도 항상 data.frame 값을 돌려줌
#값을 원자 벡터로 추출하려면 데이터 프레임에서 첫 번째 열을 선택함
dbGetQuery(con, "select distinct clarity from diamonds")[[1]]

#다음 코드처럼 기존에 있는 열을 사용해서 새로운 열을 만들고, 새로 만든 열을
#사용하여 또 다른 열을 만들려고 한다면 어떻게 될까?
db_diamonds <- dbGetQuery(con,
                          "select carat, price, x * y * z as size, price / size as value_density from diamonds")

#당연히 이러한 식으로는 할 수 없음
#A as B에서 A는 기존 열로 구성되어 있음
#이렇게 꼭 해야 할 때는 중첩된 쿼리를 사용함
#즉, 중첩된 select로 만드는 임시 테이블에서 열을 선택하면 됨
db_diamonds <- dbGetQuery(con, "select *, price / size as value_density from
                          (select carat, price, x*y*z as size from diamonds)")
head(db_diamonds,3)

#데이터베이스 쿼리에서 다음으로 중요한 구성 요소는 조건임
#결과를 만족시키는 조건을 지정하려면 where를 사용해야 함
#예를 들어 cut이 Good인 다이아몬드를 다음과 같이 선택할 수 있음
good_diamonds <- dbGetQuery(con,
                            "select carat, cut, price from diamonds where cut ='Good'")

head(good_diamonds,3)

#cut이 Good인 레코드의 비율이 모든 레코드에서 낮다는 점에 유의하자
nrow(good_diamonds) / nrow(diamonds)

#동시에 만족해야 하는 조건이 여러 가지가 있을 때는 이러한 조건을 조합하려고 and를 사용할 수 있음
#예를 들어 cut이 Good이고, color가 E인 모든 레코드를 선택해 보자

good_e_diamonds <- dbGetQuery(con,
                              "select carat, cut, color, price from diamonds where cut = 'Good' and color = 'E'")
head(good_e_diamonds,3)

 

nrow(good_e_diamonds) / nrow(diamonds)

# 이와 비슷한 연산자로는 or와 not이 있음
# 간단한 논리 연산자 외에도 필드 값이 주어진 집합에 포함되는지 알고자 레코드를
# 검사할 때 in을 사용할 수 있음
# 예를 들어 color가 E와 F인 레코드를 선택할 수 있음

color_ef_diamonds <- dbGetQuery(con, "select carat, cut, color, price from diamonds where color in ('E','F')")

nrow(color_ef_diamonds)

 

#다음 테이블로 결과를 검증할 수 있음
table(diamonds$color)

#in을 사용하려면 한 집합을 지정해야 함
#in과 마찬가지로 범위를 지정할 때 between A and B를 사용할 수 있음

some_price_diamonds <- dbGetQuery(con, "select carat, cut, color, price from diamonds where price between 5000 and 5500")

nrow(some_price_diamonds)

#문자형 열에 유용한 연산자로 like가 있음
#이것은 비슷한 문자형 패턴으로 레코드를 필터링할 수 있음
#예를 들어 cut 변수가 Good으로 끝나는 레코드를 모두 선택할 수 있음
#즉, Good이나 Very Good이 선택될 수 있는데, like '%Good'처럼 표기하면 됨

good_cut_diamonds <- dbGetQuery(con, "select carat, cut, color, price from diamonds where cut like '%Good'")

diamond_selector <- data.frame(cut = c("Ideal","Good","Fair"),
                               color = c("E","I","D"), clarity = c("VS1","I1","IF"), stringsAsFactors = FALSE)
diamond_selector

dbWriteTable(con, "diamond_selecotr", diamond_selector, row.names = FALSE, overwrite = TRUE)