Data Migration from SQL to noSQL

The main differences between relational and non-relational databases are schema and flexibility. Relational DBs store store data in tables with primary and foreign keys which create links between them. Non-relational DBs usually store records as json formatted documents without a strict schema or relationships between the documents.

Extract Data from PostgreSQL

# Create a connection with PostgreSQL
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,
                 host = "localhost", 
                 port = 5432,
                 user = "postgres", 
                 password = 'rafal')


# Query the data from postgreSQL 
film_list <- dbGetQuery(con, "SELECT * from films")

# Close the DB connection
dbDisconnect(con)

# Check the results
film_list

##                       name   genre rating
## 1   Spiderman - Homecoming  action   4.86
## 2                  Dunkirk     war   4.14
## 3 Star Wars: The Last Jedi   scifi   4.43
## 4             Wonder Woman  action   4.79
## 5     Beauty and the Beast musical   3.50
## 6           Justice League  action   4.57

Create a connection, database and a collection in MongoDB

# Connect to a local MongoDB

mdb <- mongo(collection = 'movies',
            db = 'imbd', 
            url = "mongodb://localhost:27017")

# Push movies into MongoDB
mdb$insert(film_list)

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

# Check the results by quering all records from the collection
mdb$find()

##                        name   genre rating
## 1    Spiderman - Homecoming  action   4.86
## 2                   Dunkirk     war   4.14
## 3  Star Wars: The Last Jedi   scifi   4.43
## 4              Wonder Woman  action   4.79
## 5      Beauty and the Beast musical   3.50
## 6            Justice League  action   4.57
## 7    Spiderman - Homecoming  action   4.86
## 8                   Dunkirk     war   4.14
## 9  Star Wars: The Last Jedi   scifi   4.43
## 10             Wonder Woman  action   4.79
## 11     Beauty and the Beast musical   3.50
## 12           Justice League  action   4.57

Data Migration

Rafal Decowski

November 17, 2017

Data Migration from SQL to noSQL

Extract Data from PostgreSQL

Create a connection, database and a collection in MongoDB

ScreenShot of the MDB collection from Robo 3T