關卡 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函數中都會提供append和overwrite兩個參數。
關卡 30
overwrite = TRUE 時，R 會自動把撞名的表格刪除，並且寫入新的資料。 如果是overwrite = FALSE時撞名，R 就會出錯。 這個部分若是讓同學測試的話，swirl會跳掉，所以就只能先帶過了。
關卡 31
append = TRUE時，R 會把我們要寫入的資料，接在撞名的表格之下。 這等介紹完dbReadTable之後，我們再試試。
關卡 32
接下來，透過dbReadTable來從表格中取出資料。 這個指令只需要兩個參數：connection和name。 請同學輸入：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
以上講解的dbReadTable和dbWriteTable，都是一次將所有資料倒進去和拿出來的方法，雖然方便，但當數據量很大的時候，一次可能只需要一部分的資料。 進階的使用者可能需要更多關聯式資料庫所提供的功能。
關卡 44
DBI界面中，我們可以利用dbGetQuery和dbSendQuery來執行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
dbSendQuery和fetch的技巧在使用R 處理「大量資料」，是非常有用的。 尤其是當資料量「超過記憶體」時，運用資料庫可以很有效率地優化硬碟處理資料的效能。 此時，如果我們的演算法不需要一次看到所有的資料，那dbSendQuery和fetch就非常有用，它們可以避免我們發生滑鼠不能移動的悲劇。
關卡 52
上述的課程中，我們並沒有講解SQL expression。 同學如果有興趣的話，可以再找SQL相關的資料學習。 這部份的細節，留待後續dplyr套件的系列課程中，再跟同學介紹。 我們這裡想要強調的是所有對資料庫的基礎操作，以及DBI所提供一些輔助函數，例如：dbGetQuery、dbSendQuery等。
關卡 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)
# 測試程式將會檢查這個資料庫內的資料