R學習筆記 - 資料工程篇(四) Database

#註: 本篇筆記諸多內容直接紀錄課程原文，而本文也僅整理資訊之用途。
課程連結 : R語言翻轉教室
本次課程連結

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

本次介紹如何使用R 語言透過DBI和其系列套件為例，操作關聯式資料庫。而這裡我們以SQLite這套精巧的關聯式資料庫系統為例。

值得注意的是，如果直接用R大量的撈取線上資料庫，是有可能影響資料庫的效能的。所以如果在實務使用時，最好是先知會與請教負責資料庫系統的同仁。

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

主要使用R套件 :　RSQLite
它是R與SQLite資料庫之間的橋樑。專業的術語叫：「客戶端」（Client）


資料庫基本操作介紹

1.資料庫連線
1.1 dbDriver("SQLite") : 取得連接SQLite 資料庫的方式
1.2 db  = dbConnect(drv, db_path)
1.3 dbDisconnect(db) : 中斷連線
drv = 連接方式
db_path = 資料庫的檔案
db = 這個資料庫在R 的代理人，通常都要把`db`這個物件當成函數的參數之一。在DBI的文件中會把`db`這種角色的物件記載為：`connection`

2.寫入資料
2.1 dbWriteTable() :  把數據寫入SQL 資料庫之中

#範例
dbWriteTable(db, "lvr_land2", lvr_land)
db = 這個資料庫在R 的代理人
lvr_land2＝　SQLite資料庫的表格
lvr_land　＝資料框　

#查詢如何將資料框寫入資料庫的語法:　
help("dbWriteTable,SQLiteConnection,character,data.frame-method")

R 的大部分的資料庫套件，在`dbWriteTable`函數中都會提供`append`和`overwrite`兩個參數。`overwrite = TRUE` 時，R 會自動把撞名的表格刪除，並且寫入新的資料。`append = TRUE`時，R會把我們要寫入的資料，接在撞名的表格之下。


3.讀取資料

3.1 dbReadTable(db, "lvr_land2") : 讀取名為lvr_land2的資料庫表格
3.2 dbListTables(db): 可以列出目前已經以db連接方式存在於資料庫的表格。
3.3 all.equal() : 檢視兩資料框是否一致，主要用於驗證資料
型態改變這件事情，其實是可大可小的。在大部分的狀況下，例如建立統計模型、製作圖表，factor、字串（character）的互換並不會帶來太大個困擾。但是在某些很不常見的狀況下（如`strsplit`在遇到factor的時候會出錯），可能會導致程式出錯。

#進階讀取資料方式: 透過下列函數直接執行SQL語句
3.4 dbGetQuery():
會解析SQLexpression，並且依照指令進行資料庫的操作後，再把結果直接回傳給R。
3.5 dbSendQuery() :
#範例
rs <- dbSendQuery(db, "SELECT * FROM iris")

`dbSendQuery`函數則會先把結果儲存於rs物件中，再由使用者一段一段的取出。我們可以利用`fetch`方法，一次拿一點資料出來。請同學試試看：`fetch(rs, 1)`每次執行`fetch`指令時，R就會「依照順序」把結果一個又一個的回傳給使用者。如果使用者每次想多拿一些資料，則可以使用第二個參數，如：`fetch(rs, 100)`一次取出100筆資料。

#技巧
`dbSendQuery`和`fetch`的技巧在使用R 處理「大量資料」，是非常有用的。尤其是當資料量「超過記憶體」時，運用資料庫來優化在硬碟上處理資料的效能，是很有效率的方式。

3.6 dbClearResult(rs) : 不需要再使用`rs`物件時，就刪除它

5.刪除資料
5.1 dbBegin(db) : 來開啟一個Transaction
5.2 dbRemoveTable(db, "CO2") : 來刪除"CO2"表格
5.3 dbRollback(db) :
把資料庫的狀態復原至我們執行`dbBegin(db)`的時間點
5.4 dbCommit(db) : 讓dbBegin(db)後的所有變更生效。
(跟dbBegin(db)要成對出現)

運用外部資源（如資料庫、如檔案系統）處理資料時，最害怕的狀況是被意外中斷。當我們正在寫入資料庫時，意外的中斷就會帶來以下的困擾。當我們排除意外的原因，要繼續工作時，就會問：目前資料庫的狀態，是「還未寫入」、「寫到一半」還是「寫入完畢」。「還未寫入」的狀況下，我們只要重新執行程式即可。而「寫入完畢」的話就更棒了，我們只要繼續後續的工作。但若是「寫到一半」的狀態，要接著工作就會非常麻煩（可能要改程式碼）。

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

在R 裡面，我們可以利用`dbBegin(db)`來開啟一個Transaction。勿刪檔案時，除了中斷連線，我們也可以主動使用`dbRollback(db)`指令把資料庫的狀態復原至我們執行`dbBegin(db)`的時間點。

#技巧
當我們開始`dbBegin(db)`之後，所有的變更，都要等到我們執行`dbCommit(db)`才會生效。這招在寫入大量數據時，也是很重要的技巧。這個技巧可以保證我們的「資料正確性」。