Shantanu's Blog

Here are 4 easy steps to connect to documentDB cluster from any EC2 instance that is part of the same VPC.

1) Download key file:
cd /tmp/
wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

2) Create mongo container:
docker run --name mymongo  -p 27017:27017 -v /tmp/:/tmp/ -d mongo:3.4

3) Login to mongo container:
docker exec -it mymongo bash

4) connect to documentDB cluster:
mongo --ssl --host docdb-XXX.us-east-1.docdb.amazonaws.com:27017 --sslCAFile /tmp/rds-combined-ca-bundle.pem --username abcd  --password xyz

Note that /tmp/ folder of host is mounted on container. All the files in that folder are available to "mymongo" container.

Here are the commands to restore data to documentDB cluster.

# Restore from snapshot backup using AWS console and then query data using these commands:

cd /tmp/
wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

docker run -v /tmp/:/tmp/ -it mongo
mongo --ssl --host docdb-2019-08-23-06-00-23.cluster-xxx.us-east-1.docdb.amazonaws.com:27017 --sslCAFile /tmp/rds-combined-ca-bundle.pem --username root --password xxxx

use some_db

db.some_table.findOne()
_____

# Download mongo backup file from S3 and restore to documentDB

aws s3 cp s3://bckup-67-60/backup_sept_2018/mp_201809.tar.gz .

tar xvf mp_201809.tar.gz

docker run -v /tmp/:/tmp/ -it mongo mongorestore --ssl --host docdb-2019-08-23-06-00-23.cluster-xxx.us-east-1.docdb.amazonaws.com:27017 --sslCAFile /tmp/rds-combined-ca-bundle.pem --username root --password xxx -d mp_201809 /tmp/mp_201809/

1) Create a table in dynamoDB with the name "ssc" and use field "seat_no" as a string primary key.
2) Add the records for the subjects by creating fields "English", "Maths" and adding marks as string "54", "32". The primary key can be something like "B54MH".

3) Create python function in Lambda as shown below:

import boto3
import json

client = boto3.resource('dynamodb')
table = client.Table('ssc')

def lambda_handler(event, context):
    item = {'seat_no': (event['seatno'])}
    r=table.get_item(Key=item)  
    return json.dumps(r)

4) Create an API
Link the above lambda function name for e.g. "ssc" to the API gateway.
It is important to correctly specify the mapping for API get method execution request:

{"seatno": "$input.params('seatno')"}
_____

Once deployed, the URL will look something like this...

https://9xd7zbdqjg.execute-api.us-east-1.amazonaws.com/S1?seatno=B54MH

Note we are using secure server https to connect. This makes it possible to consume the API results directly into an application like slack.

It is also possible to include cutom authorizer using the steps outlined here...

http://docs.aws.amazon.com/apigateway/latest/developerguide/use-custom-authorizer.html

Here is the command that will initiate a toku container.

docker run -d -p 27017:27017 -v /tokudata:/data/db ankurcha/tokumx

(or use official image "mongo" instead of ankurcha/tokumx to install mongoDB without toku engine)

The following python code will connect to the toku mongo container and add a record.

from pymongo import MongoClient
client = MongoClient('172.17.0.1:27017')
db = client.myFirstMB
db.countries.insert_one({"name" : "USA"})
for i in db.countries.find():
    print i

Since we have linked the data directory to /tokudata folder of the host machine, the data can be easily backed up.

# add index
db.users.ensureIndex({"email":1})

# add composite index
db.users.ensureIndex({"email":1, "customer":1})

# add index on sub-document
db.users.ensureIndex({"email":1, "customer.address":1})

# add unique constraint
db.users.ensureIndex({"email":1, "customer.address":1}, {"unique":true})

# Remove duplicates
db.users.ensureIndex({"email":1, "customer.address":1}, {"unique":true, "dropDups":true})

# add sparse index that will return the record only if the field exists in the collection
db.users.ensureIndex({"email":1, "customer.address":1}, {"unique":true, "dropDups":true, "sparse":true})

# add full text index
db.users.ensureIndex({"email":1, "customer.address":1, "comment":text})

# add full text index on multiple fields
db.users.ensureIndex({"email":1, "customer.address":1, "comment":text, "customer.name":text})

# add weight to full text idnex
db.users.ensureIndex({"email":1, "customer.address":1, "comment":text, "customer.name":text}, {"weight":{"cusomter.name":2}})

# add full text index on all fields
db.users.ensureIndex({"email":1, "customer.address":1, "$**":text})


And here is how you can use the full text index in the query:

db.runCommand({"text":users, "search":"excellent post"})

By default it will search for "excellent" or "post". Use quotes to search using "and"

db.runCommand({"text":users, "search":"\"excellent post\""})

1) Here is how we can connect to mongodb and create a "db" object.

# connect to mongo test database
from pymongo import MongoClient
client = MongoClient()
db = client.test

2) Save dict data to mongodb test database, collection name: posts

y={"name": "amar", "age": 30}
db.posts.insert_one(y)

3) Get a sample record using findOne

db.posts.find_one()

4) copy the pandas dataframe to mongo

# assuming "df" is a dataframe that is alrady created

db.posts.insert_many(df.to_dict('records'))

If you get an error, you may need to correct the data using map function

df["region"] = df["region"].map(lambda x: str(x).split(':')[-1:])

This will remove the semicolon : from region column and select the last slice.

5) Import mongo data to pandas dataframe

# get all data from posts collection into a list of dicts
mylist=[]
for post in db.posts.find():
   mylist.append(post)

# convert the list to pandas dataframe
import pandas as pd
pd.DataFrame(mylist)

1) Sharding helps only if you have 3+ servers.
2) Choose shard key carefully, it can not be changed later.
3) All queries should use shard key.
4) Shard server can be started as replica sets. Take advantage of that.
5) Shard server should not be removed from the cluster. It does not offer the flexibility like replica set.

Here is a website that will convert any MySQL Queries to MongoDB Syntax:
http://www.querymongo.com/

db.users.find({}, {})	select * from users
db.users.find({}, {“username”:1, “email”:1}}	select username, email from  users
db.users.find({}, {“username”:1, “email”:1}}.limit(10)	select username, email from  users limit 10
.limit()          .skip()                   .sort()
db.users.count()	select count(*) from users
db.runCommand(“distinct”: “users”, “key”: “age”})	select distinct(age) from users
db.users.find({“age”:{“$gte”:18, “$lte”:30}})	select * from users where age >= 18 and age <= 30
$lt      $gt     $lte    $get             $ne    $elemMatch
db.users.find({“ticket_no”:{“$in”:[75, 390]}})	select * from  users where ticket_no in (“75”, “390”)
$in     $nin             $not             $all
db.users.find({“$or”:[{“ticket_no”:{“$in”:[75, 390]}}, {“winner”:true}]})	select * from  users where ticket_no in (“75”, “390”) or winner is not null
$or     $and           $nor             $elemMatch
db.users.find({“age”:{“$in”:[null], “$exists”:true}})	select * from  users where age is null
db.users.find({“username”:/happy?/i})	select * from  users where username like ‘happy%’
perl compatible regular expressions
db.users.find({“ticket_no”:75})	select * from  users where ticket_no like ‘%75%’
[75, 390, 120, 450]	“75”, “390”, “120”, “450”
db.users.find({“ticket_no.2”:120})
db.users.find({“ticket_no”:{“$size”:4}})
db.users.findOne({criteria as above}, {“$slice”:[23, 10]}})	select * from users where age >= 18 and age <= 30 limit 23, 10
db.runCommand($getLastError”:1})	show warnings;
db.articles.aggregate("$project": {"author":1}}, {$group":{"_id":"$author", "count":{"$sum":1}}}, {"$sort": {"count": -1}}, {"$limit":5}	Select  author, count(*) as cnt from  articles group by author order by cnt desc limit 5
Aggregation results are limited to maximum response time of 16 MB
db.employees.aggregate( {"$project": {"totalPay" : {"$subtract" : [{"$add": ["$salary", "$bonus"]}, "$taxes"] } } } )	Select  (salary + bouns – taxes) as totalPay from  employees
$add  $subtract      $multiply  $divide   $mod
db.employees.aggregate( { "$project" : { "tenure" : {"$subtract" : [{"$year" : new Date()}, {"$year": "$hireDate"}] } } } )	select   year(now()) – year(hireDate) as tenure from employees
$year $month $week $dayOfMonth $dayOfWeek $dayOfYear  $hour  $minute  $second
db.employees.aggreage( { "$project": { "email" : { "$concat" : [ {"$substr" : [ "$firstName", 0, 1]}, ".", "$lastName", "@company.com" ] } } } )	select  concat(left(firstName, 1), “.”, lastName, “@company.com”) as email from employees
$substr   $concat  $toLower   $toUpper
db.sales.aggregate( { "$group": { "_id": "$country", "totalRevenue": { "$sum" : "$revenue" } } } )	select country, sum(revenue) from sales group by country
db.blog.aggregate({"$project": {"comments": "$comments"}}, {"$unwind" : "$comments"}, {"$match": {"comments.author" : "Akbar" }})

When I log in to mongo shell, I see these warnings...

[root@az-215 ~]# mongo
MongoDB shell version: 3.0.3
connecting to: test
Server has startup warnings:
2015-05-20T13:04:19.010+0530 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2015-05-20T13:04:19.010+0530 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2015-05-20T13:04:19.010+0530 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2015-05-20T13:04:19.010+0530 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2015-05-20T13:04:19.010+0530 I CONTROL  [initandlisten] ** WARNING: soft rlimits too low. rlimits set to 1024 processes, 65535 files. Number of processes should be at least 32767.5 : 0.5 times number of files.

$$$$ I need to edit these 2 files to change from "always" to "never"

# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never

# cat /sys/kernel/mm/transparent_hugepage/defrag
[always] madvise never

And the soft limit of file descriptors is restricted to 1024 and that needs to be inreased for mongo to behave correctly.

cat /etc/security/limits.d/90-nproc.conf
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.

*          soft    nproc     1024
root       soft    nproc     unlimited

cp /etc/security/limits.d/90-nproc.conf /etc/security/limits.d/99-mongodb-nproc.conf

$$$ edit the new file 99-mongodb-nproc with higher soft limit.

Do not take these warnings lightly!

Here are the commands used for logging, statistics, administration and replica set. Each of these 4 sections have a few commands those will be useful for the system administrators.

##### logging

db.adminCommand({"setParameter":1, "logLevel":3})
# loglevels 0 to 5 defaults to 0
# like mysql general log

db.setProfilingLevel(1)
# 0-disable, 1 - log queries slower than 100 ms 2 - log everything
# like mysql slow query log

db.getProfilingLevel()
# current profile level

db.adminCommand({"diagLogging":2})
# captures data for diagnostic purposes
# 0-off, 1-writes, 2-reads, 3-both
# /data/db/dialog* | hexdump -c

##### stats

db.adminCommand({"serverStatus":1})["recordStats"]
# accesssNotInMemory if high, server overloaded

use local
db.startup_log.findOne()
# know how the server was started

user local
db.serverCmdLineOpts()
# how mongodb was started

db.foo.validate()
# deletedCount to know how many documents deleted

db.stats()
# database stats

db.collname.stats()
# collection stats

object.bsonsize({_id:ObjectId()})
db.eval("Object.bsonsize(db.foo.findOne())")
#size of a document


##### administration

db.runCommand({"compact":"collname"})
# compress database

db.runCommand({"dropIndexes":"foo", "index":"*"})
# drop all indexes

db.runCommand({"cloneCollection":"collname", "from": "shot:27017"})
# copy data from remote server

db.currentOp()
# show processlist equivalent

db.killOp(opid)
# kill process

db.adminCommand({"shutdown":1, "force":true})
# same as # use admin ## db.shutdownServer()
# same as kill -2 process_id


##### Replica set commands:

# start replica, add / remove server
rs.initiate()
rs.add({"_id":1, "host":"server:27017", "votes":0, "priority":0})
rs.remove("server:27017")
rs.stopSet()

# check status
db.printSlaveReplicationInfo()
rs.status()
rs.config()

# manage
rs.reconfig()
rs.reconfig(myconfig, {"force":true})
rs.stepDown()
rs.freeze(10000)
rs.setSlaveOk()

In some cases Mongodb does not allow you to count the results of certain queries because there are too many results returned and the server does not have enough memory to process all of them at once. In such cases you need to use map-reduce.

1) Write a map function that will create a numbered key for e.g. "count" for each record found.
2) Write a reducer function that will number all the "count" keys and give you the total.

In this example, I have 4 records in a collection called "abhishek".

> db.abhishek.find()
{ "_id" : ObjectId("55b8978ab14a658f6e173618"), "name" : "shantanu1" }
{ "_id" : ObjectId("55b8980bb14a658f6e173619"), "name" : "shantanu2" }
{ "_id" : ObjectId("55b898fbb14a658f6e17361b"), "name" : "shantanu3" }
{ "_id" : ObjectId("55b898feb14a658f6e17361c"), "name" : "shantanu4" }

I can easily see that there are 4 records in this collection. But I can get the same results using map-reduce as shown below:

map = function() {
for (var key in this) {
emit(key, {count: 1});
}};


reduce = function(key, emits) {
total = 0;
for (var i in emits) {
total += emits[i].count;
}
return {"count": total };
}

mr = db.runCommand({"mapreduce": "abhishek", "map": map, "reduce": reduce, "out": 'redcuedcalls1'})

> db[mr.result].find()
{ "_id" : "_id", "value" : { "count" : 4 } }
{ "_id" : "name", "value" : { "count" : 4 } }

You have to apply the same map-reduce logic to get the count of results returned from a query.

There is a function called "last_insert_id" in mysql that will return the auto incremented id of the record recently added. MongoDB by default returns the object ID of the last inserted record. Interesting :)

>>> import pymongo
>>> client=pymongo.MongoClient()
>>> db=client.mydb
>>> db.testData.insert({"x": 211, "phone":["27848226", "26788485"]})
ObjectId('537aec6da44d0b7348eb6a25')

Other useful commands:

# db.testData.find().limit(1).forEach(printjson)

# list(db.testData.find().limit(1))

# import pprint
# pprint.PrettyPrinter(some_text)