CUNY MSDS Data 607 HW #8

Overview

In this assignment, information from a relational database, MySQL, is migrated to the NoSQL database called MongoDB. Later, there is a briefly discussion on the advantages and disadvantages of storing the data in a relational database versus a NoSQL database.

Data Acquisition

MySQL is a relational database that allows tables to be joined together and also supports the concept of foreign keys.

Step 1

Load the database interface and MySQL driver for R called RMySQL.

library(RMySQL)

Step 2

Connect R to MySQL server.

mydb <- dbConnect(MySQL(), user = 'root', password='XXXXXX', dbname = 'Database name', host = 'localhost', port=3306)

Step 3

Retrieving the database and make it available in RStudio.

retrieved <- dbSendQuery(mydb, "SELECT * FROM shineeranking")
dataset <- tidyr::gather(fetch(retrieved, n = -1),"rank", "value", 3:17)

# The database
library(kableExtra)
kable(dataset) %>%
  kable_styling() %>%
  scroll_box(width = "100%", height = "200px")

ID	mvs	rank	value
1	Sherlock	first_visual	0
2	Everybody	first_visual	8
3	View	first_visual	13
4	Tell Me What To Do	first_visual	15
5	Good Evening	first_visual	14
1	Sherlock	second_visual	0
2	Everybody	second_visual	12
3	View	second_visual	12
4	Tell Me What To Do	second_visual	6
5	Good Evening	second_visual	20
1	Sherlock	third_visual	14
2	Everybody	third_visual	11
3	View	third_visual	4
4	Tell Me What To Do	third_visual	14
5	Good Evening	third_visual	7
1	Sherlock	forth_visual	21
2	Everybody	forth_visual	11
3	View	forth_visual	10
4	Tell Me What To Do	forth_visual	4
5	Good Evening	forth_visual	4
1	Sherlock	fifth_visual	15
2	Everybody	fifth_visual	8
3	View	fifth_visual	11
4	Tell Me What To Do	fifth_visual	11
5	Good Evening	fifth_visual	5
1	Sherlock	first_theme_story	10
2	Everybody	first_theme_story	7
3	View	first_theme_story	7
4	Tell Me What To Do	first_theme_story	10
5	Good Evening	first_theme_story	16
1	Sherlock	second_theme_story	7
2	Everybody	second_theme_story	4
3	View	second_theme_story	14
4	Tell Me What To Do	second_theme_story	9
5	Good Evening	second_theme_story	16
1	Sherlock	third_theme_story	14
2	Everybody	third_theme_story	2
3	View	third_theme_story	13
4	Tell Me What To Do	third_theme_story	9
5	Good Evening	third_theme_story	12
1	Sherlock	forth_theme_story	13
2	Everybody	forth_theme_story	13
3	View	forth_theme_story	8
4	Tell Me What To Do	forth_theme_story	12
5	Good Evening	forth_theme_story	4
1	Sherlock	fifth_theme_story	6
2	Everybody	fifth_theme_story	24
3	View	fifth_theme_story	8
4	Tell Me What To Do	fifth_theme_story	10
5	Good Evening	fifth_theme_story	2
1	Sherlock	first_performance	17
2	Everybody	first_performance	16
3	View	first_performance	6
4	Tell Me What To Do	first_performance	3
5	Good Evening	first_performance	8
1	Sherlock	second_performance	15
2	Everybody	second_performance	9
3	View	second_performance	10
4	Tell Me What To Do	second_performance	10
5	Good Evening	second_performance	6
1	Sherlock	third_performance	7
2	Everybody	third_performance	10
3	View	third_performance	17
4	Tell Me What To Do	third_performance	7
5	Good Evening	third_performance	9
1	Sherlock	forth_performance	9
2	Everybody	forth_performance	12
3	View	forth_performance	9
4	Tell Me What To Do	forth_performance	10
5	Good Evening	forth_performance	10
1	Sherlock	fifth_performance	2
2	Everybody	fifth_performance	3
3	View	fifth_performance	8
4	Tell Me What To Do	fifth_performance	20
5	Good Evening	fifth_performance	17

Step 4

Close and disconnect from MySQL database.

dbDisconnect(mydb)

## [1] TRUE

Database Migration

An account was made on MongoDB Atlas where a user’s credentials are used to create a connection.

Step 1

Load the MongoDB client for R called mongolite.

library(mongolite)

Step 2

Connect to MongoDB Atlas

mongofile <- mongo(collection = 'dataset', url = "mongodb+srv://<USER>:<PASSWORD>@cluster0-eurk1.mongodb.net/test")

Step 3

Drop databases in the MongoDB and add the data set.

mongofile$drop()
mongofile$insert(dataset)

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

kable(mongofile$find()) %>%
  kable_styling() %>%
  scroll_box(width = "100%", height = "200px")

ID	mvs	rank	value
1	Sherlock	first_visual	0
2	Everybody	first_visual	8
3	View	first_visual	13
4	Tell Me What To Do	first_visual	15
5	Good Evening	first_visual	14
1	Sherlock	second_visual	0
2	Everybody	second_visual	12
3	View	second_visual	12
4	Tell Me What To Do	second_visual	6
5	Good Evening	second_visual	20
1	Sherlock	third_visual	14
2	Everybody	third_visual	11
3	View	third_visual	4
4	Tell Me What To Do	third_visual	14
5	Good Evening	third_visual	7
1	Sherlock	forth_visual	21
2	Everybody	forth_visual	11
3	View	forth_visual	10
4	Tell Me What To Do	forth_visual	4
5	Good Evening	forth_visual	4
1	Sherlock	fifth_visual	15
2	Everybody	fifth_visual	8
3	View	fifth_visual	11
4	Tell Me What To Do	fifth_visual	11
5	Good Evening	fifth_visual	5
1	Sherlock	first_theme_story	10
2	Everybody	first_theme_story	7
3	View	first_theme_story	7
4	Tell Me What To Do	first_theme_story	10
5	Good Evening	first_theme_story	16
1	Sherlock	second_theme_story	7
2	Everybody	second_theme_story	4
3	View	second_theme_story	14
4	Tell Me What To Do	second_theme_story	9
5	Good Evening	second_theme_story	16
1	Sherlock	third_theme_story	14
2	Everybody	third_theme_story	2
3	View	third_theme_story	13
4	Tell Me What To Do	third_theme_story	9
5	Good Evening	third_theme_story	12
1	Sherlock	forth_theme_story	13
2	Everybody	forth_theme_story	13
3	View	forth_theme_story	8
4	Tell Me What To Do	forth_theme_story	12
5	Good Evening	forth_theme_story	4
1	Sherlock	fifth_theme_story	6
2	Everybody	fifth_theme_story	24
3	View	fifth_theme_story	8
4	Tell Me What To Do	fifth_theme_story	10
5	Good Evening	fifth_theme_story	2
1	Sherlock	first_performance	17
2	Everybody	first_performance	16
3	View	first_performance	6
4	Tell Me What To Do	first_performance	3
5	Good Evening	first_performance	8
1	Sherlock	second_performance	15
2	Everybody	second_performance	9
3	View	second_performance	10
4	Tell Me What To Do	second_performance	10
5	Good Evening	second_performance	6
1	Sherlock	third_performance	7
2	Everybody	third_performance	10
3	View	third_performance	17
4	Tell Me What To Do	third_performance	7
5	Good Evening	third_performance	9
1	Sherlock	forth_performance	9
2	Everybody	forth_performance	12
3	View	forth_performance	9
4	Tell Me What To Do	forth_performance	10
5	Good Evening	forth_performance	10
1	Sherlock	fifth_performance	2
2	Everybody	fifth_performance	3
3	View	fifth_performance	8
4	Tell Me What To Do	fifth_performance	20
5	Good Evening	fifth_performance	17

Comparison

MongoDB is a document-oriented database which stores information in key-value pair. Data is stored in JSON-like documents that can have varied structures. MySQL database, on the other hand, is the most commonly used relational database management system (RDBMS). It is built on the concept of storing data in rows and tables which are further classified into the database.

The return of true means that the item in the data set was matched, and successful stored onto MongoDB.

mdbdataset.df <- mongofile$find()

kable(mdbdataset.df == dataset) %>%
  kable_styling() %>%
  scroll_box(width = "100%", height = "200px")

ID	mvs	rank	value
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	TRUE	TRUE

Advantages

Advantages of Storing Data in a Relational Database

One of the best parts about MySQL is the JOIN operations, which makes the relational database ‘relational’.
MySQL follows the ACID (Atomic, Consistent, Isolated and Durable) model which means that once a transaction is complete, the data remains consistent and stable on the disc which may include distinct multiple memory locations.

Advantages of NoSQL over Relational Database

There is more flexibility as it is a no-schema database. This adds to the benefits of auto-sharding, embedding and on-board replication which in turn provides high scalability and availability.
It offers greater efficiency and reliability since MongoDB has features like replication and gridFS. These features help to increase data availability, hence the performance is very high.

Disadvantages

Disadvantages of storing Data in Relational Database

Before storing, tables and columns should be clearly define, and also, every row in the table should have the same column. Thus, there is not much space for flexibility.
Development and deployment process is slowed down as well due to the fact that even a little modification in data model mandates the change in schema design.

Disadvantages of NoSQL over Relational Database

MongoDB doesn’t support JOINs like a relational database. And, even if one can use joins functionality by coding it manually, it may slow execution and affect performance.
With the key-value pairs, and due to no functionality of JOINs, there is data redundancy. This results in increasing unnecessary usage of memory, on top of the already limited data size.

Conclusion

In conclusion, MySQL and MongoDB both have their advantages and disadvantages. If the data requires multiple row transactions or in need of a legacy application, a relational database may be the right choice. However, if in need of a more flexible, no-schema solution that can work with unstructured data, MongoDB may be the right choice. This SIMFORM webpage provides an in-dept review of the differences and similarities between MySQL and MongoDB.

CUNY MSDS Data 607 HW #8

Samantha Deokinanan

25th April, 2019

Overview

Data Acquisition

Step 1

Step 2

Step 3

Step 4

Database Migration

Step 1

Step 2

Step 3

Comparison

Advantages

Disadvantages

Conclusion