1 Objective

Ini adalah bagian kedua dari beberapa tulisan tentang database dan R. Dalam rangkaian tulisan ini akan membahas cara membuat koneksi R dan beberapa database yang banyak digunakan, import dan export data. Database yang akan dibahas (yang pernah digunakan oleh penulis :P) adalah Ms Excel (Excel sebagai database? Ya!), SQLite, MySQL, PostgreSQL, dan Ms SQL Server (via ODBC). Pada tulisan ini akan dibahas penggunaan SQLite. Anda dapat mebaca posting berikutnya tentang penggunaan R dan MySQL.

2 SQLite

“SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.” SQLite

SQLite adalah sebuah database yang sangat bagus untuk sebuah database jika ukuran datanya kecil. Data untuk tutorial ini dapat Anda download disini.

library(RSQLite)
con <- dbConnect(SQLite(), dbname = "rdb.sqlite")
con
<SQLiteConnection>
  Path: D:\aephidayatuloh\R\leaRning\Rdb\rdb.sqlite
  Extensions: TRUE

Jika file rdb.sqlite belum ada di working directory Anda maka R otomatis akan membuatnya.

Untuk mengetahui tabel apa saja yang terdapat di database gunakan fungsi dbListTables().

dbListTables(con)
[1] "iris"   "mtcars"

3 Export Data

Gunakan fungsi dbWriteTable() untuk menyimpan data.frame ke dalam database.

dbWriteTable(con, name = "iris", value = iris, overwrite = TRUE)
dbWriteTable(con, name = "mtcars", value = mtcars, overwrite = TRUE)
dbListTables(con)
[1] "iris"   "mtcars"

Argumen name = adalah nama tabel di dalam database setelah disimpan. Argumen value = adalah objek data.frame yang akan diekspor ke database. Data.frame iris dan mtcars sekarang sudah ada di dalam database.

4 Import Data

Untuk mengakses data dari database SQLite dapat dilakukan dengan 2 cara.

4.1 Take one table as a whole

  • Menggunakan fungsi dbReadTable()
iris_db <- dbReadTable(con, name = "iris")
head(iris_db)
  • Menggunakan package dplyr
library(dbplyr)
library(dplyr)
iris_dplyr <- con %>% tbl("iris") %>% collect()
head(iris_dplyr)

4.2 Import using Query

  • Menggunakan fungsi dbGetQuery()
mcars_db <- dbGetQuery(con, statement = "SELECT * FROM mtcars WHERE am = 1;")
head(mcars_db)
  • Menggunakan package dplyr
mcars_query <- con %>% 
  tbl("mtcars") %>% 
  filter(am == 1)

show_query(mcars_query)
<SQL>
SELECT *
FROM `mtcars`
WHERE (`am` = 1.0)
mcars_query

Script di atas tidak langsung menyimpan tabel dari database sebagai data.frame di R, melainkan hanya sebagai tabel sementara di memory.

mcars_dplyr <- mcars_query %>% 
  collect()

head(mcars_dplyr)

Setelah menggunakan fungsi collect() barulah data tersebut disimpan di R sebagai data.frame (tibble lebih tepatnya).

LS0tDQp0aXRsZTogICJDb25uZWN0IERhdGFiYXNlcyBhbmQgUiAtIFNRTGl0ZSINCmF1dGhvcjogIkJ5IEFlcCBIaWRheWF0dWxvaCINCmRhdGU6ICAgIjIwMTkgQXVndXN0IDE3Ig0Kb3V0cHV0OiANCiAgaHRtbF9ub3RlYm9vazoNCiAgICBudW1iZXJfc2VjdGlvbnM6IHllcw0KICAgIHRoZW1lOiBzcGFjZWxhYg0KICAgIGRmX3ByaW50OiBwYWdlZA0KICAgIHRvYzogeWVzDQogICAgdG9jX2RlcHRoOiA0DQogICAgdG9jX2Zsb2F0OiB0cnVlDQotLS0NCg0KPHN0eWxlIHR5cGU9InRleHQvY3NzIj4NCg0KYm9keXsgLyogTm9ybWFsICAqLw0KICAgICAgZm9udC1zaXplOiAxNHB4Ow0KICB9DQp0ZCB7ICAvKiBUYWJsZSAgKi8NCiAgZm9udC1zaXplOiAxMnB4Ow0KfQ0KaDEudGl0bGUgew0KICBmb250LXNpemU6IDM4cHg7DQogIGNvbG9yOiBsaWdodGJsdWU7DQogIGZvbnQtd2VpZ2h0OiBib2xkOw0KfQ0KaDEgeyAvKiBIZWFkZXIgMSAqLw0KICBmb250LXNpemU6IDI0cHg7DQogIGNvbG9yOiBEYXJrQmx1ZTsNCn0NCmgyIHsgLyogSGVhZGVyIDIgKi8NCiAgZm9udC1zaXplOiAyMHB4Ow0KICBjb2xvcjogRGFya0JsdWU7DQp9DQpoMyB7IC8qIEhlYWRlciAzICovDQogIGZvbnQtc2l6ZTogMTZweDsNCiMgIGZvbnQtZmFtaWx5OiAiVGltZXMgTmV3IFJvbWFuIiwgVGltZXMsIHNlcmlmOw0KICBjb2xvcjogRGFya0JsdWU7DQp9DQpoNCB7IC8qIEhlYWRlciA0ICovDQogIGZvbnQtc2l6ZTogMTRweDsNCiAgY29sb3I6IERhcmtCbHVlOw0KfQ0KY29kZS5yeyAvKiBDb2RlIGJsb2NrICovDQogICAgZm9udC1zaXplOiAxMnB4Ow0KfQ0KcHJlIHsgLyogQ29kZSBibG9jayAtIGRldGVybWluZXMgY29kZSBzcGFjaW5nIGJldHdlZW4gbGluZXMgKi8NCiAgICBmb250LXNpemU6IDEycHg7DQp9DQo8L3N0eWxlPg0KDQoNCmBgYHtyIHNldHVwLCBpbmNsdWRlPUZBTFNFfQ0KI2tuaXRyOjpvcHRzX2NodW5rJHNldChlY2hvID0gVFJVRSkNCmtuaXRyOjpvcHRzX2NodW5rJHNldChlY2hvPVRSVUUsIHJlc3VsdHM9J2hvbGQnLCB3YXJuaW5nPUZBTFNFLCBmaWcuc2hvdz0naG9sZCcsIG1lc3NhZ2U9RkFMU0UpIA0Kb3B0aW9ucyhzY2lwZW4gPSA5OSkNCmBgYA0KDQoNCiMgT2JqZWN0aXZlDQoNCkluaSBhZGFsYWggYmFnaWFuIGtlZHVhIGRhcmkgYmViZXJhcGEgdHVsaXNhbiB0ZW50YW5nIGRhdGFiYXNlIGRhbiBSLiBEYWxhbSByYW5na2FpYW4gdHVsaXNhbiBpbmkgYWthbiBtZW1iYWhhcyBjYXJhIG1lbWJ1YXQga29uZWtzaSBSIGRhbiBiZWJlcmFwYSBkYXRhYmFzZSB5YW5nIGJhbnlhayBkaWd1bmFrYW4sIGltcG9ydCBkYW4gZXhwb3J0IGRhdGEuIERhdGFiYXNlIHlhbmcgYWthbiBkaWJhaGFzICh5YW5nIHBlcm5haCBkaWd1bmFrYW4gb2xlaCBwZW51bGlzIDpQKSBhZGFsYWggTXMgRXhjZWwgKEV4Y2VsIHNlYmFnYWkgZGF0YWJhc2U/IFlhISksIFNRTGl0ZSwgTXlTUUwsIFBvc3RncmVTUUwsIGRhbiBNcyBTUUwgU2VydmVyICh2aWEgT0RCQykuIFBhZGEgdHVsaXNhbiBpbmkgYWthbiBkaWJhaGFzIHBlbmdndW5hYW4gU1FMaXRlLiBBbmRhIGRhcGF0IG1lYmFjYSBwb3N0aW5nIGJlcmlrdXRueWEgdGVudGFuZyBwZW5nZ3VuYWFuIDxhIGhyZWY9Imh0dHA6Ly9ycHVicy5jb20vYWVwaGlkYXlhdHVsb2gvcmRiLW15c3FsIiB0YXJnZXQ9Il9ibGFuayI+UiBkYW4gTXlTUUw8L2E+Lg0KDQojIFNRTGl0ZQ0KDQo+IlNRTGl0ZSBpcyBhIEMtbGFuZ3VhZ2UgbGlicmFyeSB0aGF0IGltcGxlbWVudHMgYSBzbWFsbCwgZmFzdCwgc2VsZi1jb250YWluZWQsIGhpZ2gtcmVsaWFiaWxpdHksIGZ1bGwtZmVhdHVyZWQsIFNRTCBkYXRhYmFzZSBlbmdpbmUuIFNRTGl0ZSBpcyB0aGUgbW9zdCB1c2VkIGRhdGFiYXNlIGVuZ2luZSBpbiB0aGUgd29ybGQuIFNRTGl0ZSBpcyBidWlsdCBpbnRvIGFsbCBtb2JpbGUgcGhvbmVzIGFuZCBtb3N0IGNvbXB1dGVycyBhbmQgY29tZXMgYnVuZGxlZCBpbnNpZGUgY291bnRsZXNzIG90aGVyIGFwcGxpY2F0aW9ucyB0aGF0IHBlb3BsZSB1c2UgZXZlcnkgZGF5LiIgW1NRTGl0ZV0oaHR0cHM6Ly93d3cuc3FsaXRlLm9yZy9pbmRleC5odG1sKQ0KDQpTUUxpdGUgYWRhbGFoIHNlYnVhaCBkYXRhYmFzZSB5YW5nIHNhbmdhdCBiYWd1cyB1bnR1ayBzZWJ1YWggZGF0YWJhc2UgamlrYSB1a3VyYW4gZGF0YW55YSBrZWNpbC4gRGF0YSB1bnR1ayB0dXRvcmlhbCBpbmkgZGFwYXQgQW5kYSBkb3dubG9hZCBbZGlzaW5pXShodHRwczovL2dpdGh1Yi5jb20vYWVwaGlkYXlhdHVsb2gvZGF0YXNldHMvYmxvYi9tYXN0ZXIvWExTWGRiLnhsc3gpLg0KDQpgYGB7cn0NCmxpYnJhcnkoUlNRTGl0ZSkNCmNvbiA8LSBkYkNvbm5lY3QoU1FMaXRlKCksIGRibmFtZSA9ICJyZGIuc3FsaXRlIikNCmNvbg0KYGBgDQoNCkppa2EgZmlsZSBgcmRiLnNxbGl0ZWAgYmVsdW0gYWRhIGRpIF93b3JraW5nIGRpcmVjdG9yeV8gQW5kYSBtYWthIFIgb3RvbWF0aXMgYWthbiBtZW1idWF0bnlhLg0KDQpVbnR1ayBtZW5nZXRhaHVpIHRhYmVsIGFwYSBzYWphIHlhbmcgdGVyZGFwYXQgZGkgZGF0YWJhc2UgZ3VuYWthbiBmdW5nc2kgYGRiTGlzdFRhYmxlcygpYC4NCg0KYGBge3J9DQpkYkxpc3RUYWJsZXMoY29uKQ0KYGBgDQoNCiMgRXhwb3J0IERhdGENCg0KR3VuYWthbiBmdW5nc2kgYGRiV3JpdGVUYWJsZSgpYCB1bnR1ayBtZW55aW1wYW4gZGF0YS5mcmFtZSBrZSBkYWxhbSBkYXRhYmFzZS4NCg0KYGBge3J9DQpkYldyaXRlVGFibGUoY29uLCBuYW1lID0gImlyaXMiLCB2YWx1ZSA9IGlyaXMsIG92ZXJ3cml0ZSA9IFRSVUUpDQpkYldyaXRlVGFibGUoY29uLCBuYW1lID0gIm10Y2FycyIsIHZhbHVlID0gbXRjYXJzLCBvdmVyd3JpdGUgPSBUUlVFKQ0KZGJMaXN0VGFibGVzKGNvbikNCmBgYA0KDQpBcmd1bWVuIGBuYW1lID0gYCBhZGFsYWggbmFtYSB0YWJlbCBkaSBkYWxhbSBkYXRhYmFzZSBzZXRlbGFoIGRpc2ltcGFuLiBBcmd1bWVuIGB2YWx1ZSA9IGAgYWRhbGFoIG9iamVrIGRhdGEuZnJhbWUgeWFuZyBha2FuIGRpZWtzcG9yIGtlIGRhdGFiYXNlLiBEYXRhLmZyYW1lIGBpcmlzYCBkYW4gYG10Y2Fyc2Agc2VrYXJhbmcgc3VkYWggYWRhIGRpIGRhbGFtIGRhdGFiYXNlLg0KDQojIEltcG9ydCBEYXRhDQoNClVudHVrIG1lbmdha3NlcyBkYXRhIGRhcmkgZGF0YWJhc2UgU1FMaXRlIGRhcGF0IGRpbGFrdWthbiBkZW5nYW4gMiBjYXJhLg0KDQojIyBUYWtlIG9uZSB0YWJsZSBhcyBhIHdob2xlDQoNCiogTWVuZ2d1bmFrYW4gZnVuZ3NpIGBkYlJlYWRUYWJsZSgpYA0KDQpgYGB7cn0NCmlyaXNfZGIgPC0gZGJSZWFkVGFibGUoY29uLCBuYW1lID0gImlyaXMiKQ0KaGVhZChpcmlzX2RiKQ0KYGBgDQoNCiogTWVuZ2d1bmFrYW4gcGFja2FnZSBgZHBseXJgDQoNCmBgYHtyfQ0KbGlicmFyeShkYnBseXIpDQpsaWJyYXJ5KGRwbHlyKQ0KaXJpc19kcGx5ciA8LSBjb24gJT4lIHRibCgiaXJpcyIpICU+JSBjb2xsZWN0KCkNCmhlYWQoaXJpc19kcGx5cikNCmBgYA0KDQoNCiMjIEltcG9ydCB1c2luZyBRdWVyeQ0KDQoqIE1lbmdndW5ha2FuIGZ1bmdzaSBgZGJHZXRRdWVyeSgpYA0KDQpgYGB7cn0NCm1jYXJzX2RiIDwtIGRiR2V0UXVlcnkoY29uLCBzdGF0ZW1lbnQgPSAiU0VMRUNUICogRlJPTSBtdGNhcnMgV0hFUkUgYW0gPSAxOyIpDQpoZWFkKG1jYXJzX2RiKQ0KYGBgDQoNCiogTWVuZ2d1bmFrYW4gcGFja2FnZSBgZHBseXJgDQoNCmBgYHtyfQ0KbWNhcnNfcXVlcnkgPC0gY29uICU+JSANCiAgdGJsKCJtdGNhcnMiKSAlPiUgDQogIGZpbHRlcihhbSA9PSAxKQ0KDQpzaG93X3F1ZXJ5KG1jYXJzX3F1ZXJ5KQ0KbWNhcnNfcXVlcnkNCmBgYA0KDQpTY3JpcHQgZGkgYXRhcyB0aWRhayBsYW5nc3VuZyBtZW55aW1wYW4gdGFiZWwgZGFyaSBkYXRhYmFzZSBzZWJhZ2FpIGRhdGEuZnJhbWUgZGkgUiwgbWVsYWlua2FuIGhhbnlhIHNlYmFnYWkgdGFiZWwgc2VtZW50YXJhIGRpIG1lbW9yeS4NCg0KYGBge3J9DQptY2Fyc19kcGx5ciA8LSBtY2Fyc19xdWVyeSAlPiUgDQogIGNvbGxlY3QoKQ0KDQpoZWFkKG1jYXJzX2RwbHlyKQ0KYGBgDQoNClNldGVsYWggbWVuZ2d1bmFrYW4gZnVuZ3NpIGBjb2xsZWN0KClgIGJhcnVsYWggZGF0YSB0ZXJzZWJ1dCBkaXNpbXBhbiBkaSBSIHNlYmFnYWkgZGF0YS5mcmFtZSAoKip0aWJibGUqKiBsZWJpaCB0ZXBhdG55YSkuDQo=