Monday, May 9, 2011

First Interaction with NOSQL Database (MongoDB)

I heard a lot about NoSQL Databases. So, I thought to try out this database. I found it simple to use and easy to configure.

To Download MongoDB, user can use following link:
http://www.mongodb.org/downloads

Installation is easy. If you are planning to use pre-built binaries, then you can untar the binaries and can start using it.

To start. First Create a data directory as given below:
mkdir -p mongodb/data
Initialize the Mongodb as given below:
"MongoDB Installation Directory"/bin/mongod --dbpath mongodb/data --logpath mongodb.log

Default port on which MonogoDB runs is 27017

To Connect with MongoDB instance, user can use following command:
./mongo 127.0.0.1:27017/foo 
Or you can use following options:
usage: ./mongo [options] [db address] [file names (ending in .js)]
db address can be:
  foo                   foo database on local machine
  192.169.0.5/foo       foo database on 192.168.0.5 machine
  192.169.0.5:9999/foo  foo database on 192.168.0.5 machine on port 9999
options:
  --shell               run the shell after executing files
  --nodb                don't connect to mongod on startup - no 'db address' 
                        arg expected
  --quiet               be less chatty
  --port arg            port to connect to
  --host arg            server to connect to
  --eval arg            evaluate javascript
  -u [ --username ] arg username for authentication
  -p [ --password ] arg password for authentication
  -h [ --help ]         show this usage information
  --version             show version information
  --verbose             increase verbosity
  --ipv6                enable IPv6 support (disabled by default)

Since, I have already created the mongodb and my mongodb instance is ready to use, so lets see how you can work with MongoDB.

As we know, its a NoSQL Database, so SQL syntax would not be working here, therefore I would be showing the SQL Statement and Respective mongodb command:

Snapshot of connecting to mongoDB is given below:
edbs-MacBook-Pro:bin vibhor$ ./mongo --shell mydb
MongoDB shell version: 1.8.1
connecting to: mydb
type "help" for help
>

Lets see the interaction with mongoDB:
SQL: CREATE TABLE test (a Number, b text)
MogoDB Syntax:
> db.createCollection("user", {capped:true, size:100000});
{ "ok" : 1 }

SQL: INSERT INTO test values(123,'Check1');
MongoDB Syntax:
db.test.insert({a:1,b:'check1'})
SQL: SELECT a,b FROM test;
MongoDB Syntax:
> db.test.find({},{a:1,b:"check1"});
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "check1" }

SQL: SELECT * From Test;
MongoDB Syntax:
> db.test.find();
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "check1" }

SQL: SELECT * FROM test WHERE a=1;
MongoDB Syntax:
> db.test.find({a:1},{});
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "check1" }

SQL: SELECT a,b FROM users WHERE a=2;
MongoDB Syntax:
> db.test.find({a:2},{a:1,b:1});
{ "_id" : ObjectId("4dc86432c0f16496e5e0f8f2"), "a" : 2, "b" : "check2" }
SQL: SELECT * FROM test order by a asc;
MongoDB Syntax:
> db.test.find().sort({a:1});
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc86432c0f16496e5e0f8f2"), "a" : 2, "b" : "check2" }
> 
SQL: SELECT * FROM test order by a dsc;
MongoDB Syntax:
> db.test.find().sort({a:-1});
{ "_id" : ObjectId("4dc86432c0f16496e5e0f8f2"), "a" : 2, "b" : "check2" }
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "check1" }

SQL: SELECT distinct b FROM test;
MongoDB Syntax:
> db.test.distinct("b");
[ "check1", "check2" ]
SQL: UPDATE test SET b='Check2' WHERE a=1;
MongoDB Syntax:
> db.test.update({a:1}, {$set:{b:"Check2"}}, false, true);
> db.test.find({},{a:1,b:1});
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "Check2" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "Check2" }
{ "_id" : ObjectId("4dc86432c0f16496e5e0f8f2"), "a" : 2, "b" : "check2" }
SQL: DELETE FROM test WHERE a=1;
MongoDB Syntax:
> db.test.remove({a:1});
> db.test.find({},{a:1,b:1});
{ "_id" : ObjectId("4dc86432c0f16496e5e0f8f2"), "a" : 2, "b" : "check2" }
SQL: SELECT COUNT(a) from text
MongoDB Syntax:
> db.test.find({a: {'$exists': true}}).count();
1
> 

Following is a chart link which contains SQL Mapping with MongoDB Commands:
http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart

2 comments:

  1. Hello,

    Hadoop you tried? integration with PostgreSQL

    http://hadoopdb.sourceforge.net/guide/

    ReplyDelete
  2. Not yet...However, I would like to try this.

    ReplyDelete