In this assignment, we are going to take information from a relational database and migrate it to a MongoDB database.
In my SQL we have a Flight database. We are going to migrate that database to mongoDB database using mongolite library.

Select all the data from the Flight database by tables.

#Upload needed libraries
library(DBI)
library(RMySQL)

Connect to MySql database

con<-dbConnect(MySQL(),user='root', password='Clorox1!', dbname='flights')

Get the data from all the tables int the database

Airlines<-dbGetQuery(con,"select * from airlines")
Airports<-dbGetQuery(con, "select* from airports")
Flights<-dbGetQuery(con,"select * from flights")
Planes<-dbGetQuery(con,"select * from planes")
weather<-dbGetQuery(con,"select * from weather")

Disconnect to the Database

dbDisconnect(con)
## [1] TRUE

Migrate to MongoDB

Upload the mongolite library

library(mongolite)

Create Flights Collection

mongodb<-mongo(collection = "Flights")

Insert Airlines to mongodb

mongodb$insert(Airlines)
## List of 5
##  $ nInserted  : num 16
##  $ nMatched   : num 0
##  $ nRemoved   : num 0
##  $ nUpserted  : num 0
##  $ writeErrors: list()

Insert Airports to MongoDB

mongodb$insert(Airports)
## List of 5
##  $ nInserted  : num 1397
##  $ nMatched   : num 0
##  $ nRemoved   : num 0
##  $ nUpserted  : num 0
##  $ writeErrors: list()

Insert Flights to mongodb

mongodb$insert(Flights)
## List of 5
##  $ nInserted  : num 336776
##  $ nMatched   : num 0
##  $ nRemoved   : num 0
##  $ nUpserted  : num 0
##  $ writeErrors: list()

Insert Planes to mongodb

mongodb$insert(Planes)
## List of 5
##  $ nInserted  : num 3322
##  $ nMatched   : num 0
##  $ nRemoved   : num 0
##  $ nUpserted  : num 0
##  $ writeErrors: list()

Insert weather to mongodb

mongodb$insert(weather)
## List of 5
##  $ nInserted  : num 8719
##  $ nMatched   : num 0
##  $ nRemoved   : num 0
##  $ nUpserted  : num 0
##  $ writeErrors: list()

Comparison of MongoDB and MySQL

MySQL is an open source relational database that stores data in table and used structured query language. In my SQL you define schemas and set up rules to govern the relationship between field in your tables. You cannot change the schemas without taking the database offline.

MongoDB is also an open source non-relational database that stores data as document in a binary representation called BSON (Binary JSON). related information is stored together for faster retrieval. MongoDB is built on a distributed system architecture rather than a monolithic, single node design. MongoDB is easily scalable and replica set to maintain always-on capabilities.

tab<-"
|MySQL     |MongoDB               |
|----------|----------------------|
|ACID      |ACID                  |
|----------|----------------------|
|Table     |Collection            |
|----------|----------------------|
|Row       |Document              |
|----------|----------------------|
|Column    |Field                 |
|----------|----------------------|
|Joins     |Embedded              |
|----------|----------------------|
|Group_by  |Aggregation Pipeline  |
|----------|----------------------|
"
cat(tab)
## 
## |MySQL     |MongoDB               |
## |----------|----------------------|
## |ACID      |ACID                  |
## |----------|----------------------|
## |Table     |Collection            |
## |----------|----------------------|
## |Row       |Document              |
## |----------|----------------------|
## |Column    |Field                 |
## |----------|----------------------|
## |Joins     |Embedded              |
## |----------|----------------------|
## |Group_by  |Aggregation Pipeline  |
## |----------|----------------------|