In this Home Work, I have tried to connect to MYSQL database, get records from table and have created data.frames and migtrated the data to MongoDB.
SQL Tables: Have created three tables for this home work namely REVIEWER, MOVIE_NAMES and REIVIEW_MOVIE_RATINGS
Mongo
In MongoDB, everything is just a Collection. So all the individual fields will get moved as a single collection.
#Connecting to MySQL database using dbConnect. Password is not masked for home work purpose.
mydb = dbConnect(MySQL(), user='root', password='mysql@123', dbname='DATA607', host='localhost')
dbListTables(mydb)## [1] "movie_names" "review_movie_rating" "reviewer"# Now trying to get the 3 table data as individual data.frames.
reviewer <- dbGetQuery(mydb, "select * from reviewer")
movie_names <- dbGetQuery(mydb, "select * from movie_names")
ratings <- dbGetQuery(mydb, "select * from review_movie_rating")head(reviewer)## reviewer_id reviewer
## 1 1 KYLE
## 2 2 DUUBAR
## 3 3 JAI
## 4 4 JAAN
## 5 5 KELLY
## 6 6 GEORGIAhead(movie_names)## movie_id movie_names
## 1 1 The Shawshank Redemption
## 2 2 Harry Potter
## 3 3 The Matrix
## 4 4 Home Alone
## 5 5 The Godfather
## 6 6 Titanichead(ratings)## reviewer_id movie_id ratings
## 1 1 1 5
## 2 1 2 2
## 3 1 3 2
## 4 1 4 4
## 5 1 5 3
## 6 1 6 5##if (!require("RMongo")) install.packages('RMongo')
##library(RMongo)
##mongo = mongoDbConnect("test", "127.0.0.1", 27017)
##b=mongo.bson.from.df(reviewer)
##output <- dbInsertDocument(mongo, "reviewer", reviewer)
##output <- dbGetQuery(mongo, 'test_data', '{"foo": "bar"}')
##print(output)
# Connectiong to MongoDB using MongoLite pacakge
con=mongo(collection = "test", db = "test", url = "mongodb://localhost",
verbose = FALSE, options = ssl_options())# Inserting in to MongoDB
con$insert(reviewer)## List of 5
## $ nInserted : num 6
## $ nMatched : num 0
## $ nRemoved : num 0
## $ nUpserted : num 0
## $ writeErrors: list()con$insert(movie_names)## List of 5
## $ nInserted : num 6
## $ nMatched : num 0
## $ nRemoved : num 0
## $ nUpserted : num 0
## $ writeErrors: list()con$insert(ratings)## List of 5
## $ nInserted : num 36
## $ nMatched : num 0
## $ nRemoved : num 0
## $ nUpserted : num 0
## $ writeErrors: list()# Getting the count of rows
con$count()## [1] 48#Find Query to select all fields
alldata=con$find('{}')
knitr::kable(alldata)| reviewer_id | reviewer | movie_id | movie_names | ratings |
|---|---|---|---|---|
| 1 | KYLE | NA | NA | NA |
| 2 | DUUBAR | NA | NA | NA |
| 3 | JAI | NA | NA | NA |
| 4 | JAAN | NA | NA | NA |
| 5 | KELLY | NA | NA | NA |
| 6 | GEORGIA | NA | NA | NA |
| NA | NA | 1 | The Shawshank Redemption | NA |
| NA | NA | 2 | Harry Potter | NA |
| NA | NA | 3 | The Matrix | NA |
| NA | NA | 4 | Home Alone | NA |
| NA | NA | 5 | The Godfather | NA |
| NA | NA | 6 | Titanic | NA |
| 1 | NA | 1 | NA | 5 |
| 1 | NA | 2 | NA | 2 |
| 1 | NA | 3 | NA | 2 |
| 1 | NA | 4 | NA | 4 |
| 1 | NA | 5 | NA | 3 |
| 1 | NA | 6 | NA | 5 |
| 2 | NA | 1 | NA | 5 |
| 2 | NA | 2 | NA | 5 |
| 2 | NA | 3 | NA | 4 |
| 2 | NA | 4 | NA | 4 |
| 2 | NA | 5 | NA | 3 |
| 2 | NA | 6 | NA | 5 |
| 3 | NA | 1 | NA | 2 |
| 3 | NA | 2 | NA | 3 |
| 3 | NA | 3 | NA | 1 |
| 3 | NA | 4 | NA | 4 |
| 3 | NA | 5 | NA | 4 |
| 3 | NA | 6 | NA | 5 |
| 4 | NA | 1 | NA | 2 |
| 4 | NA | 2 | NA | 5 |
| 4 | NA | 3 | NA | 4 |
| 4 | NA | 4 | NA | 2 |
| 4 | NA | 5 | NA | 4 |
| 4 | NA | 6 | NA | 5 |
| 5 | NA | 1 | NA | 5 |
| 5 | NA | 2 | NA | 5 |
| 5 | NA | 3 | NA | 4 |
| 5 | NA | 4 | NA | 4 |
| 5 | NA | 5 | NA | 4 |
| 5 | NA | 6 | NA | 5 |
| 6 | NA | 1 | NA | 4 |
| 6 | NA | 2 | NA | 5 |
| 6 | NA | 3 | NA | 3 |
| 6 | NA | 4 | NA | 4 |
| 6 | NA | 5 | NA | 5 |
| 6 | NA | 6 | NA | 5 |
#Remove all data from MongoDB
con$remove('{}')
#Drop the collection
con$drop()