Assignment 13 - Data 607

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.

• Connect to flights database in MySQL and list its existing tables.

db <- dbConnect(MySQL(), user='root', host='localhost', dbname='flights')
dbListTables(db)

## [1] "airlines" "airports" "flights"  "planes"   "weather"

• Query the flights db
• Load data into data frame and display part of its contents
• Count all of its rows
• Disconnect MySQL connection

query <- "select * from flights;"

flights.db <- dbGetQuery(conn=db, statement=query)
kable(head(flights.db))

year	month	day	dep_time	dep_delay	arr_time	arr_delay	carrier	tailnum	flight	origin	dest	air_time	distance	hour	minute
2013	1	1	517	2	830	11	UA	N14228	1545	EWR	IAH	227	1400	5	17
2013	1	1	533	4	850	20	UA	N24211	1714	LGA	IAH	227	1416	5	33
2013	1	1	542	2	923	33	AA	N619AA	1141	JFK	MIA	160	1089	5	42
2013	1	1	544	-1	1004	-18	B6	N804JB	725	JFK	BQN	183	1576	5	44
2013	1	1	554	-6	812	-25	DL	N668DN	461	LGA	ATL	116	762	6	54
2013	1	1	554	-4	740	12	UA	N39463	1696	EWR	ORD	150	719	6	54

nrow(flights.db)

## [1] 336776

dbDisconnect(db) 

## [1] TRUE

• Prepare flights document in Mongodb
• Insert to Mongodb
• Find in MongoDB, load data into data frame and list part of its contents
• Count all of its rows

mongo.collection <- mongo(collection = "flights")
if (mongo.collection$count()>0)(mongo.collection$drop())

## [1] TRUE

mongo.collection$insert(flights.db)

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

mongo.list <- mongo.collection$find() 
kable(head(mongo.list))

year	month	day	dep_time	dep_delay	arr_time	arr_delay	carrier	tailnum	flight	origin	dest	air_time	distance	hour	minute
2013	1	1	517	2	830	11	UA	N14228	1545	EWR	IAH	227	1400	5	17
2013	1	1	533	4	850	20	UA	N24211	1714	LGA	IAH	227	1416	5	33
2013	1	1	542	2	923	33	AA	N619AA	1141	JFK	MIA	160	1089	5	42
2013	1	1	544	-1	1004	-18	B6	N804JB	725	JFK	BQN	183	1576	5	44
2013	1	1	554	-6	812	-25	DL	N668DN	461	LGA	ATL	116	762	6	54
2013	1	1	554	-4	740	12	UA	N39463	1696	EWR	ORD	150	719	6	54

nrow(mongo.list)

## [1] 336776

• SQL vs NoSQL: MySQL vs MongoDB

MySQL: The SQL Relational Database

• Schema: Strict
• Type: RDBMS
• Structure: Table, row, column
• Maturity: Established database - backed with a huge community - somehow stable.
• Scalibility: Vertically scalable
• Cost-effective: Open source and free.
• Replicable: It can be replicated across multiple nodes, meaning that the workload can be reduced and the scalability and availability of the application can be increased.
• Sharding: While sharding cannot be done on most SQL databases, it can be done on MySQL servers. This is both cost-effective and good for business.

MongoDB: The NoSQL - Non-Relational Database

• Schema: Dynamic
• Type: Document-oriented
• Structure: Collection, document, field
• Scalability: Horizontally scalable reducing the workload and scaling business with ease.
• Manageability: No database administrator needed.
• Cost-effective: Open source and free.
• Speed: High-performance for simple queries.
• Flexibility: JSON based documents. New columns or fields can be added without affecting the existing rows or application performance in general.