Mani DBA - Learning Mongo DB: November 2016

Tuesday, November 22, 2016

Bulk insertOne

try {
   db.products.insertOne( { item: "card", qty: 15 } );
} catch (e) {
   print (e);
};

try {
   db.products.insertOne( { _id: 210, item: "box", qty: 20 } );
} catch (e) {
   print (e);
}

try {
   db.products.insertOne( { _id: 110, "item" : "packing peanuts", "qty" : 200 } );
} catch (e) {
   print (e);
}

{ "acknowledged" : true, "insertedId" : 110 }
>
\

try {
   db.characters.bulkWrite(
      [
         { insertOne :
            {
               "document" :
               {
                  "_id" : 140, "char" : "Mani", "class" : "barbarian", "lvl" : 4
               }
            }
         },
         { insertOne :
            {
               "document" :
               {
                  "_id" : 150, "char" : "Sana", "class" : "fighter", "lvl" : 3
               }
            }
         },
         { updateOne :
            {
               "filter" : { "_id" : 15 },
               "update" : { $set : { "status" : "Critical Injury" } }
            }
         },
         { deleteOne :
            { "filter" : { "char" : "Taeln"} }
         },
         { replaceOne :
            {
               "filter" : { "char" : "Dithras" },
               "replacement" : { "char" : "Tanys", "class" : "oracle", "lvl" : 4 }
            }
         }
      ]
   );
} catch (e) {
   print(e);
}

{

"acknowledged" : true,

"deletedCount" : 1,

"insertedCount" : 2,

"matchedCount" : 2,

"upsertedCount" : 0,

"insertedIds" : {

"0" : 140,

"1" : 150

"upsertedIds" : {

}

With unordered inserts, if an error occurs during an insert of one of the documents, MongoDB continues to insert the remaining documents in the array.

db.products.insert(
   [
     { _id: 200, item: "lamp", qty: 50, type: "desk" },
     { _id: 200, item: "lamp", qty: 20, type: "floor" },
     { _id: 220, item: "bulk", qty: 100 }
   ],
   { ordered: false }
)

Wednesday, November 16, 2016

Features of MongoDB

Indexing of any field and any number of fields is supported
Ad hoc queries support - Search by field, range, regular expression etc
Replication - Master/Slave (Slave can only read-only)
Duplication of Data across multiple servers for data redundancy
Load balancing
Scalability- Add new machine into running db
Capped collections - Behaves like a circular queue
Special File Storage - GridFS
Aggregation - MapReduce used - Big computation split into small units
Java queries
Special support for Spacial data (location) - Longitude/Latitude info

insertOne and insertMany methods

db.users.insertOne(
{
name:"Mani",
age:37,
status:"P"
}
)

db.users.insertMany(
[
{name:"Raja",age:34,status:"D"},
{name:"Kaja",age:24,status:"A"},
{name:"Roja",age:44,status:"F"}
]
)

Different between insert and InsertOne/Many Method is
Insert One/Many returns _id in the output but Insert doesn't do so, however it's internally updated _id value for normal inserts also.

_id field - Unique identifier of any record inserted into collection

Data accepted by _id field
====================
db.users.save({_id:1})
db.users.save({_id:3.14})
db.users.save({_id:"Hellow World"})
db.users.save({_id:ISODate()})
db.users.save({_id:{A:'x', b:1, c:3.14}})

Apart from an Array value, you can pass any value to an _id field

Tuesday, November 15, 2016

Aggregate Functions

(1) Sum

db.users.aggregate({
$group : {
_id : "$eyeColor",
total : {$sum : 1}
}
})

(2)
db.users.aggregate({
$group : {
_id : "$gender",
total : {$sum : 1}
}
})

(3) Avg

db.users.aggregate({
$group : {
_id : "$gender",
avgAge : {$avg : "$age"}
}
})

(4) max/min
db.users.aggregate({
$group : {
_id : "$gender",
Richest : {$max : "$balance"}
}
})

Indexing

Indexing
========

Sample query ->

db.users.find({"age":{$lt:23}})

Execution plan ->

db.users.find({"age":{$lt:23}}).explain("executionStats")

Create Index on Age ->

db.users.ensureIndex({"age":1})

List index ->

db.users.getIndexes()

Drop index ->

db.users.dropIndex({"age":1})

Various Types of SELECT commands

(1) Select from collection:
=======================
db.players.find({"position" : "Left Wing"})

db.players.find( {"position" : "Left Wing"} ).pretty()

(2) Using mulitiple conditions
==========================

db.players.find( {"position" : "Center","age" : 26} ).pretty()

(3) Using OR operator
=================

db.players.find(
{ $or:
[
{"position" : "Left Wing"},{"position" : "Right Wing"}
]
}
)

(4) Conditional Select Statements

Greater than, Less than, Grater than equal to, Less than equal to, Not equal
=======================================================

db.players.find(
{"age":{$gt:30}
}
)

db.players.find(
{"age":{$gte:28}
}
)

db.players.find(
{"age":{$lt:30}
}
)

db.players.find(
{"age":{$ne:29}
}
)

db.players.find(
{$or:
[
{"position":"Center"}, {"age":{lte:29}}
]
}
)

(5) Limitting Columns in the Display

db.players.find(
{$or:
[
{"position":"Center"}, {"age":{lte:29}}
]
},
{"name":1,"age":1}
)

db.players.find(
{$or:
[
{"position":"Center"}, {"age":{lte:29}}
]
},
{"name":1,"age":1,_id:0}
)

db.players.find(
{$or:
[
{"position":"Center"}, {"age":{lte:29}}
]
},
{"name":1,"age":1,_id:0}
).limit(1)

db.players.find(
{$or:
[
{"position":"Center"}, {"age":{lte:29}}
]
},
{"name":1,"age":1,_id:0}
).skip(1)

(6)By Sort order
db.users.find().sort(name:1})

Basic Commands

For RDBMS DBAs understanding ->

DB is nothing but a Schema, Tables are nothing but a Collection in mongoDB

Commands:

List all DBs -> show dbs
Show current db -> db
show collections
make any db as current db -> use <dbname>
show collections
db.players.find()
db.players.find().pretty()
db.players.findOne()

DDL/DML statements - CRUD Operation (Create, Read, Update & Delete)

(1) Insert record:

db.players.insert(
{
"position" : "Center",
"id" : 8474091,
"weight" : 190,
"height" : "6' 3\"",
"imageUrl" : "http://1.cdn.nhle.com/photos/mugs/8474091.jpg",
"birthplace" : "Huntington, NY, USA",
"age" : 26,
"name" : "Brandon Sutter",
"birthdate" : "February 14, 1989",
"number" : 16
}
)

Insert nested records
--------------------------
Nested Records

db.users.insert(
{
_id:"123",
name:"Hassan Bob",
addresses:[
{
street:"151, South Locust Dr",
city:"Hartford",
state:"CT",
postal:"49949"
},
{
street:"15, Garden Dr",
city:"Liverpool",
state:"NY",
postal:"13088"
}
]
}
)

===============

db.users.insert(
{
_id: "joe",
name: "Joe Bookreader"
}

{
patron_id: "joe",
street: "123 Fake Street",
city: "Faketon",
state: "MA",
zip: "12345"
}

{
patron_id: "joe",
street: "1 Some Other Street",
city: "Boston",
state: "MA",
zip: "12345"
}
)

(2) Update record:

db.players.update(
{
"_id" : ObjectId("582b7c5fa784f623f13106eb")},
{
"position" : "Left Wing",
"id" : 8465166,
"weight" : 180,
"height" : "6' 0\"",
"imageUrl" : "http://1.cdn.nhle.com/photos/mugs/8465166.jpg",
"birthplace" : "Seria, BRN",
"age" : 37,
"name" : "Raza mohamed",
"birthdate" : "April 26, 1977",
"number" : 27
}
)

(3) Remove record/delete command:

db.players.remove(
{"_id" : ObjectId("582b86fca784f623f13106ee")
}
)

(4) Drop Collection:
===============
db.players.drop()

DDL/DML statements

DBA Commands
=============
(1) Create DB

Use <dbname>

Use Hockey

Hit Enter - DB created

(2) Creating Collection called "Players"

db.players.insert(
{
"position":"Right Wing",
"id":8465166,
"weight":180,
"height":"6' 0\"",
"imageUrl":"http://1.cdn.nhle.com/photos/mugs/8465166.jpg",
"birthplace":"Seria, BRN",
"age":37,
"name":"Craig Adams",
"birthdate":"April 26, 1977",
"number":27
}
)

db.players.insert(
{
"position":"Right Wing",
"id":8475761,
"weight":195,
"height":"6' 0\"",
"imageUrl":"http://1.cdn.nhle.com/photos/mugs/8475761.jpg",
"birthplace":"Gardena, CA0, USA",
"age":23,
"name":"Beau Bennett",
"birthdate":"November 27, 1991",
"number":19
}
)

(3)
Inserting array of records
===================

db.players.insert(
[
{
"position":"Right Wing",
"id":8471260,
"weight":202,
"height":"6' 2\"",
"imageUrl":"http://1.cdn.nhle.com/photos/mugs/8471260.jpg",
"birthplace":"Meadow Lake, SK, CAN",
"age":29,
"name":"Blake Comeau",
"birthdate":"February 18, 1977",
"number":17
},
{
"position":"Right Wing",
"id":8471887,
"weight":189,
"height":"5' 11\"",
"imageUrl":"http://1.cdn.nhle.com/photos/mugs/8471887.jpg",
"birthplace":"Sollentuna, SWE",
"age":28,
"name":"Patric Hornqvist",
"birthdate":"January 01, 1987",
"number":72
},

{
"position":"Left Wing",
"id":8470543,
"weight":195,
"height":"6' 0\"",
"imageUrl":"http://1.cdn.nhle.com/photos/mugs/8470543.jpg",
"birthplace":"Regina, SK, CAN",
"age":29=35,
"name":"Chris Kunitz",
"birthdate":"September 28, 1979",
"number":14
},

{
"position":"Left Wing",
"id":8470543,
"weight":195,
"height":"6' 0\"",
"imageUrl":"http://1.cdn.nhle.com/photos/mugs/8470543.jpg",
"birthplace":"Regina, SK, CAN",
"age":29=35,
"name":"Chris Kunitz",
"birthdate":"September 28, 1979",
"number":14
},

{
"position":"Center",
"id":8474096,
"weight":201,
"height":"6' 1\"",
"imageUrl":"http://1.cdn.nhle.com/photos/mugs/8474096.jpg",
"birthplace":"Palmerston, ON, CAN",
"age":29,
"name":"Nick Spaling",
"birthdate":"September 19, 1988",
"number":13
},
{
"position":"Center",
"id":8474091,
"weight":190,
"height":"6' 3\"",
"imageUrl":"http://1.cdn.nhle.com/photos/mugs/8474091.jpg",
"birthplace":"Huntington, NY, USA",
"age":26,
"name":"Brandon Sutter",
"birthdate":"February 14, 1989",
"number":16
}
]
)

Creating banks DB
==============