使用R dplyr串接sqlite資料庫

最近十年R在很多方面都有進步,其中一個就是加強跟資料庫的串接方式,DBI就是其中一個奠基於Relational Database的對接方式,觀看R Special Interest Group on Databases名單:Hadley Wickham, Kirill Müller,都是頗為厲害的高手。

當然,當資料量還能塞在記憶體裡時,用資料庫的方式反而會浪費儲存空間,操作速度也不會過快,但當資料量大過於記憶體可以支撐,或是有一些同步資料等的需求時,利用R和資料庫的連接變很棒!

其中dplyr在這方面就默默做得超棒的,大多數人都知道dplyr對於資料的處理很方便,可以快速做select, filter, mutate, summarise等,但另一方面更棒的地方是他也能很好的跟資料庫對街,sqlite算是最簡單可以入手的,可以很快速的跟現有的工作流程整合在一起。

目前dplyr支持sqlite, mysql, postgresql這三大資料庫類型,基本上很夠用。他的目的是減少在R和SQL這兩類語言間換來換去的麻煩,另一方面,設計一個機制讓你可以安全地在R裡面用dplyr操作資料的verse轉換成SQL,所以相對來說可以轉換較順利,另一方面,他會有一個預算處理的機制(通常處理前幾行),所以可以簡單的預看自己的操作,等確定要撈資料的方式在執行這些query。

最主要提供的功能可以分成三大類:
1. 基礎database的串接(下面的代碼)
2. 使用dplyr的語法來進行sql query
3. 效調與資料庫的query:collect, compute, collapse

# 創建一個新的資料庫
my_db <- src_sqlite("Test.sqlite", create=T)
# 連接現有的資料庫
my_db <- src_sqlite("OldeDb.sqlite", create=F)

# 查看資料庫裡有哪些tables
src_tbls(my_db)

# 形成一種query status,沒有實際查看全部的資料
query <- tbl(my_db, "table_1")
# 實際查找資料庫,回傳資料到R裡面(利用collect)
query.result <- tbl(my_db, "table_1") %>% collect()
# 將R裡面的data.frame塞到資料庫裡成為table
copy_to(my_db, rdataframe, name="rtable", temporary=F)

#建立一個query物件
flights_sqlite <- tbl(nycflights13_sqlite(), "flights") #也可以直接使用sql的語法 tbl(my_db, sql("SELECT * FROM flights")) #奠基在dplyr語法來進行query select(flights_sqlite, year:day, dep_delay, arr_delay) filter(flights_sqlite, dep_delay > 240)
arrange(flights_sqlite, year, month, day)
mutate(flights_sqlite, speed = air_time / distance)
summarise(flights_sqlite, delay = mean(dep_time))

閱讀資料:
1. How to Use DBI: Connecting to Databases with R
2. DBI: R Database Interface
3. A Common Database InterfaceA Common Database Interface
4. Databases and dplyrDatabases and dplyr

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

您的留言將使用 WordPress.com 帳號。 登出 / 變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 / 變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 / 變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 / 變更 )

連結到 %s