Database with R and Spatial Data

Database Connection with R

chinook <-DBI::dbConnect(RSQLite::SQLite(), "dataset/chinook.db")

Tabel yang terdapat dalam chinook adalah :

dbListTables(chinook)

##  [1] "albums"          "artists"         "customers"       "employees"      
##  [5] "genres"          "invoice_items"   "invoices"        "media_types"    
##  [9] "playlist_track"  "playlists"       "sqlite_sequence" "sqlite_stat1"   
## [13] "tracks"

Retrieving Tables

Tabel-tabel yang akan digunakan pada percobaan mergin tabel adalah tabel albums,artists,playlists,playlist_track,tracks,customers,invoices.

albums = dbGetQuery(chinook, "SELECT * FROM albums")
artist = dbGetQuery(chinook, "SELECT * FROM artists")
playlist = dbGetQuery(chinook, "SELECT * FROM playlists")
playlist_track = dbGetQuery(chinook, "SELECT * FROM playlist_track")
tracks = dbGetQuery(chinook, "SELECT * FROM tracks")
customers = dbGetQuery(chinook, "SELECT * FROM customers")
invoices = dbGetQuery(chinook, "SELECT * FROM invoices")

Left Join

The mutating joins add columns from y to x, matching rows based on the keys, includes all rows in x.

left_join() yang dicoba pada tahap ini adalah antara table playlist track dan tracks.

left_res = left_join(playlist_track,tracks)

## Joining, by = "TrackId"