Skip to main content

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

Comments

Popular posts from this blog

Teiid 8.11 Beta1 and 8.10.1 Released

Teiid 8.11 Beta1 is now available from the  downloads  and maven.  Feature highlights since Alpha2 include: TEIID-3434 More caching control over ttls in the result set cache down to the schema/table level. TEIID-3412 MS Access support via the UCanAccess driver. The UCanAccess support is necessary for those running on Java 1.8 as the JDBC ODBC bridge has been removed from the JRE. The waiting continues on EAP 6.4 Alpha1 - it still should be available shortly and should be the platform target for Teiid 8.11 Beta2. Of course, let us know if you find any issues with these early releases.  There's still plenty of time to get fixes into the final release. Teiid 8.10.1 is also available.  It addresses 5 important issues discovered since 8.10 was released: [ TEIID-3409 ] - PostgreSQLExecutionFactory TranslatorProperty annotation in wrong place [ TEIID-3437 ] - Inconsistencies with row count handling [ TEIID-3438 ] - Null value returned from BlobImpl

Tech Tip: Teiid SQL Language MAKEDEP Hint Explained

In this article I will explain what a MAKEDEP hint is, how and when, why it should be used in Teiid. What: MAKEDEP is query hint.  When a query hint is defined in the SQL query it influences the Teiid query planner to optimize the query in a way that is driven by the user. MAKEDEP means "make this as a dependent join". What is a Dependent Join? For example if we have query like: SELECT * FROM X INNER JOIN Y ON X.PK = Y.FK Where the data for X, and Y are coming from two different sources like Oracle and WebService in Teiid, so in relational algebra you can represent above query as Here the result tuples from node X and node Y are being simultaneously fetched by Teiid query engine, then it joins the both the results inside Teiid engine based on the specified X.PK = Y.PK condition and returns the filtered resulted to the user. simple.. Now, what if, if X table has 5 rows and Y table has 100K rows? In order to do the JOIN naively Teiid need sto read all the 5

Teiid 8.13.3 Released

Teiid 8.13.3 is now  available .  In total 8.13.3 addresses 10 issues since 8.13.2: [ TEIID-4028 ] - adding salesforce-34 resource adapter does not work through the cli [ TEIID-4066 ] - Odata translator ClassNotFoundException: com.sun.ws.rs.ext.RuntimeDelegateImpl [ TEIID-4070 ] - Issues with resource adapters with api modules in wildfly [ TEIID-4089 ] - Teiid JDBC driver does not reset the update count when calling getMoreResults(int) [ TEIID-4093 ] - OData authentication fails with NPE when gss-pattern related properties are included in VDB [ TEIID-4096 ] - AssertionError with independent side of a dependent join that has an ordered limit [ TEIID-3050 ] - allow for more incremental insert with iterator [ TEIID-4075 ] - Netezza translator to support common table expressions [ TEIID-4098 ] - Always preserve columns order in google spreadsheets models [ TEIID-4046 ] - OData - $skip is beeing ignor