Show HN: SQL to Mongo Query Translator

http://www.querymongo.com/

by robertjmoore • 14 years ago

62 33 14 years ago

querymongo.com

christkv• 14 years ago

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

lucian1900• 14 years ago

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

polskibus• 14 years ago

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.

marginalboy• 14 years ago

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

w0utert2• 14 years ago

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

debacle• 14 years ago

MongoDB's query language is just not very good.

hmexx• 14 years ago

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...

cheald• 14 years ago

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.

rmrfrmrf• 14 years ago

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

sgt2• 14 years ago

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

robertjmoore2• 14 years ago

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

sgt2• 14 years ago

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

jurre• 14 years ago

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

rit2• 14 years ago

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.

robertjmoore2• 14 years ago

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.

ukd1• 14 years ago

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.

rit2• 14 years ago

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.

scottc• 14 years ago

Came here to mention the aggregation framework. +1

e98cuenc• 14 years ago

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

w0utert2• 14 years ago

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.

redler• 14 years ago

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 }}}
    ]);

camus• 14 years ago

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

cjfont• 14 years ago

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

isabre• 14 years ago

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.

gry• 14 years ago

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...

jaytaylor• 14 years ago

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

rolfvandekrol• 14 years ago

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.

aioprisan• 14 years ago

are there any plans to open source this?

skram• 14 years ago

+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.

fox91• 14 years ago

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.

hellopat• 14 years ago

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

nohaving2• 14 years ago

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

  HAVING SUM(abc) > 1

to the example.

hayksaakian• 14 years ago

Is there one that goes in the other direction?

Show HN: SQL to Mongo Query Translator

Related Stories