1 Objective

Ini adalah bagian ketiga 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 MySQL. Anda dapat mebaca posting berikutnya tentang penggunaan R dan PostgreSQL.

2 MySQL

MySQL adalah sistem manajemen database relasional open source (RDBMS) dengan client-server model. Sedangkan RDBMS merupakan software untuk membuat dan mengelola database berdasarkan pada model relasional. Sebelum dibahas lebih lanjut, ada baiknya bagi kita untuk mengetahui sejarah singkat MySQL. MySQL dibaca MY-ES-KYOO-EL [maɪˌɛsˌkjuːˈɛl]. Beberapa orang bahkan membaca MySQL seperti sedang menyebutkan “my sequel”. MySQL AB, sebuah perusahaan asal Swedia, menjadi yang pertama dalam mengembangkan MySQL di tahun 1994. Hak kepemilikan MySQL kemudian diambil secara menyeluruh oleh perusahaan teknologi Amerika Serikat, Sun Microsystems, ketika mereka membeli MySQL AB pada tahun 2008. Di tahun 2010, Oracle yang adalah salah satu perusahaan teknologi terbesar di Amerika Serikat mengakuisisi Sun Microsystems. Semenjak itulah, MySQL sepenuhnya dimiliki oleh Oracle.

Sumber

Disini tidak akan membahas cara install MySQL. Jika Anda belum mempunyai MySQL yang terinstall di PC, silahkan baca disini.

library(RMySQL)
con <- dbConnect(drv = MySQL(), 
                 host = "localhost", 
                 dbname = "rdb", 
                 user = "user1", 
                 password = "L@t1h4n!")
con
<MySQLConnection:0,0>

Perbedaan antara membuat koneksi dengan MySQL dan SQLite adalah parameter yang diperlukan. Untuk koneksi ke SQLite menggunakan driver RSQLite::SQLite(), sedangkan untuk koneksi ke MySQL menggunakan RMySQL::MySQL(). Argumen berikutnya di SQLite adalah loaksi dan nama file database SQLite. Pada koneksi R dengan MySQL dibutuhkan host (IP address), dbname (nama database), user (username untuk akses database), dan password (password untuk akses database).

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

dbListTables(con)

character(0)

3 Export Data

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

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).

5 Schema

Jika database yang diakses memiliki schema, maka sangat disarankan menggunakan package dbplyr dan dplyr. Pada package dbplyr terdapat fungsi in_schema() yang dapat mengakomodir kebutuhan akses tabel pada database dengan schema.

library(dbplyr)
library(dplyr)

con %>% 
  tbl(in_schema("schema_name", "table_name")) %>% 
  collect()
LS0tDQp0aXRsZTogICJDb25uZWN0IERhdGFiYXNlcyBhbmQgUiAtIE15U1FMIg0KYXV0aG9yOiAiQnkgQWVwIEhpZGF5YXR1bG9oIg0KZGF0ZTogICAiMjAxOSBBdWd1c3QgMjIiDQpvdXRwdXQ6IA0KICBodG1sX25vdGVib29rOg0KICAgIG51bWJlcl9zZWN0aW9uczogeWVzDQogICAgdGhlbWU6IHNwYWNlbGFiDQogICAgZGZfcHJpbnQ6IHBhZ2VkDQogICAgdG9jOiB5ZXMNCiAgICB0b2NfZGVwdGg6IDQNCiAgICB0b2NfZmxvYXQ6IHRydWUNCi0tLQ0KDQo8c3R5bGUgdHlwZT0idGV4dC9jc3MiPg0KDQpib2R5eyAvKiBOb3JtYWwgICovDQogICAgICBmb250LXNpemU6IDE0cHg7DQogIH0NCnRkIHsgIC8qIFRhYmxlICAqLw0KICBmb250LXNpemU6IDEycHg7DQp9DQpoMS50aXRsZSB7DQogIGZvbnQtc2l6ZTogMzhweDsNCiAgY29sb3I6IGxpZ2h0Ymx1ZTsNCiAgZm9udC13ZWlnaHQ6IGJvbGQ7DQp9DQpoMSB7IC8qIEhlYWRlciAxICovDQogIGZvbnQtc2l6ZTogMjRweDsNCiAgY29sb3I6IERhcmtCbHVlOw0KfQ0KaDIgeyAvKiBIZWFkZXIgMiAqLw0KICBmb250LXNpemU6IDIwcHg7DQogIGNvbG9yOiBEYXJrQmx1ZTsNCn0NCmgzIHsgLyogSGVhZGVyIDMgKi8NCiAgZm9udC1zaXplOiAxNnB4Ow0KIyAgZm9udC1mYW1pbHk6ICJUaW1lcyBOZXcgUm9tYW4iLCBUaW1lcywgc2VyaWY7DQogIGNvbG9yOiBEYXJrQmx1ZTsNCn0NCmg0IHsgLyogSGVhZGVyIDQgKi8NCiAgZm9udC1zaXplOiAxNHB4Ow0KICBjb2xvcjogRGFya0JsdWU7DQp9DQpjb2RlLnJ7IC8qIENvZGUgYmxvY2sgKi8NCiAgICBmb250LXNpemU6IDEycHg7DQp9DQpwcmUgeyAvKiBDb2RlIGJsb2NrIC0gZGV0ZXJtaW5lcyBjb2RlIHNwYWNpbmcgYmV0d2VlbiBsaW5lcyAqLw0KICAgIGZvbnQtc2l6ZTogMTJweDsNCn0NCjwvc3R5bGU+DQoNCg0KYGBge3Igc2V0dXAsIGluY2x1ZGU9RkFMU0V9DQoja25pdHI6Om9wdHNfY2h1bmskc2V0KGVjaG8gPSBUUlVFKQ0Ka25pdHI6Om9wdHNfY2h1bmskc2V0KGVjaG89VFJVRSwgcmVzdWx0cz0naG9sZCcsIHdhcm5pbmc9RkFMU0UsIGZpZy5zaG93PSdob2xkJywgbWVzc2FnZT1GQUxTRSkgDQpvcHRpb25zKHNjaXBlbiA9IDk5KQ0KYGBgDQoNCg0KIyBPYmplY3RpdmUNCg0KSW5pIGFkYWxhaCBiYWdpYW4ga2V0aWdhIGRhcmkgYmViZXJhcGEgdHVsaXNhbiB0ZW50YW5nIGRhdGFiYXNlIGRhbiBSLiBEYWxhbSByYW5na2FpYW4gdHVsaXNhbiBpbmkgYWthbiBtZW1iYWhhcyBjYXJhIG1lbWJ1YXQga29uZWtzaSBSIGRhbiBiZWJlcmFwYSBkYXRhYmFzZSB5YW5nIGJhbnlhayBkaWd1bmFrYW4sIGltcG9ydCBkYW4gZXhwb3J0IGRhdGEuIERhdGFiYXNlIHlhbmcgYWthbiBkaWJhaGFzICh5YW5nIHBlcm5haCBkaWd1bmFrYW4gb2xlaCBwZW51bGlzIDpQKSBhZGFsYWggTXMgRXhjZWwgKEV4Y2VsIHNlYmFnYWkgZGF0YWJhc2U/IFlhISksIFNRTGl0ZSwgTXlTUUwsIFBvc3RncmVTUUwsIGRhbiBNcyBTUUwgU2VydmVyICh2aWEgT0RCQykuIFBhZGEgdHVsaXNhbiBpbmkgYWthbiBkaWJhaGFzIHBlbmdndW5hYW4gTXlTUUwuIEFuZGEgZGFwYXQgbWViYWNhIHBvc3RpbmcgYmVyaWt1dG55YSB0ZW50YW5nIHBlbmdndW5hYW4gPGEgaHJlZj0iaHR0cDovL3JwdWJzLmNvbS9hZXBoaWRheWF0dWxvaC9yZGItcG9zdGdyZSIgdGFyZ2V0PSJfYmxhbmsiPlIgZGFuIFBvc3RncmVTUUw8L2E+Lg0KDQojIE15U1FMDQoNCj4gTXlTUUwgYWRhbGFoIHNpc3RlbSBtYW5hamVtZW4gZGF0YWJhc2UgcmVsYXNpb25hbCBvcGVuIHNvdXJjZSAoUkRCTVMpIGRlbmdhbiBjbGllbnQtc2VydmVyIG1vZGVsLiBTZWRhbmdrYW4gUkRCTVMgbWVydXBha2FuIHNvZnR3YXJlIHVudHVrIG1lbWJ1YXQgZGFuIG1lbmdlbG9sYSBkYXRhYmFzZSBiZXJkYXNhcmthbiBwYWRhIG1vZGVsIHJlbGFzaW9uYWwuIFNlYmVsdW0gZGliYWhhcyBsZWJpaCBsYW5qdXQsIGFkYSBiYWlrbnlhIGJhZ2kga2l0YSB1bnR1ayBtZW5nZXRhaHVpIHNlamFyYWggc2luZ2thdCBNeVNRTC4gTXlTUUwgZGliYWNhIE1ZLUVTLUtZT08tRUwgW21hyarLjMmbc8uMa2p1y5DLiMmbbF0uIEJlYmVyYXBhIG9yYW5nIGJhaGthbiBtZW1iYWNhIE15U1FMIHNlcGVydGkgc2VkYW5nIG1lbnllYnV0a2FuICJteSBzZXF1ZWwiLiBNeVNRTCBBQiwgc2VidWFoIHBlcnVzYWhhYW4gYXNhbCBTd2VkaWEsIG1lbmphZGkgeWFuZyBwZXJ0YW1hIGRhbGFtIG1lbmdlbWJhbmdrYW4gTXlTUUwgZGkgdGFodW4gMTk5NC4gSGFrIGtlcGVtaWxpa2FuIE15U1FMIGtlbXVkaWFuIGRpYW1iaWwgc2VjYXJhIG1lbnllbHVydWggb2xlaCBwZXJ1c2FoYWFuIHRla25vbG9naSBBbWVyaWthIFNlcmlrYXQsIFN1biBNaWNyb3N5c3RlbXMsIGtldGlrYSBtZXJla2EgbWVtYmVsaSBNeVNRTCBBQiBwYWRhIHRhaHVuIDIwMDguIERpIHRhaHVuIDIwMTAsIE9yYWNsZSB5YW5nIGFkYWxhaCBzYWxhaCBzYXR1IHBlcnVzYWhhYW4gdGVrbm9sb2dpIHRlcmJlc2FyIGRpIEFtZXJpa2EgU2VyaWthdCBtZW5nYWt1aXNpc2kgU3VuIE1pY3Jvc3lzdGVtcy4gU2VtZW5qYWsgaXR1bGFoLCBNeVNRTCBzZXBlbnVobnlhIGRpbWlsaWtpIG9sZWggT3JhY2xlLiANCg0KPGEgaHJlZiA9ICJodHRwczovL3d3dy5ob3N0aW5nZXIuY28uaWQvdHV0b3JpYWwvYXBhLWl0dS1teXNxbC8iPlN1bWJlcjwvYT4NCg0KRGlzaW5pIHRpZGFrIGFrYW4gbWVtYmFoYXMgY2FyYSBpbnN0YWxsIE15U1FMLiBKaWthIEFuZGEgYmVsdW0gbWVtcHVueWFpIE15U1FMIHlhbmcgdGVyaW5zdGFsbCBkaSBQQywgc2lsYWhrYW4gYmFjYSBbZGlzaW5pXShodHRwczovL2Rldi5teXNxbC5jb20vZG9jL215c3FsLWluc3RhbGxhdGlvbi1leGNlcnB0LzUuNS9lbi93aW5kb3dzLWluc3RhbGxhdGlvbi5odG1sKS4NCg0KYGBge3J9DQpsaWJyYXJ5KFJNeVNRTCkNCmNvbiA8LSBkYkNvbm5lY3QoZHJ2ID0gTXlTUUwoKSwgDQogICAgICAgICAgICAgICAgIGhvc3QgPSAibG9jYWxob3N0IiwgDQogICAgICAgICAgICAgICAgIGRibmFtZSA9ICJyZGIiLCANCiAgICAgICAgICAgICAgICAgdXNlciA9ICJ1c2VyMSIsIA0KICAgICAgICAgICAgICAgICBwYXNzd29yZCA9ICJMQHQxaDRuISIpDQpjb24NCmBgYA0KDQpQZXJiZWRhYW4gYW50YXJhIG1lbWJ1YXQga29uZWtzaSBkZW5nYW4gTXlTUUwgZGFuIDxhIGhyZWY9Imh0dHA6Ly9ycHVicy5jb20vYWVwaGlkYXlhdHVsb2gvcmRiLXNxbGl0ZSIgdGFyZ2V0PSJfYmxhbmsiPlNRTGl0ZTwvYT4gYWRhbGFoIHBhcmFtZXRlciB5YW5nIGRpcGVybHVrYW4uIFVudHVrIGtvbmVrc2kga2UgU1FMaXRlIG1lbmdndW5ha2FuIGRyaXZlciBgUlNRTGl0ZTo6U1FMaXRlKClgLCBzZWRhbmdrYW4gdW50dWsga29uZWtzaSBrZSBNeVNRTCBtZW5nZ3VuYWthbiBgUk15U1FMOjpNeVNRTCgpYC4gQXJndW1lbiBiZXJpa3V0bnlhIGRpIFNRTGl0ZSBhZGFsYWggbG9ha3NpIGRhbiBuYW1hIGZpbGUgZGF0YWJhc2UgU1FMaXRlLiBQYWRhIGtvbmVrc2kgUiBkZW5nYW4gTXlTUUwgZGlidXR1aGthbiBob3N0ICgqSVAgYWRkcmVzcyopLCBkYm5hbWUgKG5hbWEgZGF0YWJhc2UpLCB1c2VyICh1c2VybmFtZSB1bnR1ayBha3NlcyBkYXRhYmFzZSksIGRhbiBwYXNzd29yZCAocGFzc3dvcmQgdW50dWsgYWtzZXMgZGF0YWJhc2UpLg0KDQpVbnR1ayBtZW5nZXRhaHVpIHRhYmVsIGFwYSBzYWphIHlhbmcgdGVyZGFwYXQgZGkgZGF0YWJhc2UgZ3VuYWthbiBmdW5nc2kgYGRiTGlzdFRhYmxlcygpYC4NCg0KYGBge3J9DQpkYkxpc3RUYWJsZXMoY29uKQ0KYGBgDQoNCiMgRXhwb3J0IERhdGENCg0KR3VuYWthbiBmdW5nc2kgYGRiV3JpdGVUYWJsZSgpYCB1bnR1ayBtZW55aW1wYW4gZGF0YS5mcmFtZSBrZSBkYWxhbSBkYXRhYmFzZS4NCg0KYGBge3J9DQpkYldyaXRlVGFibGUoY29uLCBuYW1lID0gImlyaXMiLCB2YWx1ZSA9IGlyaXMsIG92ZXJ3cml0ZSA9IFRSVUUpDQpkYldyaXRlVGFibGUoY29uLCBuYW1lID0gIm10Y2FycyIsIHZhbHVlID0gbXRjYXJzLCBvdmVyd3JpdGUgPSBUUlVFKQ0KZGJMaXN0VGFibGVzKGNvbikNCmBgYA0KDQpBcmd1bWVuIGBuYW1lID0gYCBhZGFsYWggbmFtYSB0YWJlbCBkaSBkYWxhbSBkYXRhYmFzZSBzZXRlbGFoIGRpc2ltcGFuLiBBcmd1bWVuIGB2YWx1ZSA9IGAgYWRhbGFoIG9iamVrIGRhdGEuZnJhbWUgeWFuZyBha2FuIGRpZWtzcG9yIGtlIGRhdGFiYXNlLiBEYXRhLmZyYW1lIGBpcmlzYCBkYW4gYG10Y2Fyc2Agc2VrYXJhbmcgc3VkYWggYWRhIGRpIGRhbGFtIGRhdGFiYXNlLg0KDQojIEltcG9ydCBEYXRhDQoNClVudHVrIG1lbmdha3NlcyBkYXRhIGRhcmkgZGF0YWJhc2UgU1FMaXRlIGRhcGF0IGRpbGFrdWthbiBkZW5nYW4gMiBjYXJhLg0KDQojIyBUYWtlIG9uZSB0YWJsZSBhcyBhIHdob2xlDQoNCiogTWVuZ2d1bmFrYW4gZnVuZ3NpIGBkYlJlYWRUYWJsZSgpYA0KDQpgYGB7cn0NCmlyaXNfZGIgPC0gZGJSZWFkVGFibGUoY29uLCBuYW1lID0gImlyaXMiKQ0KaGVhZChpcmlzX2RiKQ0KYGBgDQoNCiogTWVuZ2d1bmFrYW4gcGFja2FnZSBgZHBseXJgDQoNCmBgYHtyfQ0KbGlicmFyeShkYnBseXIpDQpsaWJyYXJ5KGRwbHlyKQ0KaXJpc19kcGx5ciA8LSBjb24gJT4lIHRibCgiaXJpcyIpICU+JSBjb2xsZWN0KCkNCmhlYWQoaXJpc19kcGx5cikNCmBgYA0KDQoNCiMjIEltcG9ydCB1c2luZyBRdWVyeQ0KDQoqIE1lbmdndW5ha2FuIGZ1bmdzaSBgZGJHZXRRdWVyeSgpYA0KDQpgYGB7cn0NCm1jYXJzX2RiIDwtIGRiR2V0UXVlcnkoY29uLCBzdGF0ZW1lbnQgPSAiU0VMRUNUICogRlJPTSBtdGNhcnMgV0hFUkUgYW0gPSAxOyIpDQpoZWFkKG1jYXJzX2RiKQ0KYGBgDQoNCiogTWVuZ2d1bmFrYW4gcGFja2FnZSBgZHBseXJgDQoNCmBgYHtyfQ0KbWNhcnNfcXVlcnkgPC0gY29uICU+JSANCiAgdGJsKCJtdGNhcnMiKSAlPiUgDQogIGZpbHRlcihhbSA9PSAxKQ0KDQpzaG93X3F1ZXJ5KG1jYXJzX3F1ZXJ5KQ0KbWNhcnNfcXVlcnkNCmBgYA0KDQpTY3JpcHQgZGkgYXRhcyB0aWRhayBsYW5nc3VuZyBtZW55aW1wYW4gdGFiZWwgZGFyaSBkYXRhYmFzZSBzZWJhZ2FpIGRhdGEuZnJhbWUgZGkgUiwgbWVsYWlua2FuIGhhbnlhIHNlYmFnYWkgdGFiZWwgc2VtZW50YXJhIGRpIG1lbW9yeS4NCg0KYGBge3J9DQptY2Fyc19kcGx5ciA8LSBtY2Fyc19xdWVyeSAlPiUgDQogIGNvbGxlY3QoKQ0KDQpoZWFkKG1jYXJzX2RwbHlyKQ0KYGBgDQoNClNldGVsYWggbWVuZ2d1bmFrYW4gZnVuZ3NpIGBjb2xsZWN0KClgIGJhcnVsYWggZGF0YSB0ZXJzZWJ1dCBkaXNpbXBhbiBkaSBSIHNlYmFnYWkgZGF0YS5mcmFtZSAoKip0aWJibGUqKiBsZWJpaCB0ZXBhdG55YSkuDQoNCiMgU2NoZW1hDQoNCkppa2EgZGF0YWJhc2UgeWFuZyBkaWFrc2VzIG1lbWlsaWtpIHNjaGVtYSwgbWFrYSBzYW5nYXQgZGlzYXJhbmthbiBtZW5nZ3VuYWthbiBwYWNrYWdlIGBkYnBseXJgIGRhbiBgZHBseXJgLiBQYWRhIHBhY2thZ2UgYGRicGx5cmAgdGVyZGFwYXQgZnVuZ3NpIGBpbl9zY2hlbWEoKWAgeWFuZyBkYXBhdCBtZW5nYWtvbW9kaXIga2VidXR1aGFuIGFrc2VzIHRhYmVsIHBhZGEgZGF0YWJhc2UgZGVuZ2FuIHNjaGVtYS4NCg0KYGBge3IgZXZhbD1GQUxTRX0NCmxpYnJhcnkoZGJwbHlyKQ0KbGlicmFyeShkcGx5cikNCg0KY29uICU+JSANCiAgdGJsKGluX3NjaGVtYSgic2NoZW1hX25hbWUiLCAidGFibGVfbmFtZSIpKSAlPiUgDQogIGNvbGxlY3QoKQ0KYGBgDQoNCg==