SQL to NOSQL Migration

Creating a connection to sql server to connect to desired databse.

loaded ODBC driver for 64 bit ADDED the driver on DNS calling the obcConnect method to connect.

con <- odbcConnect("abc",uid = "faad",pwd = "pass")

#sql query to get all the Departments from Department table and stored in DataFrame.
Departments <- sqlQuery(con,"SELECT * FROM HumanResources.Department")
myDepartments <- data.frame(Departments)

#sql query to get all the employees from Employee table and stored in DataFrame.
Employees <- sqlQuery(con,"SELECT * FROM HumanResources.Employee")
nrow(Employees)
## [1] 290
#close the servre connection.
odbcClose(con)

Creating mongo Collection to store data from dataframe.

cereted two collections for each corresponding DataFram

#creating collections
Department_Collection <- mongo(collection = "myDepartments", db="Department")
Employee_Collection <- mongo(collection = "Employees", db="Employees")

#Inserting data into collections
Department_Collection$insert(Departments)
## List of 5
##  $ nInserted  : num 16
##  $ nMatched   : num 0
##  $ nRemoved   : num 0
##  $ nUpserted  : num 0
##  $ writeErrors: list()
Employee_Collection$insert(Employees)
## List of 5
##  $ nInserted  : num 290
##  $ nMatched   : num 0
##  $ nRemoved   : num 0
##  $ nUpserted  : num 0
##  $ writeErrors: list()

Checking the Mongo Collections if loaded.

#calling count function to see row count for each collection.
Department_Collection$count()
## [1] 192
Employee_Collection$count()
## [1] 3480
#pulling single record from each collection
Department_Collection$iterate()$one()
## $DepartmentID
## [1] 1
## 
## $Name
## [1] "Engineering"
## 
## $GroupName
## [1] "Research and Development"
## 
## $ModifiedDate
## [1] "2002-06-01 EDT"
Employee_Collection$iterate()$one()
## $BusinessEntityID
## [1] 1
## 
## $NationalIDNumber
## [1] 295847284
## 
## $LoginID
## [1] "adventure-works\\ken0"
## 
## $OrganizationNode
## raw(0)
## attr(,"type")
## [1] 05
## 
## $OrganizationLevel
## [1] 0
## 
## $JobTitle
## [1] "Chief Executive Officer"
## 
## $BirthDate
## [1] "1963-03-02"
## 
## $MaritalStatus
## [1] "S"
## 
## $Gender
## [1] "M"
## 
## $HireDate
## [1] "2003-02-15"
## 
## $SalariedFlag
## [1] 1
## 
## $VacationHours
## [1] 99
## 
## $SickLeaveHours
## [1] 69
## 
## $CurrentFlag
## [1] 1
## 
## $rowguid
## [1] "F01251E5-96A3-448D-981E-0F99D789110D"
## 
## $ModifiedDate
## [1] "2008-07-31 EDT"

Comparing both SQL SERVER DATA with Mongo Collections

kable(head(myDepartments))
DepartmentID Name GroupName ModifiedDate
1 Engineering Research and Development 2002-06-01
2 Tool Design Research and Development 2002-06-01
3 Sales Sales and Marketing 2002-06-01
4 Marketing Sales and Marketing 2002-06-01
5 Purchasing Inventory Management 2002-06-01
6 Research and Development Research and Development 2002-06-01 
kable(head(Department_Collection$find()))
DepartmentID Name GroupName ModifiedDate
1 Engineering Research and Development 2002-06-01
2 Tool Design Research and Development 2002-06-01
3 Sales Sales and Marketing 2002-06-01
4 Marketing Sales and Marketing 2002-06-01
5 Purchasing Inventory Management 2002-06-01
6 Research and Development Research and Development 2002-06-01 
kable(head(Employees))
BusinessEntityID NationalIDNumber LoginID OrganizationNode OrganizationLevel JobTitle BirthDate MaritalStatus Gender HireDate SalariedFlag VacationHours SickLeaveHours CurrentFlag rowguid ModifiedDate
1 295847284 adventure-works0 0 Chief Executive Officer 1963-03-02 S M 2003-02-15 1 99 69 1 F01251E5-96A3-448D-981E-0F99D789110D 1217476800
2 245797967 adventure-works0 58 1 Vice President of Engineering 1965-09-01 S F 2002-03-03 1 1 20 1 45E8F437-670D-4409-93CB-F9424A40D6EE 1217476800
3 509647174 adventure-works0 5a, c0 2 Engineering Manager 1968-12-13 M M 2001-12-12 1 2 21 1 9BBBFB2C-EFBB-4217-9AB7-F97689328841 1217476800
4 112457891 adventure-works0 5a, d6 3 Senior Tool Designer 1969-01-23 S M 2002-01-05 0 48 80 1 59747955-87B8-443F-8ED4-F8AD3AFDF3A9 1217476800
5 695256908 adventure-works0 5a, da 3 Design Engineer 1946-10-29 M F 2002-02-06 1 5 22 1 EC84AE09-F9B8-4A15-B4A9-6CCBAB919B08 1217476800
6 998320692 adventure-works0 5a, de 3 Design Engineer 1953-04-11 M M 2002-02-24 1 6 23 1 E39056F1-9CD5-478D-8945-14ACA7FBDCDD 1217476800 
kable(head(Employee_Collection$find()))
BusinessEntityID NationalIDNumber LoginID OrganizationNode OrganizationLevel JobTitle BirthDate MaritalStatus Gender HireDate SalariedFlag VacationHours SickLeaveHours CurrentFlag rowguid ModifiedDate
1 295847284 adventure-works0 0 Chief Executive Officer 1963-03-02 S M 2003-02-15 1 99 69 1 F01251E5-96A3-448D-981E-0F99D789110D 1217476800
2 245797967 adventure-works0 58 1 Vice President of Engineering 1965-09-01 S F 2002-03-03 1 1 20 1 45E8F437-670D-4409-93CB-F9424A40D6EE 1217476800
3 509647174 adventure-works0 5a, c0 2 Engineering Manager 1968-12-13 M M 2001-12-12 1 2 21 1 9BBBFB2C-EFBB-4217-9AB7-F97689328841 1217476800
4 112457891 adventure-works0 5a, d6 3 Senior Tool Designer 1969-01-23 S M 2002-01-05 0 48 80 1 59747955-87B8-443F-8ED4-F8AD3AFDF3A9 1217476800
5 695256908 adventure-works0 5a, da 3 Design Engineer 1946-10-29 M F 2002-02-06 1 5 22 1 EC84AE09-F9B8-4A15-B4A9-6CCBAB919B08 1217476800
6 998320692 adventure-works0 5a, de 3 Design Engineer 1953-04-11 M M 2002-02-24 1 6 23 1 E39056F1-9CD5-478D-8945-14ACA7FBDCDD 1217476800
#Advantage and Disa dvantages 
#NOSQL much more flexible as it does not rely on the refrential integratiy which means that you can easily create objects without the worry of creating lookup tables or in this collectin unlike SQL. This however will lead to unwanted data and duplications as there is no check to match the values. Sorting is very      helpful in sql as it creates indexed based on the sorting criteria which imporoves the query runtime and save resources. Sorting will not do much in NoSQL since we have collections as not structured data as we have in sql.