關卡 1

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

關卡 2

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

關卡 3

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

關卡 4

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

關卡 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所提供的資料庫代理人(當初的drv代表的是dbDriver("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' is not a factor"的字樣,這表示當我們從資料庫中將iris拿出來後,Species的型態改變了。

關卡 40

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

關卡 41

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

關卡 42

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

關卡 43

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

關卡 44

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

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

關卡 45

剛剛的範例中,"SELECT * FROM iris WHERE species = \"virtinica\""是一段SQL expression。 而dbGetQuery會解析這樣的SQL expression,並且依照指令進行資料庫的操作後,再將結果直接回傳給R 。

關卡 46

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

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

上述的課程中,我們並沒有講解SQL expression。 同學如果有興趣的話,可以再找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 &lt;- dbConnect(drv, db_path)

關卡 60

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

dbBegin(db)

關卡 61

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

dbRemoveTable(db, &quot;CO2&quot;)

關卡 62

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

dbListTables(db)

關卡 63

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

dbDisconnect(db)

關卡 64

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

db &lt;- dbConnect(drv, db_path)

關卡 65

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

dbListTables(db)

關卡 66

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

關卡 67

再請同學輸入:dbBegin(db)

dbBegin(db)

關卡 68

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

dbRemoveTable(db, &quot;CO2&quot;)

關卡 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)

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