Thursday, July 21, 2016

Fix mongoexport returning scientific (exponential) notation issue using customized python program

[mongod@localhost ~]$ mongo localhost/test --eval "db.longIntTest.find().pretty()"
MongoDB shell version: 3.2.6
connecting to: localhost/test
{
        "_id" : ObjectId("57909e59888012cba24b0481"),
        "c1" : 10000,
        "c2" : 199999999,
        "c3" : 223423423,
        "c4" : 23423423
}
{
        "_id" : ObjectId("57909e5e95474190b05b0e36"),
        "c1" : NumberLong(10000),
        "c2" : NumberLong(199999999),
        "c3" : NumberLong(223423423),
        "c4" : NumberLong(23423423)
}
{
        "_id" : ObjectId("57909e797c6fa819adf3ce37"),
        "c1" : 10000,
        "c2" : 199999999,
        "c3" : 223423423,
        "c4" : 23423423
}


[mongod@localhost ~]$ mongoexport -d test -c longIntTest --type=csv -f c1,c2,c3,c4 -o longIntTest.csv
2016-07-21T18:06:37.017+0800    connected to: localhost
2016-07-21T18:06:37.019+0800    exported 3 records

[mongod@localhost ~]$ cat longIntTest.csv
c1,c2,c3,c4
10000,199999999,223423423,23423423
10000,199999999,223423423,23423423
10000,1.99999999e+08,2.23423423e+08,2.3423423e+07

[mongod@localhost ~]$ cat mongoexport_python.py
import pymongo
import sys
import csv

# establish a connection to the database
connection = pymongo.MongoClient("mongodb://localhost")

# get a handle to the school database
db=connection.test
col=db.longIntTest

try:
  cursor = col.find({},{'_id':0})
except Exception as e:
  print "Unexpected error:", type(e), e

with open('longIntTest_python.csv', 'w') as csvfile:
  fieldnames = ['c1', 'c2','c3','c4']
  writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
  writer.writeheader()
  for doc in cursor:
    print doc
    writer.writerow(doc)

[mongod@localhost ~]$ python mongoexport_python.py
{u'c3': 223423423, u'c2': 199999999, u'c1': 10000, u'c4': 23423423}
{u'c3': 223423423L, u'c2': 199999999L, u'c1': 10000L, u'c4': 23423423L}
{u'c3': 223423423.0, u'c2': 199999999.0, u'c1': 10000.0, u'c4': 23423423.0}

[mongod@localhost ~]$ cat longIntTest_python.csv
c1,c2,c3,c4
10000,199999999,223423423,23423423
10000,199999999,223423423,23423423
10000.0,199999999.0,223423423.0,23423423.0

Tuesday, July 12, 2016

MongoDB TTL indexes for time series data

[mongod@localhost ~]$ mongo
MongoDB shell version: 3.2.6
connecting to: test

> db.log_events.createIndex( { "createdAt": 1 }, { expireAfterSeconds: 3600 } )
{
        "createdCollectionAutomatically" : true,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.log_events.insert( {
...    "createdAt": new Date(),
...    "logEvent": 2,
...    "logMessage": "Success!"
... } )
WriteResult({ "nInserted" : 1 })


> db.log_events.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "test.log_events"
        },
        {
                "v" : 1,
                "key" : {
                        "createdAt" : 1
                },
                "name" : "createdAt_1",
                "ns" : "test.log_events",
                "expireAfterSeconds" : 3600
        }
]

> db.log_events.find()
> db.runCommand( {"collMod" :'log_events',"index" :{keyPattern:{"createdAt" : 1}, expireAfterSeconds: 60}})
{
        "expireAfterSeconds_old" : NumberLong(3),
        "expireAfterSeconds_new" : 60,
        "ok" : 1
}
> db.log_events.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "test.log_events"
        },
        {
                "v" : 1,
                "key" : {
                        "createdAt" : 1
                },
                "name" : "createdAt_1",
                "ns" : "test.log_events",
                "expireAfterSeconds" : NumberLong(60)
        }
]
> db.log_events.insert( {    "createdAt": new Date(),    "logEvent": 2,    "logMessage": "Success!" } )
WriteResult({ "nInserted" : 1 })
> db.log_events.find()
{ "_id" : ObjectId("5784c14f6a85d96ed4d42113"), "createdAt" : ISODate("2016-07-12T10:07:11.349Z"), "logEvent" : 2, "logMessage" : "Success!" }
> db.log_events.find()
{ "_id" : ObjectId("5784c14f6a85d96ed4d42113"), "createdAt" : ISODate("2016-07-12T10:07:11.349Z"), "logEvent" : 2, "logMessage" : "Success!" }
> db.log_events.find()
{ "_id" : ObjectId("5784c14f6a85d96ed4d42113"), "createdAt" : ISODate("2016-07-12T10:07:11.349Z"), "logEvent" : 2, "logMessage" : "Success!" }
> db.log_events.find()
{ "_id" : ObjectId("5784c14f6a85d96ed4d42113"), "createdAt" : ISODate("2016-07-12T10:07:11.349Z"), "logEvent" : 2, "logMessage" : "Success!" }
> db.log_events.find()
{ "_id" : ObjectId("5784c14f6a85d96ed4d42113"), "createdAt" : ISODate("2016-07-12T10:07:11.349Z"), "logEvent" : 2, "logMessage" : "Success!" }

> Date()
Tue Jul 12 2016 18:11:32 GMT+0800 (SGT)
// The ISODate is in UTC.
> new Date()
ISODate("2016-07-12T10:08:47.861Z")
> db.log_events.find()
>