KAL
Menu

Basis Data

Koneksi basis data pertama dengan DBI dan query dplyr melalui dbplyr.

Daftar isi

Analisis sering membutuhkan data yang tersimpan di basis data, bukan di berkas. DBI adalah antarmuka standar R untuk basis data, sedangkan dbplyr memperluasnya dengan menerjemahkan verba dplyr, yaitu fungsi transformasi data seperti filter() dan arrange(), menjadi SQL. Topik ini membangun basis data pertama yang berjalan penuh di dalam memori menggunakan SQLite, menulis tabel ke dalamnya, menjalankan SQL dengan dbGetQuery(), lalu mengulang query yang sama dengan verba dplyr. Kebiasaan menutup koneksi menutup pembahasan. Pemahaman yang dibentuk di sini berlaku untuk semua driver, bukan hanya SQLite.

Koneksi pertama dengan DBI#

DBI mendefinisikan antarmuka generik, yaitu dbConnect() untuk menyambung, dbWriteTable() untuk menulis, dan dbGetQuery() untuk menjalankan SQL. Yang menangani detail teknisnya adalah driver, yaitu paket yang mengimplementasikan DBI untuk satu jenis basis data. Driver RSQLite dipakai di sini karena tidak membutuhkan instalasi server, dan nilai :memory: pada dbConnect() membuat seluruh basis data hidup di memori selama koneksi terbuka.

r Menyambung ke basis data SQLite di memori 
library(tidyverse)
library(DBI)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbListTables(con)
text Keluaran 
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.1     ✔ readr     2.2.0
✔ forcats   1.0.1     ✔ stringr   1.6.0
✔ ggplot2   4.0.3     ✔ tibble    3.3.1
✔ lubridate 1.9.5     ✔ tidyr     1.3.2
✔ purrr     1.2.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
character(0)

Pemuatan tidyverse pada blok pertama menyediakan tibble dan verba dplyr yang dipakai di bagian akhir. Fungsi dbListTables() mengembalikan daftar tabel, dan daftar kosong character(0) menegaskan bahwa basis data memori baru saja lahir tanpa tabel apa pun. Segala yang dibuat setelah ini hilang ketika koneksi ditutup, dan sifat itulah yang membuat seluruh contoh mudah diulang dari awal. Bila seluruh tabel memang ingin dibaca sekaligus sebagai data.frame, DBI juga menyediakan dbReadTable() dengan argumen nama tabelnya.

Tabel pertama ditulis dengan dbWriteTable(), yang menerima objek koneksi, nama tabel, dan tibble sumbernya. Fungsi ini mengembalikan TRUE tanpa menampilkannya, sehingga keberhasilannya diperiksa lewat dbListTables().

r Menulis tibble sebagai tabel SQLite 
siswa <- tibble(
  nim   = c(1, 2, 3, 4, 5, 6),
  nama  = c("Ali", "Budi", "Citra", "Dewi", "Eka", "Fajar"),
  kelas = c("A", "A", "B", "B", "A", "B"),
  nilai = c(82, 75, 88, 79, 91, 68)
)
dbWriteTable(con, "siswa", siswa)
dbListTables(con)
text Keluaran 
[1] "siswa"

SQL dijalankan lewat dbGetQuery(), yang mengirim query ke basis data lalu mengembalikan hasilnya sebagai data.frame. Query pertama memakai SELECT untuk memilih kolom, WHERE untuk menyaring baris, dan ORDER BY untuk mengurutkan hasil dari nilai tertinggi. Seluruh pekerjaan penyaringan berlangsung di sisi basis data, dan R hanya menerima baris yang lolos.

r Query SQL dasar dengan WHERE dan ORDER BY 
dbGetQuery(con, "SELECT nama, nilai FROM siswa WHERE nilai >= 80 ORDER BY nilai DESC")
text Keluaran 
   nama nilai
1   Eka    91
2 Citra    88
3   Ali    82

Agregasi juga berjalan di sisi basis data. Query kedua menghitung rata-rata dan banyaknya siswa per kelas dengan GROUP BY, dan alias AS memberi nama pada kolom hasilnya.

r Query agregasi dengan GROUP BY 
dbGetQuery(con, "SELECT kelas, AVG(nilai) AS rata_nilai, COUNT(*) AS n FROM siswa GROUP BY kelas")
text Keluaran 
  kelas rata_nilai n
1     A   82.66667 3
2     B   78.33333 3

Verba dplyr di atas basis data#

Menulis SQL dalam string membuat kode sulit dites dan mudah salah ketik. Paket dbplyr menawarkan jalan lain, yaitu verba dplyr ditulis seperti biasa lalu diterjemahkan menjadi SQL oleh dbplyr. Fungsi tbl() menghasilkan tampilan malas (lazy) atas tabel basis data, yaitu objek yang hanya menyimpan rencana query dan belum menarik datanya ke R.

r Tampilan malas atas tabel SQLite 
library(dbplyr)
siswa_db <- tbl(con, "siswa")
siswa_db
text Keluaran 

Attaching package: ‘dbplyr’

The following objects are masked from ‘package:dplyr’:

    ident, sql, sql_escape_ident, sql_escape_string

# A query:  ?? x 4
# Database: sqlite 3.53.3 [:memory:]
    nim nama  kelas nilai
  <dbl> <chr> <chr> <dbl>
1     1 Ali   A        82
2     2 Budi  A        75
3     3 Citra B        88
4     4 Dewi  B        79
5     5 Eka   A        91
6     6 Fajar B        68

Pesan Attaching package pada keluaran memperingatkan bahwa dbplyr menutupi beberapa objek dari dplyr. Peringatan ini wajar dan tidak mengganggu fungsi yang dipakai di sini. Tampilan berawalan # A query menunjukkan bahwa siswa_db hanyalah pintu menuju tabel, bukan salinan datanya, dan tanda ?? pada jumlah baris berarti banyaknya baris belum dihitung.

Verba dplyr kini bekerja di atas tampilan malas tersebut. Rantai filter(), arrange(), dan select() disusun persis seperti pada tibble biasa, tetapi sifat malasnya terlihat dari tanda ?? yang masih tersisa.

r Rantai verba dplyr pada tabel malas 
siswa_db |>
  filter(nilai >= 80) |>
  arrange(desc(nilai)) |>
  select(nama, nilai)
text Keluaran 
# A query:    ?? x 2
# Database:   sqlite 3.53.3 [:memory:]
# Ordered by: desc(nilai)
  nama  nilai
  <chr> <dbl>
1 Eka      91
2 Citra    88
3 Ali      82

Terjemahan SQL yang dihasilkan dbplyr dapat dilihat dengan show_query(). Fungsi ini berguna untuk memeriksa logika query dan sekaligus menjadi sarana belajar SQL bagi yang terbiasa dengan dplyr. Tidak setiap fungsi R memiliki padanan SQL, sehingga bila suatu langkah tidak dapat diterjemahkan, pesan error dbplyr akan menunjukkannya dan pekerjaan itu perlu dipindah ke sisi R setelah collect().

r Menampilkan SQL hasil terjemahan dbplyr 
siswa_db |>
  filter(nilai >= 80) |>
  arrange(desc(nilai)) |>
  select(nama, nilai) |>
  show_query()
text Keluaran 
<SQL>
SELECT `nama`, `nilai`
FROM `siswa`
WHERE (`nilai` >= 80.0)
ORDER BY `nilai` DESC

Data baru benar-benar ditarik ke R ketika collect() dipanggil. Hasilnya adalah tibble biasa yang dapat diolah tanpa koneksi sekalipun. Kebiasaan yang baik adalah menarik hanya hasil yang sudah disaring dan diringkas, bukan seluruh tabel, karena pengerjaan agregasi di sisi basis data jauh lebih hemat memori.

r Menarik hasil query ke R dengan collect 
nilai_tinggi <- siswa_db |>
  filter(nilai >= 80) |>
  collect()
nilai_tinggi
text Keluaran 
# A tibble: 3 × 4
    nim nama  kelas nilai
  <dbl> <chr> <chr> <dbl>
1     1 Ali   A        82
2     3 Citra B        88
3     5 Eka   A        91

Koneksi ditutup dengan dbDisconnect(), dan statusnya dapat dicek dengan dbIsValid(). Basis data :memory: hilang bersama koneksi itu, sedangkan basis data berkas tetap tersimpan apabila dbConnect() diberi jalur berkas, misalnya “sekolah.db”, alih-alih :memory:. Di dalam fungsi yang membuka koneksi sendiri, penutupan dapat dijamin dengan on.exit(dbDisconnect(con)) sehingga koneksi tetap tertutup meskipun terjadi error di tengah fungsi.

r Menutup koneksi dan memeriksa statusnya 
dbDisconnect(con)
dbIsValid(con)
text Keluaran 
[1] FALSE

Pola kerja yang sama berlaku untuk basis data lainnya. Cukup ganti driver pada dbConnect(), misalnya RPostgres untuk PostgreSQL atau RMariaDB untuk MySQL dan MariaDB, sementara seluruh fungsi DBI dan verba dbplyr tetap dipakai tanpa perubahan. Kebiasaan menutup koneksi penting karena skrip panjang dan laporan yang dirender berulang kali dapat menghabiskan kuota koneksi pada server basis data sungguhan.

Ringkasan#

  • dbConnect() dengan driver RSQLite dan :memory: membuat basis data sementara yang seluruh alurnya dapat dijalankan ulang, dan dbWriteTable() mengisinya dari tibble.
  • dbGetQuery() menjalankan SQL secara langsung, sedangkan dbplyr menerjemahkan verba dplyr menjadi SQL yang dapat diperiksa lewat show_query() dan ditarik ke R dengan collect().
  • Koneksi ditutup dengan dbDisconnect(), dan pola yang sama berlaku untuk PostgreSQL maupun MySQL dengan mengganti driver pada dbConnect().

Referensi#