For this assignment, you should take information from a relational database and migrate it to a NoSQL database of your own choosing.

For the relational database, you might use the flights database, the tb database, the “data skills” database your team created for Project 3, or another database of your own choosing or creation.

For the NoSQL database, you may use MongoDB (which we introduced in week 7), Neo4j, or another NoSQL database of your choosing.

Your migration process needs to be reproducible. R code is encouraged, but not required. You should also briefly describe the advantages and disadvantages of storing the data in a relational database vs. your NoSQL database.

Assumption: in MySQL “ASSIGNMENT12” database exists.

Loading necessary packages

library(nycflights13)
library("RMySQL")
## Loading required package: DBI
library(DBI)
library("mongolite")

Variables declaration

airlines.table.name <- "AIRLINES"
mysql.db.name <- "ASSIGNMENT12"
mysql.password <- "olga123"
mysql.user.name <- "root"
local.host="localhost"
mongo.url = sprintf("mongodb://%s", local.host )

Initialise connection to MySQl

mysql.connection <- dbConnect(MySQL(),
                        user=mysql.user.name, password=mysql.password,
                        dbname=mysql.db.name, host=local.host)

Initialise connection to MongoDB

mongo.connection <- mongo(airlines.table.name,url = mongo.url)

Insert Airlines table to MySQL

dbWriteTable(mysql.connection,airlines.table.name,airlines, overwrite = TRUE)
## [1] TRUE

Read data from Airlines table into a dataframe and close connection.

sql.df <- dbReadTable(mysql.connection, airlines.table.name)
dbDisconnect(mysql.connection)
## [1] TRUE

Printing dataframe

sql.df
##    carrier                        name
## 1       9E           Endeavor Air Inc.
## 2       AA      American Airlines Inc.
## 3       AS        Alaska Airlines Inc.
## 4       B6             JetBlue Airways
## 5       DL        Delta Air Lines Inc.
## 6       EV    ExpressJet Airlines Inc.
## 7       F9      Frontier Airlines Inc.
## 8       FL AirTran Airways Corporation
## 9       HA      Hawaiian Airlines Inc.
## 10      MQ                   Envoy Air
## 11      OO       SkyWest Airlines Inc.
## 12      UA       United Air Lines Inc.
## 13      US             US Airways Inc.
## 14      VX              Virgin America
## 15      WN      Southwest Airlines Co.
## 16      YV          Mesa Airlines Inc.

Insert dataframe into MongoDB and read the data frame.

mongo.connection$insert(sql.df)
## List of 5
##  $ nInserted  : num 16
##  $ nMatched   : num 0
##  $ nRemoved   : num 0
##  $ nUpserted  : num 0
##  $ writeErrors: list()
mongo.df <- mongo.connection$find('{}')
mongo.connection$disconnect()

Printing dataframe

mongo.df
##    carrier                        name
## 1       9E           Endeavor Air Inc.
## 2       AA      American Airlines Inc.
## 3       AS        Alaska Airlines Inc.
## 4       B6             JetBlue Airways
## 5       DL        Delta Air Lines Inc.
## 6       EV    ExpressJet Airlines Inc.
## 7       F9      Frontier Airlines Inc.
## 8       FL AirTran Airways Corporation
## 9       HA      Hawaiian Airlines Inc.
## 10      MQ                   Envoy Air
## 11      OO       SkyWest Airlines Inc.
## 12      UA       United Air Lines Inc.
## 13      US             US Airways Inc.
## 14      VX              Virgin America
## 15      WN      Southwest Airlines Co.
## 16      YV          Mesa Airlines Inc.
## 17      9E           Endeavor Air Inc.
## 18      AA      American Airlines Inc.
## 19      AS        Alaska Airlines Inc.
## 20      B6             JetBlue Airways
## 21      DL        Delta Air Lines Inc.
## 22      EV    ExpressJet Airlines Inc.
## 23      F9      Frontier Airlines Inc.
## 24      FL AirTran Airways Corporation
## 25      HA      Hawaiian Airlines Inc.
## 26      MQ                   Envoy Air
## 27      OO       SkyWest Airlines Inc.
## 28      UA       United Air Lines Inc.
## 29      US             US Airways Inc.
## 30      VX              Virgin America
## 31      WN      Southwest Airlines Co.
## 32      YV          Mesa Airlines Inc.
## 33      9E           Endeavor Air Inc.
## 34      AA      American Airlines Inc.
## 35      AS        Alaska Airlines Inc.
## 36      B6             JetBlue Airways
## 37      DL        Delta Air Lines Inc.
## 38      EV    ExpressJet Airlines Inc.
## 39      F9      Frontier Airlines Inc.
## 40      FL AirTran Airways Corporation
## 41      HA      Hawaiian Airlines Inc.
## 42      MQ                   Envoy Air
## 43      OO       SkyWest Airlines Inc.
## 44      UA       United Air Lines Inc.
## 45      US             US Airways Inc.
## 46      VX              Virgin America
## 47      WN      Southwest Airlines Co.
## 48      YV          Mesa Airlines Inc.
## 49      9E           Endeavor Air Inc.
## 50      AA      American Airlines Inc.
## 51      AS        Alaska Airlines Inc.
## 52      B6             JetBlue Airways
## 53      DL        Delta Air Lines Inc.
## 54      EV    ExpressJet Airlines Inc.
## 55      F9      Frontier Airlines Inc.
## 56      FL AirTran Airways Corporation
## 57      HA      Hawaiian Airlines Inc.
## 58      MQ                   Envoy Air
## 59      OO       SkyWest Airlines Inc.
## 60      UA       United Air Lines Inc.
## 61      US             US Airways Inc.
## 62      VX              Virgin America
## 63      WN      Southwest Airlines Co.
## 64      YV          Mesa Airlines Inc.

MySQL VS MongoDB

MySQl:

MongoDB: