Wednesday, June 19, 2013

Planning to Migrate Your Relational database to MongoDB?

Are you planning on migrating your relational database data in Oracle, MS-SQL, MySQL, DB2, PostgreSQL etc., into MongoDB for scalability and performance reasons?

Or you want to use MongoDB for your new project, but not do not want to spend too much time learning MongoDB APIs? However, you are a seasoned SQL developer?

Or you have data in databases, CSV files and other applications that you need to integrate together and store in MongoDB and do further analysis?

If "Yes", please keep reading...

If you have previously attempted at migration of data yourself, I am sure you may have come across these below links before.

http://docs.mongodb.org/manual/reference/sql-comparison/
http://docs.mongodb.org/manual/reference/sql-aggregation-comparison/
http://docs.mongodb.org/manual/tutorial/model-embedded-one-to-one-relationships-between-documents/
http://docs.mongodb.org/manual/tutorial/model-referenced-one-to-many-relationships-between-documents/
http://docs.mongodb.org/manual/tutorial/model-embedded-one-to-many-relationships-between-documents/

MongoDB lays out various concepts on how one can convert their data into MongoDB and then explains how to access the same using their API. MongoDB is not a relational database, it is a document database. MongoDB stores the data BSON format. MongoDB does not support JOINS like relational databases for joining the related data between multiple documents, however MongoDB document model supports "embedded" documents, where using above techniques one can convert their relational data by carefully mapping 1-to-1, 1-to-many, many-to-many relations into MongoDB based documents. You can effectively view these mapping operations as de-normalization of data in relational terms. De-normalization brings its own rewards and risks, so we will not go that debate here why it is good or bad. For our desire to use MongoDB and access it using SQL, we have to find an acceptable medium that we can work with.

All good so far, however it takes lot of time and effort to do the above mapping of relational schema into MongoDB document structure and learn to use the MongoDB querying APIs. This is where Teiid steps in.

Starting with Teiid 8.4 release, Teiid is introducing MongoDB translator and MongoDB JCA adapter. Essentially implementation of this translator does exactly what is been prescribed in the above links by MongoDB. Out of the box, it gives you a simple way to design MongoDB document schema based on your existing relational schema. You can export DDL from your database and use it with Teiid with some minor modifications to add some extended metadata in aid to design the MongoDB document structures. Once you defined your MongoDB database, you can migrate data from your relational store to this newly created MongoDB store using Teiid by simply issuing statements like

"SELECT * INTO MongoDB.Table FROM Oracle.Table"

At the same time, Teiid gives you full SQL based access to the data residing MongoDB, with out you using any MongoDB specific APIs. If you already have end user applications written for the old database, you can essentially switch it over to use MongoDB without affecting them using Teiid.

Not trying to convert relational database? Your data is in CSV files, or web services, saleaforce? No matter where the data currently resides, using Teiid you can bring that data in and store in the MongoDB. Depending upon where the currently exists, you may have to write a connector and a translator. You must be thinking there must be some gotchs..

Sure, for full disclosure there are

  • giving up on the "schema-less" or dynamic schema model that MongoDB supports
  • Some automatic duplication of data to achieve de-normalization
  • Not all tables can be designed for joins, where the relationships do not exist. Here Teiid query engine steps into picture and provides JOINS on non-key based columns or on un-related tables, but those queries will be executing in the Teiid query engine, not in MongoDB
For more information on MongoDB translator read about it here. There few more enhancements we have planned, but your input in driving this is most appreciated. So, take it for test drive, come to Teiid forums and discuss about any questions and comments or enhancement requests.

In the next blog, I will walk through an example in taking a sample database and show you how to migrate step by step. Till then...

Thanks

Ramesh..

No comments:

Post a Comment