Rpubs link: http://rpubs.com/jefflittlejohn/Data_607_Week_12 Github link: https://github.com/littlejohnjeff/DATA607_Fall2018/blob/master/Assignment%20Week%2012%20-%20NoSQL%20Migration%20-%20Jeff%20Littlejohn.Rmd
Earlier in the semester, we had an assignment that required connecting to a MySQL relational database from R. This required installing and configuring MySQL on a local machine. Like other students, I was not able to connect to the most recent version of MySQL (8.0) at the time through R but was successful after downgrading to MySQL 5.7.
This assignment includes a directive to be reproducible. The party attempting to reproduce will need a MySQL instance that either shares the connection parameters of the one used here or will need to change the connection code as required to connect to their SQL environment. Additionally, that SQL database instance will need to have the flights database loaded. Here is code that will create the SQL objects and load the data for the flights database used in the assignment. After downloading that sql file, executing the script in your MySQL environment will do all the work.
MySQL Connection to Flights Database Through R
Load library RMySQL to enable connecting to MySQL database via RStudio.
library(RMySQL)Connect to the MySQL database on local machine. Recall the difficulties mentioned above.
mydb = dbConnect(MySQL(), user='root', password='password', host='127.0.0.1')Load Flights Database Tables into R Dataframes
#Use flights schema
dbSendQuery(mydb, 'USE flights;')## <MySQLResult:0,0,0>#load the 5 flights db tables into R
df_airlines <- dbGetQuery(mydb, "SELECT * FROM airlines")
df_airports <- dbGetQuery(mydb, "SELECT * FROM airports")
df_flights <- dbGetQuery(mydb, "SELECT * FROM flights")
df_planes <- dbGetQuery(mydb, "SELECT * FROM planes")
df_weather <- dbGetQuery(mydb, "SELECT * FROM weather")
#just checking successful load
head(df_weather)## origin year month day hour temp dewp humid wind_dir wind_speed
## 1 EWR 2013 1 1 0 37.04 21.92 53.97 230 10.35702
## 2 EWR 2013 1 1 1 37.04 21.92 53.97 230 13.80936
## 3 EWR 2013 1 1 2 37.94 21.92 52.09 230 12.65858
## 4 EWR 2013 1 1 3 37.94 23.00 54.51 230 13.80936
## 5 EWR 2013 1 1 4 37.94 24.08 57.04 240 14.96014
## 6 EWR 2013 1 1 6 39.02 26.06 59.37 270 10.35702
## wind_gust precip pressure visib
## 1 11.91865 0 1013.9 10
## 2 15.89154 0 1013.0 10
## 3 14.56724 0 1012.6 10
## 4 15.89154 0 1012.7 10
## 5 17.21583 0 1012.8 10
## 6 11.91865 0 1012.0 10MongoDB Time
The purpose of this exercise is to convert a database from a relational database management system (RDBMS) to a NoSQL database system.
While RDBMS have long been the backbone of data storage, they can have serious drawbacks. A relational database generally requires the creation of a schema with database objects that require datatypes and constraints. Developing a relational database takes a considerable amount of time and effort. Taking external data and transforming and loading it into that relational database can take even more time. Also, adding additional power or storage to a RDMBS can require significant investments. Finally, RDBMS can struggle with unstructured data such as images - or emails.
In contrast, NoSQL databases do not require the development of schemas and data models before they can accept data. They can hold unstructured data and expand “horizontally” with relative ease and lower expense. Let’s check out NoSQL database, MongoDB.
For this exercise, the server version - as opposed to the cloud edition - of MongoDB was downloaded from here.
Transform from R to MongoDB Format
#load the mongolite library
library(mongolite)Connect to the local MongoDB instance using mongolite. It’s a pity the Mongodb package is now orphaned, as it looks like it had cool features.
m <- mongo(collection = "airlines", db = "flights", url = "mongodb://127.0.0.1:27017")
print(m)## <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, ...)
## $iterate(query = "{}", fields = "{\"_id\":0}", sort = "{}", skip = 0, limit = 0)
## $mapreduce(map, reduce, query = "{}", sort = "{}", limit = 0, out = NULL, scope = NULL)
## $remove(query, multiple = FALSE)
## $rename(name, db = NULL)
## $update(query, update = "{\"$set\":{}}", upsert = FALSE, multiple = FALSE)Load Data to MongoDB
#load airlines data into airlines collection
m$insert(df_airlines)##
Complete! Processed total of 16 rows.## $nInserted
## [1] 16
##
## $nMatched
## [1] 0
##
## $nRemoved
## [1] 0
##
## $nUpserted
## [1] 0
##
## $writeErrors
## list()Airports. For each of these, we’re going to reset the connection to change the “collection” to match the table name from the RDBMS.
m <- mongo(collection = "airports", db = "flights", url = "mongodb://127.0.0.1:27017")
m$insert(df_airports)##
Processed 1000 rows...
Complete! Processed total of 1397 rows.## $nInserted
## [1] 1397
##
## $nMatched
## [1] 0
##
## $nRemoved
## [1] 0
##
## $nUpserted
## [1] 0
##
## $writeErrors
## list()Flights.
m <- mongo(collection = "flights", db = "flights", url = "mongodb://127.0.0.1:27017")
m$insert(df_flights)##
Processed 1000 rows...
Processed 2000 rows...
Processed 3000 rows...
Processed 4000 rows...
Processed 5000 rows...
Processed 6000 rows...
Processed 7000 rows...
Processed 8000 rows...
Processed 9000 rows...
Processed 10000 rows...
Processed 11000 rows...
Processed 12000 rows...
Processed 13000 rows...
Processed 14000 rows...
Processed 15000 rows...
Processed 16000 rows...
Processed 17000 rows...
Processed 18000 rows...
Processed 19000 rows...
Processed 20000 rows...
Processed 21000 rows...
Processed 22000 rows...
Processed 23000 rows...
Processed 24000 rows...
Processed 25000 rows...
Processed 26000 rows...
Processed 27000 rows...
Processed 28000 rows...
Processed 29000 rows...
Processed 30000 rows...
Processed 31000 rows...
Processed 32000 rows...
Processed 33000 rows...
Processed 34000 rows...
Processed 35000 rows...
Processed 36000 rows...
Processed 37000 rows...
Processed 38000 rows...
Processed 39000 rows...
Processed 40000 rows...
Processed 41000 rows...
Processed 42000 rows...
Processed 43000 rows...
Processed 44000 rows...
Processed 45000 rows...
Processed 46000 rows...
Processed 47000 rows...
Processed 48000 rows...
Processed 49000 rows...
Processed 50000 rows...
Processed 51000 rows...
Processed 52000 rows...
Processed 53000 rows...
Processed 54000 rows...
Processed 55000 rows...
Processed 56000 rows...
Processed 57000 rows...
Processed 58000 rows...
Processed 59000 rows...
Processed 60000 rows...
Processed 61000 rows...
Processed 62000 rows...
Processed 63000 rows...
Processed 64000 rows...
Processed 65000 rows...
Processed 66000 rows...
Processed 67000 rows...
Processed 68000 rows...
Processed 69000 rows...
Processed 70000 rows...
Processed 71000 rows...
Processed 72000 rows...
Processed 73000 rows...
Processed 74000 rows...
Processed 75000 rows...
Processed 76000 rows...
Processed 77000 rows...
Processed 78000 rows...
Processed 79000 rows...
Processed 80000 rows...
Processed 81000 rows...
Processed 82000 rows...
Processed 83000 rows...
Processed 84000 rows...
Processed 85000 rows...
Processed 86000 rows...
Processed 87000 rows...
Processed 88000 rows...
Processed 89000 rows...
Processed 90000 rows...
Processed 91000 rows...
Processed 92000 rows...
Processed 93000 rows...
Processed 94000 rows...
Processed 95000 rows...
Processed 96000 rows...
Processed 97000 rows...
Processed 98000 rows...
Processed 99000 rows...
Processed 1e+05 rows...
Processed 101000 rows...
Processed 102000 rows...
Processed 103000 rows...
Processed 104000 rows...
Processed 105000 rows...
Processed 106000 rows...
Processed 107000 rows...
Processed 108000 rows...
Processed 109000 rows...
Processed 110000 rows...
Processed 111000 rows...
Processed 112000 rows...
Processed 113000 rows...
Processed 114000 rows...
Processed 115000 rows...
Processed 116000 rows...
Processed 117000 rows...
Processed 118000 rows...
Processed 119000 rows...
Processed 120000 rows...
Processed 121000 rows...
Processed 122000 rows...
Processed 123000 rows...
Processed 124000 rows...
Processed 125000 rows...
Processed 126000 rows...
Processed 127000 rows...
Processed 128000 rows...
Processed 129000 rows...
Processed 130000 rows...
Processed 131000 rows...
Processed 132000 rows...
Processed 133000 rows...
Processed 134000 rows...
Processed 135000 rows...
Processed 136000 rows...
Processed 137000 rows...
Processed 138000 rows...
Processed 139000 rows...
Processed 140000 rows...
Processed 141000 rows...
Processed 142000 rows...
Processed 143000 rows...
Processed 144000 rows...
Processed 145000 rows...
Processed 146000 rows...
Processed 147000 rows...
Processed 148000 rows...
Processed 149000 rows...
Processed 150000 rows...
Processed 151000 rows...
Processed 152000 rows...
Processed 153000 rows...
Processed 154000 rows...
Processed 155000 rows...
Processed 156000 rows...
Processed 157000 rows...
Processed 158000 rows...
Processed 159000 rows...
Processed 160000 rows...
Processed 161000 rows...
Processed 162000 rows...
Processed 163000 rows...
Processed 164000 rows...
Processed 165000 rows...
Processed 166000 rows...
Processed 167000 rows...
Processed 168000 rows...
Processed 169000 rows...
Processed 170000 rows...
Processed 171000 rows...
Processed 172000 rows...
Processed 173000 rows...
Processed 174000 rows...
Processed 175000 rows...
Processed 176000 rows...
Processed 177000 rows...
Processed 178000 rows...
Processed 179000 rows...
Processed 180000 rows...
Processed 181000 rows...
Processed 182000 rows...
Processed 183000 rows...
Processed 184000 rows...
Processed 185000 rows...
Processed 186000 rows...
Processed 187000 rows...
Processed 188000 rows...
Processed 189000 rows...
Processed 190000 rows...
Processed 191000 rows...
Processed 192000 rows...
Processed 193000 rows...
Processed 194000 rows...
Processed 195000 rows...
Processed 196000 rows...
Processed 197000 rows...
Processed 198000 rows...
Processed 199000 rows...
Processed 2e+05 rows...
Processed 201000 rows...
Processed 202000 rows...
Processed 203000 rows...
Processed 204000 rows...
Processed 205000 rows...
Processed 206000 rows...
Processed 207000 rows...
Processed 208000 rows...
Processed 209000 rows...
Processed 210000 rows...
Processed 211000 rows...
Processed 212000 rows...
Processed 213000 rows...
Processed 214000 rows...
Processed 215000 rows...
Processed 216000 rows...
Processed 217000 rows...
Processed 218000 rows...
Processed 219000 rows...
Processed 220000 rows...
Processed 221000 rows...
Processed 222000 rows...
Processed 223000 rows...
Processed 224000 rows...
Processed 225000 rows...
Processed 226000 rows...
Processed 227000 rows...
Processed 228000 rows...
Processed 229000 rows...
Processed 230000 rows...
Processed 231000 rows...
Processed 232000 rows...
Processed 233000 rows...
Processed 234000 rows...
Processed 235000 rows...
Processed 236000 rows...
Processed 237000 rows...
Processed 238000 rows...
Processed 239000 rows...
Processed 240000 rows...
Processed 241000 rows...
Processed 242000 rows...
Processed 243000 rows...
Processed 244000 rows...
Processed 245000 rows...
Processed 246000 rows...
Processed 247000 rows...
Processed 248000 rows...
Processed 249000 rows...
Processed 250000 rows...
Processed 251000 rows...
Processed 252000 rows...
Processed 253000 rows...
Processed 254000 rows...
Processed 255000 rows...
Processed 256000 rows...
Processed 257000 rows...
Processed 258000 rows...
Processed 259000 rows...
Processed 260000 rows...
Processed 261000 rows...
Processed 262000 rows...
Processed 263000 rows...
Processed 264000 rows...
Processed 265000 rows...
Processed 266000 rows...
Processed 267000 rows...
Processed 268000 rows...
Processed 269000 rows...
Processed 270000 rows...
Processed 271000 rows...
Processed 272000 rows...
Processed 273000 rows...
Processed 274000 rows...
Processed 275000 rows...
Processed 276000 rows...
Processed 277000 rows...
Processed 278000 rows...
Processed 279000 rows...
Processed 280000 rows...
Processed 281000 rows...
Processed 282000 rows...
Processed 283000 rows...
Processed 284000 rows...
Processed 285000 rows...
Processed 286000 rows...
Processed 287000 rows...
Processed 288000 rows...
Processed 289000 rows...
Processed 290000 rows...
Processed 291000 rows...
Processed 292000 rows...
Processed 293000 rows...
Processed 294000 rows...
Processed 295000 rows...
Processed 296000 rows...
Processed 297000 rows...
Processed 298000 rows...
Processed 299000 rows...
Processed 3e+05 rows...
Processed 301000 rows...
Processed 302000 rows...
Processed 303000 rows...
Processed 304000 rows...
Processed 305000 rows...
Processed 306000 rows...
Processed 307000 rows...
Processed 308000 rows...
Processed 309000 rows...
Processed 310000 rows...
Processed 311000 rows...
Processed 312000 rows...
Processed 313000 rows...
Processed 314000 rows...
Processed 315000 rows...
Processed 316000 rows...
Processed 317000 rows...
Processed 318000 rows...
Processed 319000 rows...
Processed 320000 rows...
Processed 321000 rows...
Processed 322000 rows...
Processed 323000 rows...
Processed 324000 rows...
Processed 325000 rows...
Processed 326000 rows...
Processed 327000 rows...
Processed 328000 rows...
Processed 329000 rows...
Processed 330000 rows...
Processed 331000 rows...
Processed 332000 rows...
Processed 333000 rows...
Processed 334000 rows...
Processed 335000 rows...
Processed 336000 rows...
Complete! Processed total of 336776 rows.## $nInserted
## [1] 336776
##
## $nMatched
## [1] 0
##
## $nRemoved
## [1] 0
##
## $nUpserted
## [1] 0
##
## $writeErrors
## list()Planes.
m <- mongo(collection = "planes", db = "flights", url = "mongodb://127.0.0.1:27017")
m$insert(df_planes)##
Processed 1000 rows...
Processed 2000 rows...
Processed 3000 rows...
Complete! Processed total of 3322 rows.## $nInserted
## [1] 3322
##
## $nMatched
## [1] 0
##
## $nRemoved
## [1] 0
##
## $nUpserted
## [1] 0
##
## $writeErrors
## list()Weather.
m <- mongo(collection = "weather", db = "flights", url = "mongodb://127.0.0.1:27017")
m$insert(df_weather)##
Processed 1000 rows...
Processed 2000 rows...
Processed 3000 rows...
Processed 4000 rows...
Processed 5000 rows...
Processed 6000 rows...
Processed 7000 rows...
Processed 8000 rows...
Complete! Processed total of 8719 rows.## $nInserted
## [1] 8719
##
## $nMatched
## [1] 0
##
## $nRemoved
## [1] 0
##
## $nUpserted
## [1] 0
##
## $writeErrors
## list()Check MongoDB vs. MySQL
Let’s do a couple quick checks to confirm the data was loaded successfully to Mongodb.
#Verify weather mongodb matches rows in weather df created from the RDBMS
m$count() == nrow(df_weather)## [1] TRUEConclusion
Our mission was successful. To truly understand the advantages and disadvantages of NoSQL, we probably need a better use case that just converting relatively well-formed data from an RDBMS.