關卡 1

這次個課程將著重於如何用R讀寫關聯式資料庫。因為本次課程會操作資料庫,所以在接關功能上會有些狀況。同學請儘量一口氣完成這門課程

關卡 2

資料庫(Database)泛指能夠儲存資料的檔案櫃。使用者可以對檔案中的資料進行:新增、擷取、更新和刪除。

關卡 3

關聯式資料庫(Relationaldatabase),是近代資訊科學發展得非常成熟的一個領域。成熟到目前當人們提到「資料庫」時,在沒有額外說明下,通常都是指「關聯式資料庫」。

關卡 4

關聯式資料庫背後的設計,是牽涉到一種叫「RelationalModel」的數學,並且由許多資訊科學的學者發展出的理論在背後支撐。而關聯式資料庫設計的成功,則可以從現代資訊科學在企業的應用層面所體現。

關卡 5

在現代資訊時代,幾乎我們日常生活中所碰觸到的重要資訊,都過經過關聯式資料庫。例如購票系統、會計系統、倉儲系統等等。關聯式資料庫的設計是可以滿足廣泛系統的需要。

關卡 6

而有數據的地方,就有潛在的分析需求。R語言身為大數據時代最火熱的工具之一,在與各種資料庫系統的串接上,自然有一席之地。

關卡 7

這次課程中我們會描述R語言如何透過DBI和其系列套件為例,操作關聯式資料庫。而這裡我們以SQLite這套精巧的關聯式資料庫系統為例。

關卡 8

在課程開始之前,我們也要先跟各位同學聲明,這次的教學並不會碰到太多資料庫的設計與效能問題。這些問題都是需要搭配系統的架構後,才能妥善解決的問題。我們也要先警告同學,如果直接用R大量的撈取線上資料庫,是有可能影響資料庫的效能的。所以如果在實務使用時,最好是先知會與請教負責資料庫系統的同仁。

關卡 9

請同學先安裝RSQLite套件。它是R與SQLite資料庫之間的橋樑。專業的術語叫:「客戶端」(Client)

check_then_install("RSQLite", "1.0.0")

關卡 10

請同學載入RSQLite

library(RSQLite)

關卡 11

雖然目前在市面上,有許多的SQL資料庫,但是在R中操作起來,都是非常類似的。因為R都是透過DBI套件所定義的函數,讓使用者操作資料庫。

關卡 12

等一下我們要帶同學作一次SQLite資料庫的連線、寫入資料、讀取資料、更改資料和刪除資料。這樣的操作是最基本的,並且未來當同學在使用R操作其他資料庫,甚至是NoSQL等外部儲存裝置,使用的要領也是如此:連線、寫入、讀取、更改和刪除。

關卡 13

我們先連接SQLite資料庫,再跟同學繼續解釋。我們已經事先準備了一個SQLite資料庫的檔案,並且把路徑存在:db_path變數之中。請同學先輸入:drv<-dbDriver("SQLite"),取得連接SQLite資料庫的方式。

drv <- dbDriver("SQLite")

關卡 14

接著我們建立一個SQLite資料庫的連線:db<-dbConnect(drv,db_path)

db <- dbConnect(drv, db_path)

關卡 15

這裡的db物件,就是這個資料庫在R的代理人。後續若要從這個資料庫作任何動作,通常都要把db這個物件當成函數的參數之一。在DBI的文件中會把db這種角色的物件記載為:connection

關卡 16

接著我們來嘗試把這筆數據寫入SQL資料庫之中。在DBI界面中,提供了dbWriteTable這個函數,所以請大家先打開這個函數的說明頁面。

?dbWriteTable

關卡 17

各位同學應該可以在說明視窗的左上角看到:dbReadTable{DBI}的字樣。這代表我們目前操作的函數,是DBI套件所提供的功能。而dbReadTable和dbWriteTable的說明頁面是寫在同一頁的,

關卡 18

請問下列哪一個選項「不是」DBI套件dbWriteTable的參數?

overwrite

關卡 19

因為db是RSQLite提供的資料庫代理人(當初的drvdbDriver("SQLite")),所以除了DBI的dbWriteTable之外,我們還有額外的參數可以使用。請同學輸入:help.search("dbWriteTable")

help.search("dbWriteTable")

關卡 20

R應該會列出至少兩個dbWriteTable的說明,並且其中一個是來自RSQLite。在Rstudio下,同學只要用滑鼠去點選連結即可。但是限於swirl環境,還是需要同學輸入以下指令(或是skip()help("dbWriteTable,SQLiteConnection,character,data.frame-method")使用R原生環境的同學平時也得利用上述指令查詢RSQLite提供的dbWriteTable

help("dbWriteTable,SQLiteConnection,character,data.frame-method")

關卡 21

此時的說明頁面左上角,應該會註明這份文件是來自RSQLite套件。請同學參考文件內容後,回答:overwrite是不是dbWRiteTable的參數呢?

yes

關卡 22

這是R在處理SQL資料庫的設計。如果使用者只有操作到DBI的dbWriteTable提供的功能,那未來相同的程式碼,運作在其他的資料庫系統時,不需要太大的修改即可運作。但是如果是用了RSQLite提供的功能(如overwrite),那在切換資料庫時,就要看新的資料庫的R套件有沒有提供類似的功能。

關卡 23

在解釋dbWriteTable之前,我們要先對資料庫系統有一點簡單的認知。資料庫在處理數據時,會將數據放在一個又一個的表格中。這感覺非常類似R會把一個data.frame存放到對應的變數之中。

關卡 24

回到DBI::dbWriteTable(connection,name,value,...)的說明,R就會嘗試透過connection物件,把value的data.frame寫入到名稱為name的表格中。

關卡 25

舉例來說,我們現在想要把lvr_land這個記載實價登錄資訊的表格,寫到SQLite資料庫中。請同學利用指令:head(lvr_land)看一下這個資料集。

head(lvr_land)

關卡 26

欄位名稱很清楚的註明每個欄位的數據意義。

關卡 27

接著,我們透過dbWriteTable將實價登錄資料寫入資料庫中。請同學輸入:dbWriteTable(db,"lvr_land2",lvr_land)

dbWriteTable(db, "lvr_land2", lvr_land)

關卡 28

這樣的動作,就非常類似在R中執行:lvr_land2<-<lvr_land的值>,差別在於lvr_land2這個名字是一個SQLite資料庫的表格。

關卡 29

R的大部分的資料庫套件,在dbWriteTable函數中都會提供appendoverwrite兩個參數。

關卡 30

overwrite=TRUE時,R會自動把撞名的表格刪除,並且寫入新的資料。如果是overwrite=FALSE時撞名,R就會出錯。由於讓同學測試的話,swirl會跳掉,所以我們這邊就只能先帶過了。

關卡 31

append=TRUE時,R會把我們要寫入的資料,接在撞名的表格之下。這等介紹完dbReadTable之後,我們再試試。

關卡 32

接下來,我們透過dbReadTable來從表格中取出資料。這個指令只需要兩個參數:connectionname。請同學輸入:dbReadTable(db,"lvr_land2"),同學就應該可以看到剛剛的數據。

dbReadTable(db, "lvr_land2")

關卡 33

我們可以再寫入一次lvr_land,並且設定append=TRUE。請同學試試看輸入:dbWriteTable(db,"lvr_land2",lvr_land,append=TRUE)

dbWriteTable(db, "lvr_land2", lvr_land, append = TRUE)

關卡 34

請再次執行dbReadTable(db,"lvr_land2"),讀取“lvr_land2”表格看看。

dbReadTable(db, "lvr_land2")

關卡 35

這次我們應該看到38或57筆資料(因為lvr_land總共有19筆資料,寫入兩次或三次後應該有38筆或57筆),如果不幸需要接關的同學可能看到更多筆…

關卡 36

dbListTables(db)可以列出目前已經存在於資料庫的表格。請同學試試看。

dbListTables(db)

關卡 37

請試著用iris2<-dbReadTable(db,"iris")來將剛剛看到的iris表格讀出,並存入變數iris2。這是我是前先把R內建的iris資料集寫入資料庫後產生的表格。

iris2 <- dbReadTable(db, "iris")

關卡 38

如同RDataMining-03中看到jsonlite的vignette,我們可以用all.equal(iris,iris2)來比較看看兩者是不是一模一樣。

all.equal(iris, iris2)

關卡 39

同學應該會看到:"Component“Species”:'current'isnotafactor"的字樣,這代表當我們從資料庫中把iris拿出來後,Species的型態改變了。

關卡 40

型態改變這件事情,其實是可大可小的。在大部分的狀況下,例如建立統計模型、製作圖表,factor字串(character)的互換並不會帶來太大個困擾。但是在某些很不常見的狀況下(如strsplit在遇到factor的時候會出錯),可能會導致程式出錯。這是很討厭的。

關卡 41

我有開發過大型R的應用系統的經驗,而像這類型態不符的地方,往往會導致系統產生無法預期的結果。舉例來說,當我嘗試把資料處理的流程搬入資料庫後,某些原本可以運作的程式碼就壞掉了(就是strsplit)。

關卡 42

我猜大部分的R使用者,大概都不用管到這件事情,但是如果有一天當R不聽你話的時候,請記得檢查這些小地方。

關卡 43

剛剛我們講解的dbReadTabledbWriteTable都是一次把所有資料倒進去和拿出來的方法,雖然方便,但是當數據量很大的時候,我們可能想要一次只拿一部分的資料。進階的使用者可能需要更多關聯式資料庫所提供的功能。

關卡 44

DBI界面中,我們可以利用dbGetQuerydbSendQuery來執行SQL語句。SQL是操作關聯式資料庫必學的工具,也是資料科學家必備的工具之一。請同學執行:dbGetQuery(db,"SELECT*FROMirisWHEREspecies=\"virginica\"")

dbGetQuery(db, "SELECT * FROM iris WHERE species = \"virginica\"")

關卡 45

剛剛的"SELECT*FROMirisWHEREspecies=\"virtinica\""是一段SQLexpression。而dbGetQuery會解析這樣的SQLexpression,並且依照指令進行資料庫的操作後,再把結果直接回傳給R。

關卡 46

dbSendQuery函數則會先把結果儲存於Result物件中,再由使用者一段一段的取出。請同學試著執行:rs<-dbSendQuery(db,"SELECT*FROMiris")

rs <- dbSendQuery(db, "SELECT * FROM iris")

關卡 47

上一關所拿到的rs物件,就是整個資料集的「代理」。我們可以利用fetch方法,一次拿一點資料出來。請同學試試看:fetch(rs,1)

fetch(rs, 1)

關卡 48

請同學再試一次:fetch(rs,1)(你可以按一下↑,正常安裝的R會顯示上一個輸入)

fetch(rs, 1)

關卡 49

同學應該會注意到,兩次的結果是不同的。這是因為在第一次dbSendQuery指令完成之後,R和資料庫就把結果暫存到某個地方,並且將代理物件回傳,而這就是rs。之後每次執行fetch指令時,R就會「依照順序」把結果一個又一個的回傳給使用者。如果使用者每次想多拿一些資料,則可以使用第二個參數,如:fetch(rs,100)一次取出100筆資料。

關卡 50

當我們不需要再使用rs物件時,就刪除它:dbClearResult(rs)請同學試試看。

dbClearResult(rs)

關卡 51

dbSendQueryfetch的技巧在使用R處理「大量資料」,是非常有用的。尤其是當資料量「超過記憶體」時,運用資料庫來優化在硬碟上處理資料的效能,是很有效率的方式。此時,如果我們的演算法是不需要一次看到所有的資料,那dbSendQueryfetch的方式就非常有用,避免我們發生滑鼠不能移動的悲劇。

關卡 52

上述的課程中,我們並沒有講解SQLexpression。同學如果有興趣的話,可以再找SQL相關的資料學習。反之,我們把這部份的細節,留待後續dplyr套件的系列課程中,再跟同學介紹。我們這裡想要強調的是,所有對資料庫的基礎操作,DBI有提供一些輔助函數,例如:dbGetQuerydbSendQuery

關卡 53

最後,我們示範資料庫的transaction功能。這在處理資料時,也是非常重要的事情。

關卡 54

當我們在運用外部資源(如資料庫、如檔案系統)處理資料時,最害怕的狀況是被意外中斷。舉例來說,網路可能剛好斷線、硬碟突然壞軌、或是發生各種平常沒遇到的狀況。這時候除了幫工程師買乖乖之外,實務上其實有一些技巧可以避免意外帶來的困擾。

關卡 55

意外對我們最大的困擾並不是工作中斷。畢竟作分析的工作,畫畫的時候R掛掉了,就…重新開R再跑一遍就好了。但是當我們正在寫入資料庫時,意外的中斷就會帶來以下的困擾。

關卡 56

當我們排除意外的原因,要繼續工作時,就會問:目前資料庫的狀態,是「還未寫入」、「寫到一半」還是「寫入完畢」。「還未寫入」的狀況下,我們只要重新執行程式即可。而「寫入完畢」的話就更棒了,我們只要繼續後續的工作。但是若是「寫到一半」的狀態,要接著工作就會非常麻煩(可能要改程式碼)。

關卡 57

Transaction就是一種保證資料庫的狀態一定是「還未寫入」或是「寫入完畢」的機制。

關卡 58

在進行接下來關於Transaction的練習之前,我們先重新連線。請同學先中斷連線,輸入:dbDisconnect(db)

dbDisconnect(db)

關卡 59

請同學再次輸入:db<-dbConnect(drv,db_path)`

db <- dbConnect(drv, db_path)

關卡 60

在R裡面,我們可以利用dbBegin(db)來開啟一個Transaction。請同學試試看。

dbBegin(db)

關卡 61

接著,讓我們利用dbRemoveTable(db,"CO2")來刪除“CO2”表格。

dbRemoveTable(db, "CO2")

關卡 62

請同學用dbListTables(db)來檢查“CO2”是不是已經消失在表格清單之中。

dbListTables(db)

關卡 63

請同學輸入dbDisconnect(db),模擬當意外發生時與資料庫的連線中斷的狀況。

dbDisconnect(db)

關卡 64

請同學輸入db<-dbConnect(drv,db_path)重新連接資料庫。

db <- dbConnect(drv, db_path)

關卡 65

請同學用dbListTables(db)來檢查“CO2”是不是又重出江湖了?

dbListTables(db)

關卡 66

除了中斷連線,我們也可以主動使用dbRollback(db)指令把資料庫的狀態復原至我們執行dbBegin(db)的時間點。

關卡 67

再請同學輸入:dbBegin(db)

dbBegin(db)

關卡 68

接著,讓我們利用dbRemoveTable(db,"CO2")再刪除“CO2”表格一次。

dbRemoveTable(db, "CO2")

關卡 69

這次我們輸入dbCommit(db)

dbCommit(db)

關卡 70

請同學用dbListTables(db)來檢查“CO2”是不是確實消失了?

dbListTables(db)

關卡 71

之後,就算我們斷線重連,CO2也不會回來了。

關卡 72

總而言之,當我們開始dbBegin(db)之後,所有的變更,都要等到我們執行dbCommit(db)才會生效。中間只要發生任何狀況:無論是我們主動dbRollback(db),或是發生意外導致斷線,資料庫都會自動復原至我們執行dbBegin(db)時的狀態。這招在寫入大量數據時,也是很重要的技巧。這個技巧可以保證我們的「資料正確性」

關卡 73

資料庫還有許多處理資料的技術與手法,例如JOIN等相關技術。但是這都牽涉到SQL語法的學習,而且各家資料庫系統在這部份的語法也會略有差異,所以本次課程僅著重於R與資料庫之間的簡易操作,以及基礎概念。

關卡 74

最後還是請各位同學利用以上學習的內容,操作一次資料庫系統。請同學在完成之後存檔,並輸入submit()來檢查結果是否符合預期。如果同學在檔案中看到亂碼,請使用Rstudio左上角的File->Reopen

# 首先,我們重新連線到另一個資料庫
library(RSQLite)
drv <- dbDriver("SQLite")
db <- dbConnect(drv, db_path2)

# 請列出現在的資料庫中的表格清單
tb_list <- {
  # 請填寫你的程式碼
  dbListTables(db)
}

# 資料庫中的TWII是台灣加權指數,是透過quantmod套件從yahoo下載的數據
# 請問同學,這段數據的日期範圍,是幾號到幾號呢?
# 你的答案應該是一個字串
twii_head <- {
  # 請填寫你的程式碼
  dbReadTable(db, "TWII")$date[1]
}
twii_tail <- {
  # 請填寫你的程式碼
  tail(dbReadTable(db, "TWII")$date, 1)
}
stopifnot(class(twii_head) == "character")
stopifnot(length(twii_head) == 1)
stopifnot(class(twii_tail) == "character")
stopifnot(length(twii_tail) == 1)

# 接著我們開啟一個Transaction
dbBegin(db)

# R 內建的iris資料共有三種類別,一共150筆花的量測資料
# 請同學將屬於setosa種類(Species的值為"setosa")的資料,
# 寫入到database,並且取名為"setosa"
{
  # 請填寫你的程式碼
  dbWriteTable(db, "setosa", iris[iris$Species == "setosa",], overwrite = TRUE)
}

# 請確實將資料寫入!
{
  # 請填寫你的程式碼
  dbCommit(db)
}

# 最後,我們中斷連線
dbDisconnect(db)

# 測試程式將會檢查這個資料庫內的資料