Show HN: SQL to Mongo Query Translator | Hacker News Reader

Show HN: SQL to Mongo Query Translator

querymongo.com

62 pointsrobertjmoore33 comments

I don't have any experience with MongoDB, but the example put me off using it completely. The MySQL query is concise and brief, the MongoDB equivalent is bloated. Only after several seconds I was able to deduce that the MongoDB query probably does something more than MySQL query. Can you please make the examples more comparable? Or did I misunderstand the MongonDB and it actually is so bloated by design? I believe it is not your goal to discourage people from MongoDB , if so, better not do it unintentionally.

This, exactly. Great tool, but having no experience with Mongo yet, I literally said aloud "So verbose...!" when I saw the translation.

>> Or did I misunderstand the MongonDB and it actually is so bloated by design?

Depending on the type of query and the way you designed your database schema MongoDB queries can be either more concise or (in your words) more bloated than SQL. You can't really say either one is more bloated than the other in terms of query syntax.

Personally, I generally prefer MongoDB queries over SQL, because it doesn't have a concept of joins, inner queries, temporary tables, etc, which usually translates to more but simpler queries. If the data model for your applications relies heavily on any of these features, maybe you shouldn't use MongoDB (or any other NoSQL database, for that matter).

MongoDB's query language is just not very good.

Used to make me want to tear my hair out... But I've moved to using linqpad and writing the queries in linq now, and it's not too bad.

http://stackoverflow.com/questions/7303333/are-adhoc-queries...

The default is a particularly perverse example. Most anything that involves aggregation or on-the-fly computation is better done in MySQL or similar. Typical Mongo usage is predicated on the assumption that you're going to denormalize your data into documents that are trivially queryable. Treating it like a SQL store is a mistake, and this query demonstrates exactly why.

The default query already filled in is translating to the use of a Group function, which is a very bad idea. While not deprecated per se, its use is discouraged.

Group does not function in Sharding mode at all, it also takes a lock on the JavaScript interpreter making it non-parallelizable.

Map/Reduce is somewhat better in that it is shardable, and with V8 likely in the next stable release, will have better parallelization prospects.

Ideally, you should be using the new Aggregation Framework to do this kind of work: http://docs.mongodb.org/manual/applications/aggregation/

(EDIT) To clarify - Aggregation is ideal because its implementation is 100% in C++, meaning there are no JavaScript interpreter locks necessary to run it, so it is parallelizable. Additionally, one of the biggest overhead costs to MapReduce and Group in MongoDB is the translation back and forth between BSON (the native format MongoDB uses for data, or rather the C++ representations thereof) and JavaScript types. Aggregation not utilizing JavaScript eliminates this overhead and manipulates the database' internal types directly.

Thanks, that's extremely helpful. Based on how this is built, it might not actually be that hard to migrate over to the aggregation framework. I'll take a look.

You're correct, however note that 2.3/2.4 fixes the lock on the javascript interpreter with the move to V8 as the default engine.

I noted v8 in my comment; it won't be a panacea. It certainly doesn't fix the encoding/decoding overhead of BSON<->JavaScript, but the JIT and multithreading will help in other areas.

Group still will not become sharding-capable with v8, either.

Came here to mention the aggregation framework. +1

The example would be much clearer as an aggregation query. Roughly:

    db.demo.aggregate([
        { $match: { score: { $gt: 0 },
                    person: { $in: ["bob","jake"] }}},
        { $group: { _id: "person",
                    sumscore: { $sum: "$score" },
                    avgscore: { $avg: "$score" },
                    minscore: { $min: "$score" },
                    maxscore: { $max: "$score" },
                    count: { $sum: 1 }}}
    ]);

I've also found this diagram incredibly helpful translating from a SQL to map/reduce world.

http://rickosborne.org/blog/2010/02/infographic-migrating-fr...

Thanks @gry, and here is the direct link to the PDF: http://rickosborne.org/download/SQL-to-MongoDB.pdf

I didn't realise that Mongo was so...verbose.

Not particularly useful, I tried a simple join and I got this error message:

Failure parsing MySQL query: Unable to convert queries based on more than one table

Sorry about that! Built this in a hackathon and this first version doesn't support joins yet.

If you built all this in a Hackathon then I'm impressed - well done!

Wouldn't you just use nested documents in mongo though?

Very nice! I once thought about doing something like this, but I had some real work to do. Thanks for this resource!

This is specially useful because of the verbosity and ugliness of the "JSON" API (much more difficult to get right by hand than SQL) and because I found 0 working GUI tools to work with Mongo in a mac (they all crash at startup or after ~5 seconds of usage in a modern mac).

MongoHub (http://mongohub.todayclose.com/) works for me.

It's not very polished, but it gets the job done, and it makes it relatively easy to edit stuff that's already in the database.

any particular reason you are not mapping to the aggregation framework which is a lot faster ?

Perhaps less of SQL can be mapped to the aggregation framework than to map/reduce.

Nice work. Played around with it, and found that

SELECT * FROM demo WHERE score > 0 AND person LIKE '%bob%'

doesn't work that well. It will need to convert the %bob% thing to a regular expression.

Awesome! I haven't played around with NoSQL databases and I always wondered how you would query one. Definitely peaked my interest in the whole movement.

I find it pointless. If you want to write SQL queries just use a relational database, if you want to use Mongo learn to use it.

You could use it as a tool to learn Mongo query syntax assuming you have SQL down.

HAVING clause is being ignored. i.e. try adding

  HAVING SUM(abc) > 1

to the example.

Is there one that goes in the other direction?

are there any plans to open source this?

+1 for this. Would be interested in seeing how this is done in code to further my understanding of MongoDB. Would be interesting to create a reverse translator as well.

map/reduce anyone ? if you are using mongodb like a relational database , then maybe you should just stick to relational databases...

That's like saying if you're using a swiss army knife as a knife, maybe you should just use a regular knife.

Reply on news.ycombinator.com