A. Summary

In this assignment, I did the following tasks:

• I downloaded the Mongo Community Edition 4.0.4 onto my Windows machine.

• I ran the MongoDb server, by going into MS-DOS, going to directory, c:<Mongo Installation Path>directory and executed mongod.exe executable.

• I opened up the connection to the mongo collections airlines, airports, flights, and planes, and cleared their collections completely by using the drop command

• I connected to my local MySQL database and queried against all the tables of the flights database and stored them as data frames.

• I created a mongo collection for each mysql-based data frame and migrated the data using the insert command.

• I performed mongo queries to verify that each table outputted the results that I wanted via kabble.

B. Caveats and Limitations

• I only used the default installation for the Mongo database server with no required user credentials.

• For the MySQL connection, I used a ubiquitous user called school with no password. In a normal secure environment, password will always be required.

• Because of subsequent runs of the program to get it to work, it is assumed the MongoDB database already contains existing collections for airlines, airports, flights, and planes.

• I only did simple queries to very the table queries in Mongo worked correctly.

A. Load Required Libraries

library(RMySQL)

## Warning: package 'RMySQL' was built under R version 3.4.4

## Loading required package: DBI

library(mongolite)
library(stringr)
library(XML)
library(RCurl)

## Loading required package: bitops

library(rvest)

## Loading required package: xml2

## 
## Attaching package: 'rvest'

## The following object is masked from 'package:XML':
## 
##     xml

library(tidytext)
library(tidyverse)

## -- Attaching packages -------------------------------------------------------------------------------- tidyverse 1.2.1 --

## v ggplot2 2.2.1     v readr   1.1.1
## v tibble  1.4.1     v purrr   0.2.4
## v tidyr   0.7.2     v dplyr   0.7.4
## v ggplot2 2.2.1     v forcats 0.2.0

## -- Conflicts ----------------------------------------------------------------------------------- tidyverse_conflicts() --
## x tidyr::complete()       masks RCurl::complete()
## x dplyr::filter()         masks stats::filter()
## x readr::guess_encoding() masks rvest::guess_encoding()
## x dplyr::lag()            masks stats::lag()
## x purrr::pluck()          masks rvest::pluck()
## x rvest::xml()            masks XML::xml()

library(tm)

## Loading required package: NLP

## 
## Attaching package: 'NLP'

## The following object is masked from 'package:ggplot2':
## 
##     annotate

library(knitr)
library(kableExtra)

options(knitr.table.format = "html")

B. Connect to Mongo database

In this code snippet, I open up a connection to each of the collections of the Mongo DB server, verify that each connection is valid, and clear its contents by using the drop method.

mairlines <- mongo("airlines")
mairports <- mongo("airports")
mflights <- mongo("flights")
mplanes <- mongo("planes")

mairlines

## <Mongo collection> 'airlines' 
##  $aggregate(pipeline = "{}", options = "{\"allowDiskUse\":true}", handler = NULL, pagesize = 1000) 
##  $count(query = "{}") 
##  $distinct(key, query = "{}") 
##  $drop() 
##  $export(con = stdout(), bson = FALSE) 
##  $find(query = "{}", fields = "{\"_id\":0}", sort = "{}", skip = 0, limit = 0, handler = NULL, pagesize = 1000) 
##  $import(con, bson = FALSE) 
##  $index(add = NULL, remove = NULL) 
##  $info() 
##  $insert(data, pagesize = 1000, stop_on_error = TRUE, ...) 
##  $iterate(query = "{}", fields = "{\"_id\":0}", sort = "{}", skip = 0, limit = 0) 
##  $mapreduce(map, reduce, query = "{}", sort = "{}", limit = 0, out = NULL, scope = NULL) 
##  $remove(query, just_one = FALSE) 
##  $rename(name, db = NULL) 
##  $update(query, update = "{\"$set\":{}}", filters = "[]", upsert = FALSE, multiple = FALSE)

mairports

## <Mongo collection> 'airports' 
##  $aggregate(pipeline = "{}", options = "{\"allowDiskUse\":true}", handler = NULL, pagesize = 1000) 
##  $count(query = "{}") 
##  $distinct(key, query = "{}") 
##  $drop() 
##  $export(con = stdout(), bson = FALSE) 
##  $find(query = "{}", fields = "{\"_id\":0}", sort = "{}", skip = 0, limit = 0, handler = NULL, pagesize = 1000) 
##  $import(con, bson = FALSE) 
##  $index(add = NULL, remove = NULL) 
##  $info() 
##  $insert(data, pagesize = 1000, stop_on_error = TRUE, ...) 
##  $iterate(query = "{}", fields = "{\"_id\":0}", sort = "{}", skip = 0, limit = 0) 
##  $mapreduce(map, reduce, query = "{}", sort = "{}", limit = 0, out = NULL, scope = NULL) 
##  $remove(query, just_one = FALSE) 
##  $rename(name, db = NULL) 
##  $update(query, update = "{\"$set\":{}}", filters = "[]", upsert = FALSE, multiple = FALSE)

mflights

## <Mongo collection> 'flights' 
##  $aggregate(pipeline = "{}", options = "{\"allowDiskUse\":true}", handler = NULL, pagesize = 1000) 
##  $count(query = "{}") 
##  $distinct(key, query = "{}") 
##  $drop() 
##  $export(con = stdout(), bson = FALSE) 
##  $find(query = "{}", fields = "{\"_id\":0}", sort = "{}", skip = 0, limit = 0, handler = NULL, pagesize = 1000) 
##  $import(con, bson = FALSE) 
##  $index(add = NULL, remove = NULL) 
##  $info() 
##  $insert(data, pagesize = 1000, stop_on_error = TRUE, ...) 
##  $iterate(query = "{}", fields = "{\"_id\":0}", sort = "{}", skip = 0, limit = 0) 
##  $mapreduce(map, reduce, query = "{}", sort = "{}", limit = 0, out = NULL, scope = NULL) 
##  $remove(query, just_one = FALSE) 
##  $rename(name, db = NULL) 
##  $update(query, update = "{\"$set\":{}}", filters = "[]", upsert = FALSE, multiple = FALSE)

mplanes

## <Mongo collection> 'planes' 
##  $aggregate(pipeline = "{}", options = "{\"allowDiskUse\":true}", handler = NULL, pagesize = 1000) 
##  $count(query = "{}") 
##  $distinct(key, query = "{}") 
##  $drop() 
##  $export(con = stdout(), bson = FALSE) 
##  $find(query = "{}", fields = "{\"_id\":0}", sort = "{}", skip = 0, limit = 0, handler = NULL, pagesize = 1000) 
##  $import(con, bson = FALSE) 
##  $index(add = NULL, remove = NULL) 
##  $info() 
##  $insert(data, pagesize = 1000, stop_on_error = TRUE, ...) 
##  $iterate(query = "{}", fields = "{\"_id\":0}", sort = "{}", skip = 0, limit = 0) 
##  $mapreduce(map, reduce, query = "{}", sort = "{}", limit = 0, out = NULL, scope = NULL) 
##  $remove(query, just_one = FALSE) 
##  $rename(name, db = NULL) 
##  $update(query, update = "{\"$set\":{}}", filters = "[]", upsert = FALSE, multiple = FALSE)

mairlines$drop()
mairports$drop()
mflights$drop()
mplanes$drop()

C. Connect to MySQL Database

I connected to the MySQL database and show that my connection is valid.

con <- dbConnect(RMySQL::MySQL(), dbname = "flights", username = "school")
summary(con)

## <MySQLConnection:0,0>
##   User:   school 
##   Host:   localhost 
##   Dbname: flights 
##   Connection type: localhost via TCP/IP 
## 
## Results:

D. Use SELECT statements to place query results into data frames

This is pretty straightforward. Using the dbGetQuery function and selecting all the values from each table, it is very easy to store them into a data frame.

dfAirlines <- dbGetQuery(con, "SELECT * FROM airlines")
dfAirports <- dbGetQuery(con, "SELECT * FROM airports")
dfFlights <- dbGetQuery(con, "SELECT * FROM flights")
dfPlanes <- dbGetQuery(con, "SELECT * FROM planes")

E. Migrate resultant data frames into their Mongo DB collections

This is another straightforward procedure. Using the insert method for each Mongo collection, I can easily migrate al the components of the data frame.

mairlines$insert(dfAirlines)

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

mairports$insert(dfAirports)

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

mflights$insert(dfFlights)

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

mplanes$insert(dfPlanes)

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

F. Mongo airlines table query

To verify, that I can see airlines values, I did a simple query to show all values.

mairlinesDF <- mairlines$find('{}')
mairlinesDF %>% 
  kable () %>% 
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  scroll_box(width="100%",height="300px")

G. Mongo airports table query

To verify that I can see airports values, I did a simple query to show all values.

mairportsDF <- mairports$find('{}')
mairportsDF %>% 
  kable () %>% 
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  scroll_box(width="100%",height="300px")