Task : Take information from a relational database and migrate it to a NoSQL database

SQL v/s NoSQL database mapping

SQL v/s NoSQL data mapping

Write data to relational database

Step 1: Connect to database

suppressWarnings(library(RMySQL))
mydb = dbConnect(MySQL(), user='root', password=passsword, dbname='tb', host='localhost')

Step 2: Create table query

CREATE TABLE 'posts' 
(
'id' int(11) NOT NULL AUTO_INCREMENT,
'post_text' varchar(500) NOT NULL,
'user_name' varchar(20) NOT NULL,
'post_privacy' varchar(10) NOT NULL,
'post_likes_count' int(11) NOT NULL,
 PRIMARY KEY ('id')
)
If we want do this in MongoDB directly, we can use below query 
db.createCollection("posts")

Step 3: Insert data using query

INSERT INTO 'posts' ('id' ,'post_text' ,'user_name' ,'post_privacy' ,'post_likes_count')VALUES (NULL ,  'This is a sample post',  'mark',  'public',  '0');
If we want to do insert to MongoDB directly, we can use below query 
db.posts.insert({user_name:"mark", post_text:"This is a sample post", post_privacy:"public", post_likes_count:0})

Read data from relational database

rs = dbSendQuery(mydb, "select * from posts")
data = fetch(rs, n=-1)
data
##   id             post_text user_name post_privacy post_likes_count
## 1  1 This is a sample post      mark       public                0
If we want to select/read from MongoDB directly, we can use below query 
db.posts.find()

Create MongoDB Collection

( i.e. similar to table in relational database )

suppressWarnings(library(RMongo))
suppressWarnings(library(rmongodb))
collection<-"MyCollection"
mongo = mongo.create(host = "localhost")
mongo <- mongo.create()
mongo.get.database.collections(mongo , collection)
## character(0)

Write data to MongoDB

Convert to JSON and then to bson

suppressWarnings(library(jsonlite))
jsonObj<-toJSON(unname(split(data, 1:nrow(data))))
jsonObj
## [[{"id":1,"post_text":"This is a sample post","user_name":"mark","post_privacy":"public","post_likes_count":0}]]
bsonObj <- mongo.bson.from.JSON(jsonObj)

Write to MongoDB read.

#prepare new record to insert

data2<-mongo.bson.from.JSON("{ \"post_text\":\"This is a sample post2\" , \"user_name2\": \"mark2\", \"post_privacy\": \"public\", \"post_likes_count\": 0 }")



data2
##  post_text : 2    This is a sample post2
##  user_name2 : 2   mark2
##  post_privacy : 2     public
##  post_likes_count : 16    0
if(mongo.is.connected(mongo) == TRUE) {
  icoll <- paste(collection, "posts", sep=".")
  mongo.insert.batch(mongo, icoll, list(bsonObj,data2) )

  dbs <- mongo.get.database.collections(mongo, collection)
  pop<-mongo.find.all(mongo, icoll)
  unlist(pop)
  
  
}
##                        _id                       1.id 
## "57186ab2b9e9e6cbbf4254b1"                        "1" 
##                1.post_text                1.user_name 
##    "This is a sample post"                     "mark" 
##             1.post_privacy         1.post_likes_count 
##                   "public"                        "0" 
##                        _id                  post_text 
## "57186ab2b9e9e6cbbf4254b2"   "This is a sample post2" 
##                 user_name2               post_privacy 
##                    "mark2"                   "public" 
##           post_likes_count 
##                        "0"

Close the connection

mongo.destroy(mongo)
## NULL

SQL V/s NoSQL

Advantages of relational databases

  • Good choice for applications that involve the management of several transactions.
  • The structure of a relational database allows us to link information from different tables using foreign keys.
  • Maintains ACID properties (the set of properties that guarantee database transactions are processed reliably) important for relibility.
  • So many people knows SQL.

Advantages of non-relational databases

  • Lightweight data interchange format.
  • if you find yourself having to de-normalize your database schema, non-relational databases like Mongo may be the best way to go.
  • Database is not at risk for SQL injection attacks.
  • Sharding distributes the data across partitions to overcome hardware limitations.

Disadvantages of non-relational databases

  • Since there are no joins like there would be in relational databases, we need to perform multiple queries and join the data manually within our code – and that can get very ugly, very fast.
  • Mongo doesn’t automatically treat operations as transactions the way a relational database does, we must manually choose to create a transaction and then manually verify it, manually commit it or roll it back. To put it simply, some operations will succeed while others fail.

Features comparison

Conclusion

We did see how to get data from relational database and insert same object to MongoDB. But in real life it is not that easy. It is easy to use MongoDB directly but to migrate from relational database to MongoDB requires proper planning and testing.Below are few links related to subject.