Migrate data from relational DB to NoSQL

Question

Is it possible/are there tools/ best practices etc to migrate data to a NoSQL format from a relational DB.

I have a JEE6 app making use of Hibernate ORM to persist to MySQL but now we wish to move to NoSQL solution but need to bring the existing data with us

Thanks W

Answer 1

I did the M110JS "MongoDB for Node.js Developers" course from Mongo University https://university.mongodb.com/ and can wholeheartedly recommend it (it is free as well).

It does cover (I think in only one of its weeks) the differences between SQL and NOSQL, like when not to use foreign keys but rather embed documents, risking duplicates etc and how to handle it.

It only starts every so often, so this is only a long-term approach.

Answer 2

To simplify things a bit, an Oracle database would have complete mastery over what’s being stored in the database. Oracle or any RDBMS would do this by maintaining relationships between chunks of data stored in tables.

A Document Based database on the other hand stores mostly the how the data is being consumed in the system. It achieves this using the Key-Value pairs, with keys playing certain pivotal variable within.

It would depend on the complexity of the system being migrated, on Volumes, functionality to be offered etc. Though there are multiple ways to migrate from a RDBMS to a json based database, a standard approach would involve constructing views on the existing SQL DB and migrating in stages.

Of course the fundamental process in such a migration is to start with Schema re-design as first step. In a document structure such as Mongo JSON or BSON, most parent child relationships can be accommodated into a single structure(or document). For example, Person_ID, Car_Ownership_ID can both be combined to produce a single json document that lists all the owners of a car.

A Sound schema design process would keep in certain goals under consideration, while denormalizing the fully normalized database in the RDBMS. As a good second step, most JOINs will need to be combined into interweaved collections for easier access with the exceptions of certain outer joins.

Once the Schema is ready, an ETL process or a substitutive script can be used to extract, transform and load the newer schematic with a replica of data in the RDBMS.

Answer 3

There are some tools to help the migration, but in the end, MySQL is a relational database which has a completely different structure from noSQL databases.

In the end, you will almost always have to do these four steps stated in this article (refers to mongoDB, and you didn't specify, but it applies to any):

1. Get to know MongoDB. Download it, read the tutorials, try some toy projects.

2. Think about how to represent your model in its document store.

3. Migrate the data from the database to MongoDB, probably simply by writing a bunch of SELECT * FROM statements against the database and then loading the data into your MongoDB model using the language of your choice.

4. Rewrite your application code to query MongoDB through statements such as insert() or find().