2

Does anyone know of any product or open source project that attempts to strap together Mongo DB and MySQL for instance?

I hear a lot about how relational DBs being ACID compliant makes it nearly impossible to do sharding as easily as in Mongo and this is the achilles heel. The NoSQL fans are surely right. A technology must eventually parallelize or die.

No to NoSchema

However, schemaless DBs are not a sustainable solution to many problems. Schema changes are only hard because they force you to see what you're breaking. Not seeing doesn't mean you're not breaking.

Best of both?

What I need help understanding is why it seems accepted that you can't in theory have the advantages of both. Can't a relational DB be engineered to compromise with a wise strategy of eventual consistency? I understand why it's more difficult than with documents - a single fetch is composed of records stored physically all over the disk. But how about a document store which acts as a front for a relational DB?

Imagine

The DB has a built in ORM to map from relational to document collection and you interface with the document in your app. Essentially, the document collections are like materialised views with write-back capability. By default the relational DB part is ACID compliant and ensures that non-acidic documents are eventually consistent. Cross-record schema constraints may not be performant enough but most other schema controls could be.

When you query a large batch of documents, if a few are out of sync - because another document sharing the same denormalised data has just been written to - then they are silently refreshed live from the underlying DB by default. If blocking is an issue then you can choose to handle those dirty docs with a number of other looser consistency options.

Pros and pros

..and small cons

You don't get the document store advantage of fast writes but

Gets NoSQL advantage: massive and fast reads, easy sharding and no O-R impedance mismatch for front-end developers.

Gets SQL advantage: Organisations can stay nimble, able to work with their data from new angles not thought of initially. Schema helps keep production data from turning into a turgid mess of exceptions before its too late.

Gets hybrid advantage: Rows can be physically re-organised in the RDB to follow the patterns suggested by the most frequently accessed document collections. When the load gets too high for the relational DB on one box this will make sharding more of a possibility (with a possible tradeoff against whole-system analytical queries but that's what you have an OLAP for). The relational DB will also have much fewer reads than if it were directly user facing.

I've misunderstood something. I must have.

I don't imagine this is an easy bit of engineering but it seems within the powers of the those who have brought us the products we already have.

I'm not seeking general discussion here but I wouldn't be surprised if there's a big, concrete gotcha I'm missing in this account and I'd like to know what it is.

Improve this question
2
  • I don't see the problem. A "Document Store", to me, is a collection of relatively independent items -- little or no need for ACID; easily sharded; etc. Maybe I need a better description of what you mean by "Document Store".
    – Rick James
    Commented Jan 13, 2016 at 20:10
  • Sorry, I clarified the question. You're right the document store part doesnt need ACID. I meant just the underlying relational DB. I've read many accounts of developers choosing Mongo because their documents were relatively independent. They gradually find that the few inter-dependencies grow and their data is relational enough for Mongo to be a pain.
    – Adamantish
    Commented Jan 13, 2016 at 20:40

3 Answers 3

Reset to default
1

SQL Server has JSON support though it seems to be a variation on it's XML support.

PostGres has had JSON support since 9.2. Teradata has had it since 2014.

This makes them hybrids. Obviously PostGres is the open-source one.

It really depends on what you need from JSON support. If it is to return the document on a key then that has always been possible without explicit JSON support.

If you want to index certain parts of the JSON document then that does require specific JSON support. There is the question of how sparse the attribute is that you want to index. If it is present in the vast majority of cases (or even a mandatory attribute) then I would break it out of the JSON and have it as an explicit attribute in a hybrid store.

If by parallelize or die you mean support a distributed dataset or die then I'm not sure that I agree. For OLTP work you would have to be in a Times top 100 company to approach the limits of the traditional RDBMS. By definition most companies just don't generate as much useful data as they would like to think.

Companies like Facebook, Twitter and NetFlix are dealing with data on an order of magnitude far beyond what most people will see.

For high end web analytics work then yes, you might want a NOSQL product as a collector. Cassandra is useful in that respect, plus it has tunable consistency.

The gotchas in distributed systems is Brewers CAP theorem. You can have any two of Consistency, Availability or Partition tolerance. It's a bit more blurred than that but the 2 of 3 rule is generally true.

There's the challenge of how you handle referential integrity and in some cases how do you honour primary key constraints. Your data might be splashed across many servers so honouring a foreign key concept might require a lookup between one server and another. Even if this were a feature it would seriously impact performance.

If people want to interact with an API that only talks JSON then I see no problem with that providing there is an underlying DAL that obfuscates whether a data attribute is present in a JSON document or explicit attribute in a hybrid design.

Improve this answer
0

Not sure this is what you are looking for, but I think that the major players in the RDBMS field will add support for JSON the same way they added support for XML a couple of years ago. You can for example have a look at:

http://www.ibm.com/developerworks/data/library/techarticle/dm-1306nosqlforjson1/

Another approach is to add some kind of product that can glue together different dbms paradigms. Apache Spark might be an example of such:

http://spark.apache.org/
Improve this answer
1
  • Interesting. Judging by Apache featuring in both these answers it seems they have a strong interest here. As for JSON support I'm talking about more than some just tools to query a data format. That's necessary but not sufficient. I'm saying that in the application I add an item to the JSON node named "Interests". It adds the node to the document and adds a row to an underlying table. Next I add an item to the "GenderOptions" node and it fails because it's non-PC and because for some reason each GenderOption is mapped from a column and users can't add columns.
    – Adamantish
    Commented Jan 13, 2016 at 22:27
0

You can read more about Apache Phoenix which is Relational DB over HBase (NoSQL).

Improve this answer
1
  • 1
    Interesting but is probably solving a different problem. In this case the NoSQL part is a massive HBase table rather than document collection. That takes away the appeal for OO programmers. Related data is still stored in separate HBase tables. Though the sharding of these may be well handled it still means data must be fetched from lots of places to get one webpage. That removes the performance appeal for relational data.
    – Adamantish
    Commented Jan 14, 2016 at 1:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.