data607-illien-project4
Mael Illien
10/23/2019
Assignment 12 - NoSQL Migration
In this project, we are tasked with migrating content from a SQL database to a NoSQL database. We will perform this migration with content from Assigment 2 stored in MySQL and migrate it to MondoDB via the intermediary of R using the packages RMySQL and rmongodb.
Setup
Load required Libraries.
A helper function for displaying tables
MySQL Connection
We create a connection to the MySQL server and load the movies data in the data frame ‘movies’
mydb = dbConnect(MySQL(), user = user, password = password, dbname = 'movies', host = 'localhost')
movies <- dbGetQuery(mydb, "select * from movies")Verify that the data has been properly queried by taking a look at sampled content of movies.
| review_id | title | reviewer | rating |
|---|---|---|---|
| 14 | John Wick | Bruce | NA |
| 8 | Venom | Bruce | NA |
| 34 | Hobbes & Shaw | George | NA |
| 20 | The Lion King | Bruce | 5 |
| 2 | Roma | Bruce | 3 |
| 27 | Avengers | Elie | 5 |
| 7 | Venom | Jason | 3 |
| 6 | Roma | Frank | NA |
| 22 | The Lion King | Brian | NA |
| 29 | Avengers | Brian | 5 |
MongoDB Connection
First we create a new database called movie_reviews in the shell as follows:
mongo
use movie_reviews
We then connect to MongoDB in R.
## [1] TRUEVerify that the movie_reviews database we created in the shell is present.
## [1] "config" "movie_reviews" "mytest"Migration
Use the mongo.bson.from.df function from the rmongodb package to convert the movies data frame built from data queried from MySQL into bson format.
Insert the bson data into the reviews collection of the movie_reviews database.
## [1] TRUE## _id : 7 5dd60d0b95234f667a7acc24
## 1 : 3
## review_id : 16 1
## title : 2 Roma
## reviewer : 2 Jason
## rating : 16 5
##
## 2 : 3
## review_id : 16 2
## title : 2 Roma
## reviewer : 2 Bruce
## rating : 16 3
##
## 3 : 3
## review_id : 16 3
## title : 2 Roma
## reviewer : 2 Elie
## rating : 16 3
##
## 4 : 3
## review_id : 16 4
## title : 2 Roma
## reviewer : 2 George
## rating : 16 4
##
## 5 : 3
## review_id : 16 5
## title : 2 Roma
## reviewer : 2 Brian
## rating : 10 BSON_NULL
##
## 6 : 3
## review_id : 16 6
## title : 2 Roma
## reviewer : 2 Frank
## rating : 10 BSON_NULL
##
## 7 : 3
## review_id : 16 7
## title : 2 Venom
## reviewer : 2 Jason
## rating : 16 3
##
## 8 : 3
## review_id : 16 8
## title : 2 Venom
## reviewer : 2 Bruce
## rating : 10 BSON_NULL
##
## 9 : 3
## review_id : 16 9
## title : 2 Venom
## reviewer : 2 Elie
## rating : 16 4
##
## 10 : 3
## review_id : 16 10
## title : 2 Venom
## reviewer : 2 George
## rating : 16 3
##
## 11 : 3
## review_id : 16 11
## title : 2 Venom
## reviewer : 2 Brian
## rating : 16 2
##
## 12 : 3
## review_id : 16 12
## title : 2 Venom
## reviewer : 2 Frank
## rating : 16 3
##
## 13 : 3
## review_id : 16 13
## title : 2 John Wick
## reviewer : 2 Jason
## rating : 16 4
##
## 14 : 3
## review_id : 16 14
## title : 2 John Wick
## reviewer : 2 Bruce
## rating : 10 BSON_NULL
##
## 15 : 3
## review_id : 16 15
## title : 2 John Wick
## reviewer : 2 Elie
## rating : 16 5
##
## 16 : 3
## review_id : 16 16
## title : 2 John Wick
## reviewer : 2 George
## rating : 16 4
##
## 17 : 3
## review_id : 16 17
## title : 2 John Wick
## reviewer : 2 Brian
## rating : 16 3
##
## 18 : 3
## review_id : 16 18
## title : 2 John Wick
## reviewer : 2 Frank
## rating : 10 BSON_NULL
##
## 19 : 3
## review_id : 16 19
## title : 2 The Lion King
## reviewer : 2 Jason
## rating : 16 5
##
## 20 : 3
## review_id : 16 20
## title : 2 The Lion King
## reviewer : 2 Bruce
## rating : 16 5
##
## 21 : 3
## review_id : 16 21
## title : 2 The Lion King
## reviewer : 2 Elie
## rating : 16 5
##
## 22 : 3
## review_id : 16 22
## title : 2 The Lion King
## reviewer : 2 Brian
## rating : 10 BSON_NULL
##
## 23 : 3
## review_id : 16 23
## title : 2 The Lion King
## reviewer : 2 George
## rating : 10 BSON_NULL
##
## 24 : 3
## review_id : 16 24
## title : 2 The Lion King
## reviewer : 2 Frank
## rating : 16 5
##
## 25 : 3
## review_id : 16 25
## title : 2 Avengers
## reviewer : 2 Jason
## rating : 16 5
##
## 26 : 3
## review_id : 16 26
## title : 2 Avengers
## reviewer : 2 Bruce
## rating : 16 4
##
## 27 : 3
## review_id : 16 27
## title : 2 Avengers
## reviewer : 2 Elie
## rating : 16 5
##
## 28 : 3
## review_id : 16 28
## title : 2 Avengers
## reviewer : 2 George
## rating : 16 5
##
## 29 : 3
## review_id : 16 29
## title : 2 Avengers
## reviewer : 2 Brian
## rating : 16 5
##
## 30 : 3
## review_id : 16 30
## title : 2 Avengers
## reviewer : 2 Frank
## rating : 16 4
##
## 31 : 3
## review_id : 16 31
## title : 2 Hobbes & Shaw
## reviewer : 2 Jason
## rating : 16 3
##
## 32 : 3
## review_id : 16 32
## title : 2 Hobbes & Shaw
## reviewer : 2 Bruce
## rating : 10 BSON_NULL
##
## 33 : 3
## review_id : 16 33
## title : 2 Hobbes & Shaw
## reviewer : 2 Elie
## rating : 16 3
##
## 34 : 3
## review_id : 16 34
## title : 2 Hobbes & Shaw
## reviewer : 2 George
## rating : 10 BSON_NULL
##
## 35 : 3
## review_id : 16 35
## title : 2 Hobbes & Shaw
## reviewer : 2 Brian
## rating : 10 BSON_NULL
##
## 36 : 3
## review_id : 16 36
## title : 2 Hobbes & Shaw
## reviewer : 2 Frank
## rating : 10 BSON_NULLWe verify in the shell that the migration was successful.
db.reviews.find()
Conclusion
In this project, we were able to successfully migrate the data from a SQL-type database to a NoSQL database using R via the RMySQL and rmongodb packages. The migrated data was originally stored in a single table. A future challenge could be to migrate data from a SQL database where there are multiple tables, and to re-design the way the data is to be stored in the “unstructured” document database MongoDB.