Shantanu's Blog

## read data from excel file to pandas dataframe
import pandas as pd
df=pd.read_excel("5ch.xls", parse_dates='True', header=3)

## import pandas dataframe to mysql
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='dba', passwd='dbapass', db='test')
df.to_sql(con=conn, name='temp_aaa', if_exists='replace', flavor='mysql')

# insert a record using python pymysql module
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='dba', passwd='dbapass', db='test')
cur = conn.cursor()
cur.execute("INSERT INTO todel VALUES ('%s', '%s')" % ('aa', 'bb'))
conn.commit()



# select a reord from mysql table using python
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='dba', passwd='dbapass', db='test')
cur = conn.cursor(pymysql.cursors.DictCursor)
cur.execute("select * from todel  where id = 0 limit 10" )
for rw in cur.fetchall():
    print rw

Here is a function that will check if the given object is a list or not. If it is not a list then you will get an Assertion Error.

def checktype(mylist):
    assert isinstance(mylist, list), "only list allowed"
    for i in mylist:
        print i

checktype(['a', 'b'])

checktype(('a', 'b'))
AssertionError: need a list only

An OrderedDict is a dictionary subclass that remembers the order in which its contents are added. counter is simple function that counts the items from a given list. Both are part of collections module.

import collections
c = collections.Counter('abcdaab')
print c

Counter({'a': 3, 'b': 2, 'c': 1, 'd': 1})

We can use these 2 functions to return the count in the order as it was entered.

from __future__ import print_function
from collections import Counter, OrderedDict

class OrderedCounter(Counter, OrderedDict):
    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, OrderedDict(self))
    def __reduce__(self):
        return self.__class__,(OrderedDict(self),)

ac = OrderedCounter('abracdbdraa')
print(ac)

OrderedCounter(OrderedDict([('a', 4), ('b', 2), ('r', 2), ('c', 1), ('d', 2)]))

# 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" }})

It is very important to use the same character set across all tables and acorss all databases.

The default character set used per database are listed using this query...

mysql> select * from information_schema.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | emailplatform      | latin1                     | latin1_swedish_ci      | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | latin1                     | latin1_swedish_ci      | NULL     |
| def          | test1              | latin1                     | latin1_swedish_ci      | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
6 rows in set (0.01 sec)

If some of the tables from a database does not match with with other tables, we need to alter those tables. for e.g. if 1 or 2 tables from test database are using utf8 encoding, then the best choice is to drop and recreate those tables as latin1
There are 2 points to note here...
1) The table in question should not be part of foregin key relations.
2) The table should not actually contain unicode characters. Because once we convert it to latin1, there will be no way to store unicode.

The easiest way to check if the tables from a given database are using different character set is to use mysqldump command as shown here...
root@ip-10-86-106-75:/home/ubuntu# mysqldump test --no-data | grep ENGINE
) ENGINE=TokuDB DEFAULT CHARSET=latin1;
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
) ENGINE=TokuDB DEFAULT CHARSET=latin1;

This means there are a few tables with utf8 character while others are latin1.
You will have issues while joining a latin1 table with utf8 table. The query will not use indexes if the columns are of different character sets.
_____

The following query run on "information_schema" database shows that there is 1 table in test database that has utf8 collation. All other tables in test database are latin1. Therefore I need to change that single utf8 table to latin1

# create a new table in test database
create table test.tables select * from information_schema.tables;

# check for table_collations

mysql> select table_schema, table_collation, count(*) as cnt from test.tables group by table_schema, table_collation;
+--------------------+-------------------+-----+
| table_schema       | table_collation   | cnt |
+--------------------+-------------------+-----+
| emailplatform      | latin1_swedish_ci |  64 |
| information_schema | utf8_general_ci   |  45 |
| mysql              | latin1_swedish_ci |   1 |
| mysql              | utf8_bin          |   8 |
| mysql              | utf8_general_ci   |  15 |
| performance_schema | utf8_general_ci   |  17 |
| test               | latin1_swedish_ci |  18 |
| test               | utf8_general_ci   |   1 |
| test1              | latin1_swedish_ci |   1 |
+--------------------+-------------------+-----+
9 rows in set (0.00 sec)

To find the name of the table I use this query:

mysql> select table_name from tables where table_schema = 'test' and table_collation like 'utf8%';
+------------+
| table_name |
+------------+
| cdr_master |
+------------+
1 row in set (0.01 sec)

And here is the count:

mysql> select count(*) from test.cdr_master;
+----------+
| count(*) |
+----------+
|   186166 |
+----------+
1 row in set (0.04 sec)

This is relatively small table so we can quickly change the character set.
But we need to check that there is no other table linked to this using foreign key relations.

mysql> select * from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA='TEST' AND TABLE_NAME = 'cdr_master' LIMIT 1\G
Empty set (0.00 sec)

mysql> select * from information_schema.KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA='TEST' AND REFERENCED_TABLE_NAME = 'cdr_master' LIMIT 1\G
Empty set (0.06 sec)

_____

To change the default character set we need to drop and recreate the table.

Take the backup of the table.

# mysqldump --tab=/tmp/ test cdr_master

Change the character set:

# sed -i.bak 's/CHARSET=utf8/CHARSET=latin1/' /tmp/cdr_master.sql

Recreate the new table after dropping the old one:

# mysql test < /tmp/cdr_master.sql

# restore data:

mysql> load data infile '/tmp/cdr_master.txt' into table test.cdr_master;
Query OK, 186166 rows affected (25.06 sec)
Records: 186166  Deleted: 0  Skipped: 0  Warnings: 0

Now log-out and log back in to find that all the tables in test database are having the same table_collation.

mysql> select table_collation, count(*) as cnt from information_schema.tables where table_schema = 'test' group by table_collation;
+-------------------+-----+
| table_collation   | cnt |
+-------------------+-----+
| latin1_swedish_ci |  19 |
+-------------------+-----+
1 row in set (0.00 sec)

_____

In order to check if the utf8 encoded table really has any unicode characters, you need to take the backup of the table.

mysqldump test tbl_name > todel.txt

And then run this python script. If the script processes all the lines without any problem then the data is compatible with latin1.

import codecs
f = codecs.open("/tmp/todel.txt", "r", "utf-8")
for line in f.readlines():
    todel=line.decode('latin1')

You have unicode data in your table if you get an unicode error like this...

UnicodeEncodeError: 'ascii' codec can't encode characters in position 31-35: ordinal not in range(128)

In such case, you can not continue with the task of table re-creation unless you decide to ignore the unicode characters being
_____

If you want to check which lines contain unicode characters, you need another type of dump (skip extended insert option will generate a line per record in the table)

# mysqldump test todel --skip-extended-insert > todel.txt

And the following python code will display all the records where unicode characters are used.

import codecs
f = codecs.open("/tmp/todel.txt", "r", "utf-8")
for line in f.readlines():
    try: 
        todel=line.decode('latin1')
    except:
        print line

_____

If recreating the entire table is not an option, then simply change the single column to latin1.

mysql> alter table test.cdr_master modify column call_uuid varchar(50) charset latin1;

This will make the query very fast if the column "call_uuid" is used in the join query. The other table's column "call_uuid" should be also latin1.
_____

if you use extended explain then you will see what mysql is trying to do internally.

mysql> explain extended select * from a inner join b on a.column = b.column
mysql> show warnings;

The warning will show that mysql is trying to convert the b.column from utf8 to latin1 in order to match with a.column. This internal conversion will not allow mysql to use indexes.

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()