Latihan Deploy RPubs Nilai Order Negara Nordik, classicmodels
author
2026-04-05
Membuat Koneksi dengan Database di SQL
Database yang digunakan adalah database classicmodels. Database ini dikoneksikan dengan R Studio untuk memudahkan pembuatan visualisasi data. Dalam membuat koneksi ke SQL, diperlukan untuk menginstall beberapa packages yaitu ‘DBI’ dan ‘odbc’. Syntax tertera dibawah
#install.packages("DBI")
#install.packages("odbc")
library(DBI)
library(odbc)## Warning: package 'odbc' was built under R version 4.5.2odbc::odbcListDrivers()## name attribute
## 1 SQL Server APILevel
## 2 SQL Server ConnectFunctions
## 3 SQL Server CPTimeout
## 4 SQL Server DriverODBCVer
## 5 SQL Server FileUsage
## 6 SQL Server SQLLevel
## 7 SQL Server UsageCount
## 8 MySQL ODBC 8.0 ANSI Driver UsageCount
## 9 MySQL ODBC 8.0 Unicode Driver UsageCount
## 10 Microsoft Access Driver (*.mdb, *.accdb) UsageCount
## 11 Microsoft Access Driver (*.mdb, *.accdb) APILevel
## 12 Microsoft Access Driver (*.mdb, *.accdb) ConnectFunctions
## 13 Microsoft Access Driver (*.mdb, *.accdb) DriverODBCVer
## 14 Microsoft Access Driver (*.mdb, *.accdb) FileUsage
## 15 Microsoft Access Driver (*.mdb, *.accdb) FileExtns
## 16 Microsoft Access Driver (*.mdb, *.accdb) SQLLevel
## 17 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) UsageCount
## 18 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) APILevel
## 19 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) ConnectFunctions
## 20 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) DriverODBCVer
## 21 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) FileUsage
## 22 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) FileExtns
## 23 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) SQLLevel
## 24 Microsoft Access Text Driver (*.txt, *.csv) UsageCount
## 25 Microsoft Access Text Driver (*.txt, *.csv) APILevel
## 26 Microsoft Access Text Driver (*.txt, *.csv) ConnectFunctions
## 27 Microsoft Access Text Driver (*.txt, *.csv) DriverODBCVer
## 28 Microsoft Access Text Driver (*.txt, *.csv) FileUsage
## 29 Microsoft Access Text Driver (*.txt, *.csv) FileExtns
## 30 Microsoft Access Text Driver (*.txt, *.csv) SQLLevel
## 31 Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx) UsageCount
## 32 Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx) APILevel
## 33 Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx) ConnectFunctions
## 34 Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx) DriverODBCVer
## 35 Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx) FileUsage
## 36 Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx) FileExtns
## 37 Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx) SQLLevel
## value
## 1 2
## 2 YYY
## 3 60
## 4 03.50
## 5 0
## 6 1
## 7 1
## 8 1
## 9 1
## 10 3
## 11 1
## 12 YYN
## 13 02.50
## 14 2
## 15 *.mdb,*.accdb
## 16 0
## 17 3
## 18 1
## 19 YYN
## 20 02.50
## 21 2
## 22 *.xls,*.xlsx, *.xlsb
## 23 0
## 24 3
## 25 1
## 26 YYN
## 27 02.50
## 28 2
## 29 *.txt, *.csv
## 30 0
## 31 3
## 32 1
## 33 YYN
## 34 02.50
## 35 2
## 36 *.dbf, *.ndx, *.mdx
## 37 0connect2 = dbConnect(odbc(),
Driver = "MySQL ODBC 8.0 ANSI Driver",
Server = "127.0.0.1",
UID = "root",
PWD = "Datalks1122!",
Port = 3306,
Database = "classicmodels")‘install.packages’ digunakan untuk mengunduh packages, sementara ‘library()’ digunakan untuk mengaktifkan packages yang telah terinstall. ‘odbc::odbcListDrivers()’ digunakan untuk memastikan bahwa R sudah terhubung dengan MySQL Untuk membuat koneksi, pertama-tama kita tentukan dulu nama koneksinya (penulis menggunakan “connect2”), kemudian menuliskan syntax untuk mengkoneksikan langsung dengan database yaitu ‘dbConnect(odbc(),Driver=,Server=,UID=,PWD=,Port=,Database=)’. Keterangan: Driver berisi versi MySQL yang digunakan, server berisi server yang digunakan di MySQL, UID berisi identifikasi unik,passowrd yang digunakan user MySQL, port yang terhubung di MySQL, dan database yang akan kita gunakan yaitu database classicmodels
Memvisualisasi Data
Disini, kita akan memvisualisasikan nilai(value) dari orders yang diterima dari negara-negara Nordik (Norway, Sweden, Denmark,Finland) dalam bentuk histogram. Berikut adalah syntaxnya
library(ggplot2)## Warning: package 'ggplot2' was built under R version 4.5.2value_order = dbGetQuery(connect2,"SELECT country, quantityOrdered
FROM customers JOIN orders
USING (customerNumber)
JOIN orderdetails USING (orderNumber)
WHERE country IN ('Denmark', 'Finland', 'Sweden', 'Norway')")
value_order## country quantityOrdered
## 1 Norway 26
## 2 Norway 42
## 3 Norway 27
## 4 Norway 35
## 5 Norway 22
## 6 Norway 27
## 7 Norway 35
## 8 Norway 25
## 9 Norway 46
## 10 Norway 36
## 11 Norway 41
## 12 Norway 36
## 13 Norway 25
## 14 Norway 31
## 15 Norway 45
## 16 Norway 42
## 17 Norway 22
## 18 Norway 41
## 19 Norway 26
## 20 Norway 21
## 21 Norway 24
## 22 Norway 50
## 23 Norway 28
## 24 Norway 47
## 25 Norway 42
## 26 Norway 24
## 27 Norway 24
## 28 Norway 44
## 29 Norway 38
## 30 Norway 28
## 31 Norway 38
## 32 Norway 44
## 33 Sweden 29
## 34 Sweden 23
## 35 Sweden 31
## 36 Sweden 35
## 37 Sweden 38
## 38 Sweden 25
## 39 Sweden 26
## 40 Sweden 32
## 41 Sweden 50
## 42 Sweden 41
## 43 Sweden 41
## 44 Sweden 20
## 45 Sweden 39
## 46 Sweden 26
## 47 Sweden 46
## 48 Sweden 34
## 49 Sweden 20
## 50 Sweden 49
## 51 Sweden 42
## 52 Denmark 50
## 53 Denmark 41
## 54 Denmark 29
## 55 Denmark 22
## 56 Denmark 38
## 57 Denmark 41
## 58 Denmark 43
## 59 Denmark 44
## 60 Denmark 50
## 61 Denmark 41
## 62 Denmark 29
## 63 Denmark 31
## 64 Denmark 39
## 65 Denmark 22
## 66 Denmark 25
## 67 Denmark 28
## 68 Denmark 29
## 69 Denmark 20
## 70 Denmark 41
## 71 Denmark 49
## 72 Denmark 44
## 73 Denmark 47
## 74 Denmark 22
## 75 Denmark 34
## 76 Denmark 29
## 77 Denmark 25
## 78 Denmark 45
## 79 Denmark 25
## 80 Denmark 20
## 81 Denmark 21
## 82 Denmark 43
## 83 Denmark 37
## 84 Denmark 37
## 85 Denmark 61
## 86 Denmark 48
## 87 Denmark 65
## 88 Norway 27
## 89 Norway 28
## 90 Norway 20
## 91 Norway 36
## 92 Norway 44
## 93 Norway 42
## 94 Norway 22
## 95 Norway 21
## 96 Norway 27
## 97 Norway 45
## 98 Norway 30
## 99 Norway 22
## 100 Norway 39
## 101 Norway 34
## 102 Norway 37
## 103 Norway 23
## 104 Norway 25
## 105 Norway 48
## 106 Norway 38
## 107 Norway 45
## 108 Norway 32
## 109 Norway 25
## 110 Norway 40
## 111 Norway 44
## 112 Norway 29
## 113 Norway 38
## 114 Norway 24
## 115 Norway 43
## 116 Norway 45
## 117 Finland 32
## 118 Finland 38
## 119 Finland 44
## 120 Finland 29
## 121 Finland 23
## 122 Finland 34
## 123 Finland 37
## 124 Finland 44
## 125 Finland 32
## 126 Finland 20
## 127 Finland 43
## 128 Finland 44
## 129 Finland 34
## 130 Finland 23
## 131 Finland 29
## 132 Finland 24
## 133 Finland 39
## 134 Finland 49
## 135 Finland 47
## 136 Finland 33
## 137 Finland 32
## 138 Finland 24
## 139 Finland 38
## 140 Finland 44
## 141 Finland 24
## 142 Finland 50
## 143 Finland 35
## 144 Finland 31
## 145 Finland 36
## 146 Finland 39
## 147 Denmark 28
## 148 Denmark 43
## 149 Denmark 48
## 150 Denmark 23
## 151 Denmark 36
## 152 Denmark 25
## 153 Denmark 37
## 154 Denmark 23
## 155 Denmark 20
## 156 Denmark 25
## 157 Denmark 20
## 158 Denmark 30
## 159 Denmark 38
## 160 Denmark 46
## 161 Denmark 36
## 162 Denmark 45
## 163 Denmark 42
## 164 Denmark 20
## 165 Denmark 23
## 166 Denmark 29
## 167 Denmark 44
## 168 Denmark 39
## 169 Denmark 38
## 170 Denmark 35
## 171 Denmark 28
## 172 Denmark 38
## 173 Denmark 23
## 174 Norway 45
## 175 Norway 31
## 176 Norway 22
## 177 Norway 30
## 178 Norway 39
## 179 Norway 21
## 180 Norway 21
## 181 Norway 50
## 182 Norway 33
## 183 Norway 24
## 184 Norway 45
## 185 Norway 25
## 186 Norway 32
## 187 Norway 37
## 188 Norway 32
## 189 Norway 47
## 190 Norway 22
## 191 Norway 23
## 192 Norway 39
## 193 Norway 27
## 194 Norway 22
## 195 Norway 48
## 196 Norway 22
## 197 Norway 50
## 198 Finland 24
## 199 Finland 43
## 200 Finland 49
## 201 Finland 39
## 202 Finland 21
## 203 Finland 42
## 204 Finland 30
## 205 Finland 27
## 206 Finland 41
## 207 Finland 26
## 208 Finland 21
## 209 Finland 46
## 210 Finland 47
## 211 Finland 20
## 212 Finland 29
## 213 Finland 39
## 214 Finland 28
## 215 Finland 22
## 216 Finland 50
## 217 Finland 38
## 218 Finland 33
## 219 Finland 46
## 220 Finland 23
## 221 Finland 39
## 222 Finland 44
## 223 Finland 32
## 224 Finland 41
## 225 Finland 34
## 226 Finland 37
## 227 Finland 45
## 228 Finland 25
## 229 Finland 29
## 230 Finland 21
## 231 Finland 39
## 232 Finland 47
## 233 Finland 34
## 234 Finland 20
## 235 Finland 21
## 236 Finland 40
## 237 Finland 24
## 238 Finland 44
## 239 Finland 44
## 240 Finland 25
## 241 Finland 27
## 242 Finland 48
## 243 Finland 40
## 244 Finland 49
## 245 Finland 33
## 246 Finland 34
## 247 Finland 34
## 248 Finland 46
## 249 Finland 22
## 250 Finland 46
## 251 Finland 24
## 252 Finland 32
## 253 Finland 28
## 254 Finland 21
## 255 Finland 43
## 256 Finland 21
## 257 Finland 31
## 258 Finland 43
## 259 Finland 50
## 260 Sweden 44
## 261 Sweden 43
## 262 Sweden 46
## 263 Sweden 34
## 264 Sweden 33
## 265 Sweden 21
## 266 Sweden 20
## 267 Sweden 32
## 268 Sweden 29
## 269 Sweden 43
## 270 Sweden 29
## 271 Sweden 46
## 272 Sweden 24
## 273 Sweden 28
## 274 Sweden 40
## 275 Sweden 38
## 276 Sweden 37
## 277 Sweden 30
## 278 Sweden 41
## 279 Sweden 41
## 280 Sweden 26
## 281 Sweden 47
## 282 Sweden 37
## 283 Sweden 23
## 284 Sweden 48
## 285 Sweden 29
## 286 Sweden 48
## 287 Sweden 26
## 288 Sweden 32
## 289 Sweden 28
## 290 Sweden 26
## 291 Sweden 25
## 292 Sweden 36
## 293 Sweden 47
## 294 Sweden 49
## 295 Sweden 39
## 296 Sweden 45
## 297 Sweden 49ggplot(value_order, aes(x=quantityOrdered)) +
geom_histogram(binwidth = 10, fill = 'maroon',color = 'darkred' ,alpha = 0.5) +
theme_minimal() + labs(x="Nilai order", title = "Nilai Order dari Negara Nordik")
value_order## country quantityOrdered
## 1 Norway 26
## 2 Norway 42
## 3 Norway 27
## 4 Norway 35
## 5 Norway 22
## 6 Norway 27
## 7 Norway 35
## 8 Norway 25
## 9 Norway 46
## 10 Norway 36
## 11 Norway 41
## 12 Norway 36
## 13 Norway 25
## 14 Norway 31
## 15 Norway 45
## 16 Norway 42
## 17 Norway 22
## 18 Norway 41
## 19 Norway 26
## 20 Norway 21
## 21 Norway 24
## 22 Norway 50
## 23 Norway 28
## 24 Norway 47
## 25 Norway 42
## 26 Norway 24
## 27 Norway 24
## 28 Norway 44
## 29 Norway 38
## 30 Norway 28
## 31 Norway 38
## 32 Norway 44
## 33 Sweden 29
## 34 Sweden 23
## 35 Sweden 31
## 36 Sweden 35
## 37 Sweden 38
## 38 Sweden 25
## 39 Sweden 26
## 40 Sweden 32
## 41 Sweden 50
## 42 Sweden 41
## 43 Sweden 41
## 44 Sweden 20
## 45 Sweden 39
## 46 Sweden 26
## 47 Sweden 46
## 48 Sweden 34
## 49 Sweden 20
## 50 Sweden 49
## 51 Sweden 42
## 52 Denmark 50
## 53 Denmark 41
## 54 Denmark 29
## 55 Denmark 22
## 56 Denmark 38
## 57 Denmark 41
## 58 Denmark 43
## 59 Denmark 44
## 60 Denmark 50
## 61 Denmark 41
## 62 Denmark 29
## 63 Denmark 31
## 64 Denmark 39
## 65 Denmark 22
## 66 Denmark 25
## 67 Denmark 28
## 68 Denmark 29
## 69 Denmark 20
## 70 Denmark 41
## 71 Denmark 49
## 72 Denmark 44
## 73 Denmark 47
## 74 Denmark 22
## 75 Denmark 34
## 76 Denmark 29
## 77 Denmark 25
## 78 Denmark 45
## 79 Denmark 25
## 80 Denmark 20
## 81 Denmark 21
## 82 Denmark 43
## 83 Denmark 37
## 84 Denmark 37
## 85 Denmark 61
## 86 Denmark 48
## 87 Denmark 65
## 88 Norway 27
## 89 Norway 28
## 90 Norway 20
## 91 Norway 36
## 92 Norway 44
## 93 Norway 42
## 94 Norway 22
## 95 Norway 21
## 96 Norway 27
## 97 Norway 45
## 98 Norway 30
## 99 Norway 22
## 100 Norway 39
## 101 Norway 34
## 102 Norway 37
## 103 Norway 23
## 104 Norway 25
## 105 Norway 48
## 106 Norway 38
## 107 Norway 45
## 108 Norway 32
## 109 Norway 25
## 110 Norway 40
## 111 Norway 44
## 112 Norway 29
## 113 Norway 38
## 114 Norway 24
## 115 Norway 43
## 116 Norway 45
## 117 Finland 32
## 118 Finland 38
## 119 Finland 44
## 120 Finland 29
## 121 Finland 23
## 122 Finland 34
## 123 Finland 37
## 124 Finland 44
## 125 Finland 32
## 126 Finland 20
## 127 Finland 43
## 128 Finland 44
## 129 Finland 34
## 130 Finland 23
## 131 Finland 29
## 132 Finland 24
## 133 Finland 39
## 134 Finland 49
## 135 Finland 47
## 136 Finland 33
## 137 Finland 32
## 138 Finland 24
## 139 Finland 38
## 140 Finland 44
## 141 Finland 24
## 142 Finland 50
## 143 Finland 35
## 144 Finland 31
## 145 Finland 36
## 146 Finland 39
## 147 Denmark 28
## 148 Denmark 43
## 149 Denmark 48
## 150 Denmark 23
## 151 Denmark 36
## 152 Denmark 25
## 153 Denmark 37
## 154 Denmark 23
## 155 Denmark 20
## 156 Denmark 25
## 157 Denmark 20
## 158 Denmark 30
## 159 Denmark 38
## 160 Denmark 46
## 161 Denmark 36
## 162 Denmark 45
## 163 Denmark 42
## 164 Denmark 20
## 165 Denmark 23
## 166 Denmark 29
## 167 Denmark 44
## 168 Denmark 39
## 169 Denmark 38
## 170 Denmark 35
## 171 Denmark 28
## 172 Denmark 38
## 173 Denmark 23
## 174 Norway 45
## 175 Norway 31
## 176 Norway 22
## 177 Norway 30
## 178 Norway 39
## 179 Norway 21
## 180 Norway 21
## 181 Norway 50
## 182 Norway 33
## 183 Norway 24
## 184 Norway 45
## 185 Norway 25
## 186 Norway 32
## 187 Norway 37
## 188 Norway 32
## 189 Norway 47
## 190 Norway 22
## 191 Norway 23
## 192 Norway 39
## 193 Norway 27
## 194 Norway 22
## 195 Norway 48
## 196 Norway 22
## 197 Norway 50
## 198 Finland 24
## 199 Finland 43
## 200 Finland 49
## 201 Finland 39
## 202 Finland 21
## 203 Finland 42
## 204 Finland 30
## 205 Finland 27
## 206 Finland 41
## 207 Finland 26
## 208 Finland 21
## 209 Finland 46
## 210 Finland 47
## 211 Finland 20
## 212 Finland 29
## 213 Finland 39
## 214 Finland 28
## 215 Finland 22
## 216 Finland 50
## 217 Finland 38
## 218 Finland 33
## 219 Finland 46
## 220 Finland 23
## 221 Finland 39
## 222 Finland 44
## 223 Finland 32
## 224 Finland 41
## 225 Finland 34
## 226 Finland 37
## 227 Finland 45
## 228 Finland 25
## 229 Finland 29
## 230 Finland 21
## 231 Finland 39
## 232 Finland 47
## 233 Finland 34
## 234 Finland 20
## 235 Finland 21
## 236 Finland 40
## 237 Finland 24
## 238 Finland 44
## 239 Finland 44
## 240 Finland 25
## 241 Finland 27
## 242 Finland 48
## 243 Finland 40
## 244 Finland 49
## 245 Finland 33
## 246 Finland 34
## 247 Finland 34
## 248 Finland 46
## 249 Finland 22
## 250 Finland 46
## 251 Finland 24
## 252 Finland 32
## 253 Finland 28
## 254 Finland 21
## 255 Finland 43
## 256 Finland 21
## 257 Finland 31
## 258 Finland 43
## 259 Finland 50
## 260 Sweden 44
## 261 Sweden 43
## 262 Sweden 46
## 263 Sweden 34
## 264 Sweden 33
## 265 Sweden 21
## 266 Sweden 20
## 267 Sweden 32
## 268 Sweden 29
## 269 Sweden 43
## 270 Sweden 29
## 271 Sweden 46
## 272 Sweden 24
## 273 Sweden 28
## 274 Sweden 40
## 275 Sweden 38
## 276 Sweden 37
## 277 Sweden 30
## 278 Sweden 41
## 279 Sweden 41
## 280 Sweden 26
## 281 Sweden 47
## 282 Sweden 37
## 283 Sweden 23
## 284 Sweden 48
## 285 Sweden 29
## 286 Sweden 48
## 287 Sweden 26
## 288 Sweden 32
## 289 Sweden 28
## 290 Sweden 26
## 291 Sweden 25
## 292 Sweden 36
## 293 Sweden 47
## 294 Sweden 49
## 295 Sweden 39
## 296 Sweden 45
## 297 Sweden 49Untuk menampilkan data yang kita butuhkan, kita menggunakan bahasa query SQL dengan menggunakan syntax ‘dbGetQuery(connect,query)’ dan memberi nama data kita (penulis menggunakan “value_order”). Selanjutnya, visualisasi dilakukan dengan menggunakan syntax ’ggplot(data=value_order,aes(x,y dengan x dijadikan sebagai nilai dari quantityOrdered)) + geom_histogram(binwidth=10 menunjukkan rentang yang digunakan adalah 1-10 dst, fill=warna histogram, color=warna batas histogram, alpha= transparasi agar tidak terlalu tebal) + theme_minimal() untuk membuat latar sederhana + labs(x=keterangan nilai x, title=judul histogram)