Data607 HW 13

Read the DB password for from the file

password = read.table("C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/flights/pass.txt")

## Warning in read.table("C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/
## flights/pass.txt"): incomplete final line found by readTableHeader on 'C:/
## ProgramData/MySQL/MySQL Server 5.7/Uploads/flights/pass.txt'

install.packages(‘RMySQL’,type=‘source’)

install.packages(‘mongolite’,type=‘source’)

library(RMySQL)

## Loading required package: DBI

library(DBI)
library(ggplot2)
library(mongolite)

Connection to the MySQL:

moviesdb = dbConnect(MySQL(), user='root', password=toString(password[1,1]), dbname='movies', host='localhost')

List the fields in demo table:

dbListFields(moviesdb, 'demo')

## [1] "name"    "name_id" "state"   "age"

List the fields in ratings table:

dbListFields(moviesdb, 'ratings')

## [1] "name_id"    "movie_name" "rating"

Frequency of the ratings:

freq_df<-dbGetQuery(moviesdb,'select movie_name, rating, count(rating) as count
from ratings
group by movie_name,rating
order by movie_name, rating;')

View the frequency distribution:

freq_df

##           movie_name rating count
## 1        Baby Driver      1     5
## 2        Baby Driver      2     5
## 3        Baby Driver      3     9
## 4        Baby Driver      4     1
## 5        Baby Driver      5     4
## 6  Blade Runner 2049      1     4
## 7  Blade Runner 2049      2     7
## 8  Blade Runner 2049      3     4
## 9  Blade Runner 2049      4     7
## 10 Blade Runner 2049      5     2
## 11           Dunkirk      1     4
## 12           Dunkirk      2     1
## 13           Dunkirk      3     4
## 14           Dunkirk      4     6
## 15           Dunkirk      5     9
## 16              Lion      1     2
## 17              Lion      2     4
## 18              Lion      3     4
## 19              Lion      4     8
## 20              Lion      5     6
## 21            Wonder      1     9
## 22            Wonder      2     9
## 23            Wonder      3     3
## 24            Wonder      4     2
## 25            Wonder      5     1

Joining the 2 tables and prepare the dataframes:

movies_df<-dbGetQuery(moviesdb, 'select 
a.name, 
a.name_id, 
a.state, 
a.age,
b.movie_name,
b.rating
from demo a
left join ratings b
on a.name_id=b.name_id
order by b.rating
;')

Contents of the dataframe

head(movies_df)

##           name name_id state age        movie_name rating
## 1        David      A3    AZ   9       Baby Driver      1
## 2        Ralph     A21    VA  59            Wonder      1
## 3 Frank Cartel     A23    WV  65       Baby Driver      1
## 4   Sam Carter     A22    VA  55            Wonder      1
## 5     Divyanka     A14    OH  35            Wonder      1
## 6       Camron      A8    DE  20 Blade Runner 2049      1

Writing from Dataframe to MongoDB:

c=mongo(collection="movies_df", db="Movies")
c$insert(movies_df)

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

Checking for distinct movie name

c$distinct("movie_name")

## [1] "Dunkirk"           "Wonder"            "Baby Driver"      
## [4] "Blade Runner 2049" "Lion"

Checking for distinct name

c$distinct("name")

##  [1] "Curt Russel"   "Venugopal"     "Helen"         "Keshava"      
##  [5] "Smith"         "Ralph"         "John "         "Frank Cartel" 
##  [9] "Ritesh Lohiya" "Sam Carter"    "Sandeep"       "David"        
## [13] "Hari"          "Divyanka"      "Camron"        "Kunal"        
## [17] "Patrick"       "Dinesh"        "Dan Nelson"    "Brady"        
## [21] "Mike"          "Pradeep"       "Johnson"       "Rodgers"

Count the data in MongoDB

c$count()

## [1] 1440

Likewise we can do aggregate, find, info etc….

Comparisons:

Advantages of NoSql Databases:

1. Highly and easily scalable
2. No Schema or Fixed Data model
3. Maintaining NoSQL Servers is Less Expensive
4. Lesser Server Cost and open-Source
5. Support Integrated Caching

Disadvantages of NoSql Databases:

1. There are not many defined standards for NoSQL databases, so no two NoSQL databases are equal.
2. No Stored Procedures in mongodb (NoSql database).

Advantages of relational Databases:

1. Provide facility primary key, to uniquely identify the rows.
2. Each row contains a unique instance of data for the categories defined by the columns.
3. Declarative syntax.

Disadvantages of relational Databases:

1. Scalability: Users have to scale relational database on powerful servers that are expensive and difficult to handle.
2. Handling tables across different servers is difficult .
3. Complexity: If your data doesn’t fit into tables, then you need to design your database structure that will be complex and again difficult to handle.