CODEWITHSUNDEEP
mongodbpymongoisodate
texens
texens

Reputation: 3867

Convert a ISODate string to mongoDB native ISODate data type

My application generates logs in JSON format. The logs looks something like this :

{"LogLevel":"error","Datetime":"2013-06-21T11:20:17Z","Module":"DB","Method":"ExecuteSelect","Request":"WS_VALIDATE","Error":"Procedure or function 'WS_VALIDATE' expects parameter '@LOGIN_ID', which was not supplied."}

Currently, I'm pushing in the aforementioned log line as it is into mongoDB. But mongoDB stores the Datetime as a string (which is expected). Now that I want to run some data crunching job on these logs, I'd prefer to store the Datetime as mongoDB's native ISODate data type.

There are 3 ways I can think of for doing this :

i) parse every JSON log line and convert the string to ISODate type in the application code and then insert it. Cons : I'll have to parse each and every line before pushing it to mongoDB, which is going to be a little expensive

ii) After every insert run a query to convert the last inserted document's string date time to ISODate using 

element.Datetime = ISODate(element.Datetime);

Cons : Again expensive, as I'm gonna be running one extra query per insert iii) Modify my logs at generation point so that I don't have to do any parsing at application code level, or run an update query after every insert

Also, just curious, is there a way I can configure mongoDB to auto convert datetime strings to its native isodate format ?

TIA

EDIT: I'm using pymongo for inserting the json logs

My file looks something like this :

{"LogLevel":"error","Datetime":"2013-06-21T11:20:17Z","Module":"DB","Method":"ExecuteSelect","Request":"WS_VALIDATE","Error":"Procedure or function 'WS_VALIDATE' expects parameter '@LOGIN_ID', which was not supplied."}

There are hundreds of lines like the one mentioned above. And this is how I'm inserting them into mongodb:

for line in logfile:
    collection.insert(json.loads(line))

The following will fix my problem:

for line in logfile:
    data = json.loads(line)
    data["Datetime"] = datetime.strptime(data["Datetime"], "%Y-%M-%DTHH:mmZ")
    collection.insert(data)

What I want to do is get rid of the extra manipulation of datetime I'm having to do above. Hope this clarifies the problem.

Upvotes: 2

Views: 13214

Answers (3)

rmartinsjr
rmartinsjr

Reputation: 579

Looks like you already have the answer... I would stick with:

for line in logfile:
    data = json.loads(line)
    data["Datetime"] = datetime.strptime(data["Datetime"], "%Y-%M-%DTHH:mmZ")
    collection.insert(data)

I had a similar problem, but I didn't known beforehand where I should replace it by a datetime object. So I changed my json information to something like:

{"LogLevel":"error","Datetime":{"__timestamp__": "2013-06-21T11:20:17Z"},"Module":"DB","Method":"ExecuteSelect","Request":"WS_VALIDATE","Error":"Procedure or function 'WS_VALIDATE' expects parameter '@LOGIN_ID', which was not supplied."}

and parsed json with:

json.loads(data, object_hook=logHook)

with 'logHook' defined as:

def logHook(d):
    if '__timestamp__' in d:
        return datetime.strptime(d['__timestamp__'], "%Y-%M-%DTHH:mmZ")
    return d

This logHook function could also be extended to replace many other 'variables' with elif, elif, ...

Hope this helps!

Upvotes: 2

Matt Kneiser
Matt Kneiser

Reputation: 2156

Also, just curious, is there a way I can configure mongoDB to auto convert datetime strings to its native isodate format ?

You probably want to create a Python datetime object for the timestamp, and insert that using PyMongo. This is stored under the hood as the native date object in MongoDB.

So, for example in Python:

from datetime import datetime
object_with_timestamp = { "timestamp": datetime.now() }
your_collection.insert(object_with_timestamp)

When this object gets queried from the Mongo shell, an ISODate object is present:

"timestamp" : ISODate("2013-06-24T09:29:58.615Z")

Upvotes: 1

chanokim
chanokim

Reputation: 313

It depends on with what language/driver/utility you're pushing the log. I am assuming you're using mongoimport.

mongoimport doesn't support ISODate(). Refer to this issue https://jira.mongodb.org/browse/SERVER-5543 ISODate() is not a JSON format, hence not supported in mongoimport.

i) approach seems more efficient. ii) does two actions on mongo: insert & update. I had same issue while importing some log data into mongo. I ended up converting ISO 8601 format date to epoch format. 

{"LogLevel":"error","Datetime":{"$date" : 1371813617000},"Module":"DB","Method":"ExecuteSelect","Request":"WS_VALIDATE","Error":"Procedure or function 'WS_VALIDATE' expects parameter '@LOGIN_ID', which was not supplied."}

Above JSON should work. Note that it is 64-bit not 32-bit epoch.

Upvotes: 0

Related Questions