In this project, I created a SQL instance in GCP, connect it to MYSQL Workbench, created a database and tree separate tables in that database, imported necessary r libraries, configured DSN and connected the database through odbc Also ran few SQL custom queries directly from r. Finally, uploaded the code to my github repository.
library(RODBC)
library(dplyr)## Warning: package 'dplyr' was built under R version 4.1.3##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(DBI)## Warning: package 'DBI' was built under R version 4.1.3library(odbc)## Warning: package 'odbc' was built under R version 4.1.3conn1 <- odbcConnect("MySQL_DSN")
sqlQuery(conn1, 'SELECT * FROM usertbl')## user_id full_name
## 1 1 Leon Das
## 2 2 Steve Nash
## 3 3 Sarah Palin
## 4 4 Linda Gray
## 5 5 Cooper Kupp
## 6 6 Pete RosesqlQuery(conn1, 'SELECT * FROM movietbl')## movie_id title
## 1 1 Into The Wild
## 2 2 I Saw The Devil
## 3 3 Me Time
## 4 4 Kites
## 5 5 12 Angry Men
## 6 6 Life Is BeautifulsqlQuery(conn1, 'SELECT * FROM reviewtbl')## review_id movie_id user_id rating
## 1 1 1 1 4
## 2 2 1 2 5
## 3 3 1 3 3
## 4 4 1 4 3
## 5 5 1 5 5
## 6 6 1 6 4
## 7 7 2 1 5
## 8 8 2 2 3
## 9 9 2 3 4
## 10 10 2 4 5
## 11 11 2 5 3
## 12 12 2 6 4
## 13 13 3 1 5
## 14 14 3 2 2
## 15 15 3 3 3
## 16 16 3 4 4
## 17 17 3 5 5
## 18 18 3 6 3
## 19 19 4 1 4
## 20 20 4 2 5
## 21 21 4 3 3
## 22 22 4 4 2
## 23 23 4 5 4
## 24 24 4 6 4
## 25 25 5 1 5
## 26 26 5 2 5
## 27 27 5 3 4
## 28 28 5 4 4
## 29 29 5 5 3
## 30 30 5 5 3
## 31 31 6 1 4
## 32 32 6 2 4
## 33 33 6 3 5
## 34 34 6 4 5
## 35 35 6 5 4
## 36 36 6 6 4sqlQuery(conn1,
"SELECT M.title As 'Title', U.full_name As 'User',
R.rating As 'Rating'
FROM movietbl AS M
JOIN reviewtbl AS R
ON M.movie_id = R.movie_id
JOIN usertbl AS U
ON U.user_id = R.user_id;"
)## Title User Rating
## 1 Life Is Beautiful Leon Das 4
## 2 12 Angry Men Leon Das 5
## 3 Kites Leon Das 4
## 4 Me Time Leon Das 5
## 5 I Saw The Devil Leon Das 5
## 6 Into The Wild Leon Das 4
## 7 Life Is Beautiful Steve Nash 4
## 8 12 Angry Men Steve Nash 5
## 9 Kites Steve Nash 5
## 10 Me Time Steve Nash 2
## 11 I Saw The Devil Steve Nash 3
## 12 Into The Wild Steve Nash 5
## 13 Life Is Beautiful Sarah Palin 5
## 14 12 Angry Men Sarah Palin 4
## 15 Kites Sarah Palin 3
## 16 Me Time Sarah Palin 3
## 17 I Saw The Devil Sarah Palin 4
## 18 Into The Wild Sarah Palin 3
## 19 Life Is Beautiful Linda Gray 5
## 20 12 Angry Men Linda Gray 4
## 21 Kites Linda Gray 2
## 22 Me Time Linda Gray 4
## 23 I Saw The Devil Linda Gray 5
## 24 Into The Wild Linda Gray 3
## 25 Life Is Beautiful Cooper Kupp 4
## 26 12 Angry Men Cooper Kupp 3
## 27 12 Angry Men Cooper Kupp 3
## 28 Kites Cooper Kupp 4
## 29 Me Time Cooper Kupp 5
## 30 I Saw The Devil Cooper Kupp 3
## 31 Into The Wild Cooper Kupp 5
## 32 Life Is Beautiful Pete Rose 4
## 33 Kites Pete Rose 4
## 34 Me Time Pete Rose 3
## 35 I Saw The Devil Pete Rose 4
## 36 Into The Wild Pete Rose 4sqlQuery(conn1, "SELECT
M.title As 'Title',
U.full_name As 'User',
R.rating As 'Rating'
FROM movietbl AS M
JOIN reviewtbl AS R
ON M.movie_id = R.movie_id
JOIN usertbl AS U
ON U.user_id = R.user_id
WHERE U.user_id = 1;")## Title User Rating
## 1 Into The Wild Leon Das 4
## 2 I Saw The Devil Leon Das 5
## 3 Me Time Leon Das 5
## 4 Kites Leon Das 4
## 5 12 Angry Men Leon Das 5
## 6 Life Is Beautiful Leon Das 4sqlQuery(conn1, "SELECT
M.title As 'Title',
U.full_name As 'User',
R.rating As 'Rating'
FROM movietbl AS M
JOIN reviewtbl AS R
ON M.movie_id = R.movie_id
JOIN usertbl AS U
ON U.user_id = R.user_id
WHERE R.rating = 5;")## Title User Rating
## 1 12 Angry Men Leon Das 5
## 2 Me Time Leon Das 5
## 3 I Saw The Devil Leon Das 5
## 4 12 Angry Men Steve Nash 5
## 5 Kites Steve Nash 5
## 6 Into The Wild Steve Nash 5
## 7 Life Is Beautiful Sarah Palin 5
## 8 Life Is Beautiful Linda Gray 5
## 9 I Saw The Devil Linda Gray 5
## 10 Me Time Cooper Kupp 5
## 11 Into The Wild Cooper Kupp 5conn2 <- dbConnect(odbc :: odbc(), "MySQL_DSN")
# List tables
dbListTables(conn2)## [1] "movietbl" "reviewtbl" "usertbl"# List columns of certain table
dbListFields(conn2, "movietbl")## [1] "movie_id" "title"movietbl <- tbl(conn2, "movietbl")
movietbl_df <- collect(movietbl)
movietbl_df ## # A tibble: 6 x 2
## movie_id title
## <int> <chr>
## 1 1 Into The Wild
## 2 2 I Saw The Devil
## 3 3 Me Time
## 4 4 Kites
## 5 5 12 Angry Men
## 6 6 Life Is Beautifulusertbl <- tbl(conn2, "usertbl")
usertbl_df <- collect(usertbl)
usertbl_df ## # A tibble: 6 x 2
## user_id full_name
## <int> <chr>
## 1 1 Leon Das
## 2 2 Steve Nash
## 3 3 Sarah Palin
## 4 4 Linda Gray
## 5 5 Cooper Kupp
## 6 6 Pete Rosereviewtbl <- tbl(conn2, "reviewtbl")
reviewtbl_df <- collect(reviewtbl)
reviewtbl## # Source: table<reviewtbl> [?? x 4]
## # Database: mysql [root@:/movies]
## review_id movie_id user_id rating
## <int> <int> <int> <int>
## 1 1 1 1 4
## 2 2 1 2 5
## 3 3 1 3 3
## 4 4 1 4 3
## 5 5 1 5 5
## 6 6 1 6 4
## 7 7 2 1 5
## 8 8 2 2 3
## 9 9 2 3 4
## 10 10 2 4 5
## # ... with more rows