The purpose of this assignment was to load SQL data from a MYSQL server (in this case - MySQL hosted on Azure) to a NoSQL Database (Mongo DB Server in Azure). I choose MongoDB because it is one of the most popular NoSQL databases on the market.
For this project, I used the the following libraries:
In this section, I loaded data from my assignment 3 movies database. I used the following query to join and store into a dataframe:
mysql_movies <- dbGetQuery(con, ‘select u.username, u.firstname, u.lastname, m.title, m.category, m.length, mr.rating, mr.review from users as u join users_movierating as umr on u.userid = umr.userid join movierating as mr on umr.movieratingid = mr.movieratingid join movies as m on mr.movieid = m.movieid;’)
pwd_file <- "/Users/davidapolinar/Dropbox/CUNYProjects/Srping2019/Data607/Week 13/password.txt"
localuserpassword <-read.delim(pwd_file, header = FALSE, stringsAsFactors = FALSE)
con <- dbConnect(RMariaDB::MariaDB(), user='x-admin@cunyspsmysql', password=localuserpassword$V1, dbname='moviereviews' ,host='cunyspsmysql.mysql.database.azure.com')
#con <- dbConnect(RMariaDB::MariaDB(), user='rreadonly@cunyspsmysql', password="", dbname='moviereviews' ,host='cunyspsmysql.mysql.database.azure.com')
dbListTables(con)## [1] "movierating" "movies" "users"
## [4] "users_movierating"dbListFields(con, 'movies')## [1] "movieid" "title" "length" "director" "category"mysql_movies <- dbGetQuery(con, 'select u.username, u.firstname, u.lastname, m.title, m.category, m.length, mr.rating, mr.review from users as u
join users_movierating as umr on u.userid = umr.userid
join movierating as mr on umr.movieratingid = mr.movieratingid
join movies as m on mr.movieid = m.movieid;')
head(mysql_movies)## username firstname lastname title category length
## 1 RussellS Russell S Mission Impossible: Fallout action 148
## 2 RussellS Russell S Avengers: Infinity War action 149
## 3 RussellS Russell S The Quiet Place horror 90
## 4 RussellS Russell S DeadPool 2 comedy 119
## 5 RussellS Russell S Incredibles 2 animation 118
## 6 RussellS Russell S The Equalizer 2 crime 121
## rating
## 1 5
## 2 2
## 3 5
## 4 5
## 5 5
## 6 3
## review
## 1 The best thrilling movie ever made! With the best movie character ever portrayed: Tom Cruise as Ethan Hunt!
## 2 At stake, as ever, is the fate of the universe. Why must it always be the universe? What's wrong with the fate of Hackensack? Doesn't anyone care what happens to South Dakota, or Denmark, or Peru?
## 3 This is a movie about the sound of fear, but it gives us a great deal more to listen to.
## 4 Deadpool 2 is an R-rated, potty-mouthed splatterfest and a funny one.
## 5 How great to see them back in this funny, exciting, if less audacious film.
## 6 Once it's over, you won't necessarily be itching for an Equalizer 3.In this section, I used mongolite to connect to a MongoDB server hosted in Azure. I do not cover the installation or configuration of MongoDB in this section, but I followed a document similar to the following to install it on Ubuntu 18
The data inserted into MongoDB using MongoDBLite leveraged a dataframe. The process is simple and straight forward.
# Drop if exits
mongo_pwd_file <- "/Users/davidapolinar/Dropbox/CUNYProjects/Srping2019/Data607/Week 13/mongopassword.txt"
mongo_password <-read.delim(mongo_pwd_file, header = FALSE, stringsAsFactors = FALSE)## Warning in read.table(file = file, header = header, sep = sep, quote
## = quote, : incomplete final line found by readTableHeader on '/
## Users/davidapolinar/Dropbox/CUNYProjects/Srping2019/Data607/Week 13/
## mongopassword.txt'url <- paste0("mongodb://x-admin:", mongo_password$V1 , "@10.20.1.6:27017/moviereviews",sep = "")
mgo <- mongo(collection = "movies", db = "moviereviews", url=url, verbose = TRUE)
# mgo <- mongo(collection = "movies", db = "moviereviews", url="mongodb://x-admin:Yankees2019@10.20.1.6:27017/moviereviews", verbose = TRUE)
mgo$drop()
# Inserting a dataframe as a collection
mgo$insert(mysql_movies)##
Complete! Processed total of 30 rows.## List of 5
## $ nInserted : num 30
## $ nMatched : num 0
## $ nRemoved : num 0
## $ nUpserted : num 0
## $ writeErrors: list()# Querying usernames by RussellS
mgo$find('{"username":"RussellS"}')##
Found 6 records...
Imported 6 records. Simplifying into dataframe...## username firstname lastname title category length
## 1 RussellS Russell S Mission Impossible: Fallout action 148
## 2 RussellS Russell S Avengers: Infinity War action 149
## 3 RussellS Russell S The Quiet Place horror 90
## 4 RussellS Russell S DeadPool 2 comedy 119
## 5 RussellS Russell S Incredibles 2 animation 118
## 6 RussellS Russell S The Equalizer 2 crime 121
## rating
## 1 5
## 2 2
## 3 5
## 4 5
## 5 5
## 6 3
## review
## 1 The best thrilling movie ever made! With the best movie character ever portrayed: Tom Cruise as Ethan Hunt!
## 2 At stake, as ever, is the fate of the universe. Why must it always be the universe? What's wrong with the fate of Hackensack? Doesn't anyone care what happens to South Dakota, or Denmark, or Peru?
## 3 This is a movie about the sound of fear, but it gives us a great deal more to listen to.
## 4 Deadpool 2 is an R-rated, potty-mouthed splatterfest and a funny one.
## 5 How great to see them back in this funny, exciting, if less audacious film.
## 6 Once it's over, you won't necessarily be itching for an Equalizer 3.# query all fields, limit by first 5 results
mgo$find('{}', limit = 5)##
Found 5 records...
Imported 5 records. Simplifying into dataframe...## username firstname lastname title category length
## 1 RussellS Russell S Mission Impossible: Fallout action 148
## 2 RussellS Russell S Avengers: Infinity War action 149
## 3 RussellS Russell S The Quiet Place horror 90
## 4 RussellS Russell S DeadPool 2 comedy 119
## 5 RussellS Russell S Incredibles 2 animation 118
## rating
## 1 5
## 2 2
## 3 5
## 4 5
## 5 5
## review
## 1 The best thrilling movie ever made! With the best movie character ever portrayed: Tom Cruise as Ethan Hunt!
## 2 At stake, as ever, is the fate of the universe. Why must it always be the universe? What's wrong with the fate of Hackensack? Doesn't anyone care what happens to South Dakota, or Denmark, or Peru?
## 3 This is a movie about the sound of fear, but it gives us a great deal more to listen to.
## 4 Deadpool 2 is an R-rated, potty-mouthed splatterfest and a funny one.
## 5 How great to see them back in this funny, exciting, if less audacious film.R makes the loading of dataframes into MongoDB extremely simple with the user of its libaries. As long as the data is in a dataframe format, the loading is simple and intuitive.