P3a-Database Queries with R
Database Queries
Beberapa package yang digunakan untuk bekerja dengan database menggunakan R software adalah:
DBIberfungsi memisahkan konektivitas ke DBMS menjadi “front-end” dan “back-end”. Aplikasi hanya menggunakan API front-end yang terbuka. Fasilitas back-end yang berkomunikasi dengan DBMS tertentu (SQLite, MySQL, PostgreSQL, MonetDB, dll.) disediakan oleh driver (package lain) yang dipanggil secara otomatis.dplyrdandbplyrmemiliki SQL back-end yang digeneralisasi untuk bisa bekerja dengan database.odbcmenyediakan antarmuka yang sesuai dengan DBI untuk driver Open Database Connectivity (ODBC). Hal ini memungkinkan koneksi yang efisien dan mudah diatur ke database apa pun dengan driver ODBC yang tersedia, termasuk SQL Server, Oracle, MySQL, PostgreSQL, SQLite, dan lainnya.
Connection to a Database
Kali ini contoh pemanggilan database akan menggunakan contoh data Northwind . Untuk dapat melakukannya, package DBI dan RSQLite perlu di-install terlebih dulu.
install.packages(c("RSQLite", "DBI"), dependencies = TRUE)Selanjutnya kita panggil package yang akan digunakan. Kita dapat memanggil package dplry dan dbplyr, atau bisa dengan hanya memanggil package tidyverse yang mencakup keduanya sekaligus. Selain itu, jangan lupa untuk memanggil package RSQLite dan DBI.
library(tidyverse)## -- Attaching packages --------------------------------------- tidyverse 1.3.0 --## v ggplot2 3.3.3 v purrr 0.3.4
## v tibble 3.0.6 v dplyr 1.0.4
## v tidyr 1.1.2 v stringr 1.4.0
## v readr 1.4.0 v forcats 0.5.1## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()library(RSQLite)
library(DBI)Secara umum, koneksi terhadap database pada R software adalah dengan menggunakan syntax berikut:
DBI::dbConnect(RSQLite::SQLite(), path = ":dbname:")
Fungsi dapat pula dijalankankan tanpa memanggil package dengan perintah library() atau require(), jika syntax yang ditulis menyertakan nama package tempat fungsi tersebut berasal. Penyertaan ini dengan menggunakan tanda ::.
Untuk menjalankan syntax berikut ini, Anda perlu mengunduh Database Northwind_large.sqlite dan menyimpannya pada C:/sqlite/db/
Northwind<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/db/Northwind_large.sqlite")Dapat dilihat di syntax di atas, Anda tidak perlu menggunakan path=, karena C:/sqlite/db/Northwind_large.sqlite langsung didentifikasi langsung sebagai path. Anda akan mempelajari hal seperti ini di Mata Kuliah STA561 - Pemrograman Statistika khususnya di Pertemuan 6 - Pemrograman Fungsi R
class(Northwind)## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"Perhatikan bahwa syntax di atas tidak membaca data ke dalam R seperti halnya fungsi read_csv() melainkan berupa instruksi kepada R untuk mengkoneksikan database SQLite yang terdapat pada file Northwind_large.sqlite.
Selanjutnya, fungsi dbListTables dapat digunakan untuk melihat tables yang terdapat di dalam database.
RSQLite::dbListTables(Northwind)## [1] "Category" "Customer" "CustomerCustomerDemo"
## [4] "CustomerDemographic" "Employee" "EmployeeTerritory"
## [7] "Order" "OrderDetail" "Product"
## [10] "ProductDetails_V" "Region" "Shipper"
## [13] "Supplier" "Territory"Anda bisa melakukan hal yang sama hanya menggunakan syntax dbListTables(Northwind), karena di atas Kita telah memanggil package RSQLite dengan syntax library(RSQLite).
Untuk mengakses tabel pada database dapat digunakan fungsi tbl(database, "table") dari package dplyr. Misalkan, kita ingin melihat isi tabel Product dari database Northwind_large.sqlite. Perlu diperhatikan, Kita sudah memasukkan Northwind_large.sqlite sebagai objek Northwind
product<-dplyr::tbl(Northwind,"Product")
class(product)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"Perhatikan bahwa product merupakan object dengan class tbl yang dapat diperlakukan mirip seperti data.frame.
Berikut adalah isi dari obyek product
product## # Source: table<Product> [?? x 10]
## # Database: sqlite 3.34.1 [C:\sqlite\db\Northwind_large.sqlite]
## Id ProductName SupplierId CategoryId QuantityPerUnit UnitPrice
## <int> <chr> <int> <int> <chr> <dbl>
## 1 1 Chai 1 1 10 boxes x 20 ~ 18
## 2 2 Chang 1 1 24 - 12 oz bot~ 19
## 3 3 Aniseed Sy~ 1 2 12 - 550 ml bo~ 10
## 4 4 Chef Anton~ 2 2 48 - 6 oz jars 22
## 5 5 Chef Anton~ 2 2 36 boxes 21.4
## 6 6 Grandma's ~ 3 2 12 - 8 oz jars 25
## 7 7 Uncle Bob'~ 3 7 12 - 1 lb pkgs. 30
## 8 8 Northwoods~ 3 2 12 - 12 oz jars 40
## 9 9 Mishi Kobe~ 4 6 18 - 500 g pkg~ 97
## 10 10 Ikura 4 8 12 - 200 ml ja~ 31
## # ... with more rows, and 4 more variables: UnitsInStock <int>,
## # UnitsOnOrder <int>, ReorderLevel <int>, Discontinued <int>From dplyr to SQL
Seandainya kita ingin mengetahui minimum stok untuk produk dengan harga di atas $20 untuk setiap supplier. Untuk memperoleh informasi tersebut kita dapat menuliskan syntax berikut ini.
q<-product %>%
filter(UnitPrice>20) %>%
group_by(SupplierId) %>%
summarize(stock=min(UnitsInStock)) %>%
arrange(desc(stock))
q## Warning: Missing values are always removed in SQL.
## Use `MIN(x, na.rm = TRUE)` to silence this warning
## This warning is displayed only once per session.## # Source: lazy query [?? x 2]
## # Database: sqlite 3.34.1 [C:\sqlite\db\Northwind_large.sqlite]
## # Ordered by: desc(stock)
## SupplierId stock
## <int> <int>
## 1 25 115
## 2 9 104
## 3 8 40
## 4 6 35
## 5 4 29
## 6 15 26
## 7 5 22
## 8 26 21
## 9 28 19
## 10 29 17
## # ... with more rowsclass(q)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"Pada beberapa kasus, data yang digunakan bisa saja berukuran sangat besar sehingga lebih efisien jika disimpan dalam bentuk database. Syntax di atas merupakan contoh cara kita memproses data dalam bentuk database dengan cara menggunakan package dplyr untuk menghubungkan syntax pada R dengan data pada SQLite.
dplyr mampu menejermahkan pipeline dengan fungsi show_query() seperti contoh di bawah ini.
dplyr::show_query(q)## <SQL>
## SELECT `SupplierId`, MIN(`UnitsInStock`) AS `stock`
## FROM `Product`
## WHERE (`UnitPrice` > 20.0)
## GROUP BY `SupplierId`
## ORDER BY `stock` DESCSebaliknya, kita dapat menuliskan fungsi sql dengan fungsi tbl dari package dplyr.
tbl(Northwind, sql("SELECT productname, categoryid, unitprice FROM Product ORDER BY productname LIMIT 10"))## # Source: SQL [?? x 3]
## # Database: sqlite 3.34.1 [C:\sqlite\db\Northwind_large.sqlite]
## productname categoryid unitprice
## <chr> <int> <dbl>
## 1 Alice Mutton 6 39
## 2 Aniseed Syrup 2 10
## 3 Boston Crab Meat 8 18.4
## 4 Camembert Pierrot 4 34
## 5 Carnarvon Tigers 8 62.5
## 6 Chai 1 18
## 7 Chang 1 19
## 8 Chartreuse verte 1 18
## 9 Chef Anton's Cajun Seasoning 2 22
## 10 Chef Anton's Gumbo Mix 2 21.4Contoh lainnya, misalkan kita ingin melihat 10 perusahaan pertama dari table Customer.
tbl(Northwind,"Customer") %>%
select(CompanyName) %>%
head(n=10)## # Source: lazy query [?? x 1]
## # Database: sqlite 3.34.1 [C:\sqlite\db\Northwind_large.sqlite]
## CompanyName
## <chr>
## 1 Alfreds Futterkiste
## 2 Ana Trujillo Emparedados y helados
## 3 Antonio Moreno Taquería
## 4 Around the Horn
## 5 Berglunds snabbköp
## 6 Blauer See Delikatessen
## 7 Blondesddsl père et fils
## 8 Bólido Comidas preparadas
## 9 Bon app
## 10 Bottom-Dollar MarketsCombining Multiple Tables with dplyr in R
Untuk mempelajari mengenai penggabungan tables ini, kita akan menggunakan Datasets Band membership dari package dplyr. Datasets ini terdiri dari 3 buah tibble, dengan ukuran data yang kecil, yaitu hanya 3 baris dan 2 kolom.
data("band_members")
band_members## # A tibble: 3 x 2
## name band
## <chr> <chr>
## 1 Mick Stones
## 2 John Beatles
## 3 Paul Beatlesdata("band_instruments")
band_instruments## # A tibble: 3 x 2
## name plays
## <chr> <chr>
## 1 John guitar
## 2 Paul bass
## 3 Keith guitardata("band_instruments2")
band_instruments2## # A tibble: 3 x 2
## artist plays
## <chr> <chr>
## 1 John guitar
## 2 Paul bass
## 3 Keith guitarAgar diperhatikan, band_instruments dan band_instruments2 memiliki data yang sama, namun berbeda pada penamaan kolom nya, band_instruments menggunakan name, yang sama dengan nama kolom di band_members, sedangkan band_instruments2 menggunakan artist
Fungsi yang akan digunakan dalam penggabungan tables ini berasal dari package dplyr.
inner_join()
inner_join() menghasilkan semua baris pada table x yang memiliki kesamaan nilai dengan table y, dan semua kolom dari x dan y.
Misal, Anda hendak menggabungkan band_members dan band_instruments secara inner_join, maka Anda bisa menggunakan syntax berikut ini :
inner_join(band_members,band_instruments)## Joining, by = "name"## # A tibble: 2 x 3
## name band plays
## <chr> <chr> <chr>
## 1 John Beatles guitar
## 2 Paul Beatles bassband_members %>% inner_join(band_instruments)## Joining, by = "name"## # A tibble: 2 x 3
## name band plays
## <chr> <chr> <chr>
## 1 John Beatles guitar
## 2 Paul Beatles bassleft_join()
left_join() menghasilkan semua baris pada table x, dan semua kolom pada x dan y, dimana untuk baris pada x yang tidak memiliki kesamaan dengan y diisi dengan nilai NApada kolom yang baru.
Misal, Anda hendak menggabungkan band_members dan band_instruments secara left_join, maka Anda bisa menggunakan syntax berikut ini :
left_join(band_members,band_instruments)## Joining, by = "name"## # A tibble: 3 x 3
## name band plays
## <chr> <chr> <chr>
## 1 Mick Stones <NA>
## 2 John Beatles guitar
## 3 Paul Beatles bassright_join()
right_join() menghasilkan semua baris pada table y, dan semua kolom pada x dan y, dimana untuk baris pada y yang tidak memiliki kesamaan dengan x akan diisi dengan nilai NA pada kolom yang baru.
Misal, Anda hendak menggabungkan band_members dan band_instruments secara right_join`, maka Anda bisa menggunakan syntax berikut ini :
right_join(band_members,band_instruments)## Joining, by = "name"## # A tibble: 3 x 3
## name band plays
## <chr> <chr> <chr>
## 1 John Beatles guitar
## 2 Paul Beatles bass
## 3 Keith <NA> guitarfull_join()
full_join() menghasilkan semua baris dan kolom dari x dan y. Jika ada nilai yang tidak sama (match) antara x dan y, maka akan bernilai NA.
Misal, Anda hendak menggabungkan band_members dan band_instruments secara full_join, maka Anda bisa menggunakan syntax berikut ini :
full_join(band_members,band_instruments)## Joining, by = "name"## # A tibble: 4 x 3
## name band plays
## <chr> <chr> <chr>
## 1 Mick Stones <NA>
## 2 John Beatles guitar
## 3 Paul Beatles bass
## 4 Keith <NA> guitarsemi_join()
semi_join() menghasilkan semua baris pada table x yang memiliki kesamaan nilai dengan table y, dan semua kolom dari x. Ini mirip dengan inner_join(), bedanya inner_join() mengembalikan semua kolom dari x dan y.
Misal, Anda hendak menggabungkan band_members dan band_instruments secara semi_join, maka Anda bisa menggunakan syntax berikut ini :
semi_join(band_members,band_instruments)## Joining, by = "name"## # A tibble: 2 x 2
## name band
## <chr> <chr>
## 1 John Beatles
## 2 Paul Beatlesanti_join()
anti_join() menghasilkan semua baris dari x yang TIDAK memiliki kesamaan dengan y, dan semua kolom yang berasal dari x.
Misal, Anda hendak menggabungkan band_members dan band_instruments secara anti_join, maka Anda bisa menggunakan syntax berikut ini :
anti_join(band_members,band_instruments)## Joining, by = "name"## # A tibble: 1 x 2
## name band
## <chr> <chr>
## 1 Mick StonesCatatan
Ada kalanya Anda perlu menggabungkan data seperti band_members dan band_instruments2 dimana tidak ada nama kolom yang sama, namun sebenarnya bisa digabungkan. Maka Anda memerlukan sedikit tambahan syntax.
Misal,Anda hendak menggabungkan band_members dan band_instruments2 secara full_join, maka Anda bisa menggunakan syntax berikut ini :
full_join(band_members, band_instruments2, by = c("name" = "artist"))## # A tibble: 4 x 3
## name band plays
## <chr> <chr> <chr>
## 1 Mick Stones <NA>
## 2 John Beatles guitar
## 3 Paul Beatles bass
## 4 Keith <NA> guitarSelengkapnya dapat dipelajari di sini
Referensi
Contributors Data Carpenters. (2020, August 12). SQL databases and R. Data Carpentry. Retrieved from https://datacarpentry.org/R-ecology-lesson/05-r-and-databases.html#Introduction
Wickham, H., & Ruiz, E. (n.d.). Introduction to dbplyr. dbplyr. Retrieved from https://dbplyr.tidyverse.org/
IPB University, rahmaanisa@apps.ipb.ac.id↩︎
IPB University, gerry_dito@apps.ipb.ac.id↩︎
Badan Informasi Geospasial, abdul.aziz@big.go.id↩︎