Migrating from MariaDB to MongoDB

Howdy!

In my previous post we talked about MongoDB’s growing popularity and its compatibility with other databases with regards to business intelligence and scalability. I also wrote about how documents in a collection need not be well defined or structured and demonstrated some CRUD operations using Mongo Shell through my video.

Let me take this topic further and talk about migrating from an RDBMS to NoSQL, especially MongoDB.

Data partitioning and data replication are the major features that set NoSQL apart from conventional RDBMS. Despite these advantages, most big and medium-sized software systems currently in use are still RDBMS-based since the migration process is fraught with difficulties. The first is the amount of data that needs to be moved. Organizations typically decide to move their databases when the amount of data being stored is enormous and the RDBMS can no longer meet the demands for high availability and scalability. The way the relational database architecture avoids data redundancy, which is a feature of the NoSQL models, presents another obstacle.


The original data model must remain semantically identical to the new data model in this case. Therefore, without any data loss or modification, all current relationships must be precisely recorded (Normalize). In addition to the expense of transferring all the data and models, there is also a cost associated with upgrading software’s so they can successfully connect with the new MongoDB database.


Whether the target and source databases are of the same kind or not, migration involves moving the source database's entire contents, including its structures and data, to the target database. In my situation, switching from MariaDB to MongoDB is a difficult undertaking. The procedure of retrieving data is greatly complicated by the necessity of changing the application source code to conform to the new database.


For this blog post I wanted to convert one of my Maven web applications projects that was connected to MariaDB to MongoDB and demo it to my readers. I succeeded in converting the database (converting tables to collections and maintaining some key values that had relationships with other collections) but when it came to creating models and controllers to access the data, everything went for a toss!!

Until you are successfully connecting to your new database and doing certain operations with it, its not considered a successful migration!!

I was very confident about writing queries and manipulating the data but for some reason even my Netbeans debugger could not properly identify the connection issue. 🤔 After umpteen number of coffees and stack overflow searches, I gave up!

My son Neel drew this!😄


One of the main reasons to give up was also related to not having enough time on my hand. This semester has been one helluva time crusher with a zillion deliverable. I have promised myself to give this web app a go sometime in September so for now I’m going to migrate a simple SQL database to MongoDB hosted in my cluster on Atlas and write a java program to do some CRUD operations. I will use my favorite Eclipse to do the deed!


To demonstrate this Migration process, I will use a java program we (Jenny and I) wrote during our second semester java course. Below is a quick video on how the application compiles and generates data we request from a Movie SQL database (Movies!! Now you know why I picked this application!😉

For some reason the embeded video is not responding.(Firefox browsers usually🙄) In that case, try this link


Two of MongoDB's primary architectural objectives are developer productivity and ease of usage. The MongoDB interface is implemented as methods (or functions) within the API of a particular programming language, as opposed to a wholly distinct text-based language like SQL, which is a key distinction between a SQL-based RDBMS and MongoDB. This makes application integration simple, as does the similarity between the BSON document model used by MongoDB and the data structures used in object-oriented programming.


MongoDb has idiomatic drivers for most of the popular languages including Java and a lot of community supported drivers. This offering minimizes onboarding time for new developers like me and simplifies application development. For those who are familiar with SQL, it is useful to understand how core SQL statements such as CREATE, ALTER, INSERT, SELECT, UPDATE and DELETE map to the MongoDB API. [1] The documentation includes a comparison chart with examples to assist in the transition to MongoDB Query Language structure and semantics. In addition, MongoDB offers an extensive array of advanced query operators


There are numerous ways for project teams to import data from current relational databases into MongoDB. The project's stage and the environment at their disposal should determine the tool to use. Many users write their own scripts that convert source data into a hierarchical JSON structure so that it may be imported into MongoDB with the help of the mongo import tool.

Few options of migrating data

Many migrations require incrementally transferring production data while operating the current RDBMS in parallel with the new MongoDB database

  • The program publishes records back into MongoDB using the needed document structure as they are fetched from the RDBMS [2].

  • Only MongoDB is used to store any newly created or changed data.

    When implementing new application features with MongoDB or when several apps are running on the legacy RDBMS, incremental migration can be used. Teams can divide projects into more manageable and agile development sprints by simply migrating the applications that are being upgraded. While limiting service availability interruption, incremental migration also offers a fallback alternative in case it becomes necessary to return to the legacy database.

To execute parallel processes, develop applications, or conduct load testing, many businesses create feeds from their source systems, wherein they dump daily changes from an existing RDBMS into MongoDB.

Making "A" and "B" target databases in MongoDB and switching their daily feeds between them is one approach solve the problem. In this case, Database A only receives one daily feed before the program switches to Database B to receive feeds on the next day. When fresh feeds are made to Database A, a whole new instance of the source database is generated, ensuring that deletions are synchronized with the source data. In the meantime, the existing Database A is deleted.


MongoDB Atlas has several uses. Everything from a simple Proof of Concept to test/QA environments to finished production clusters, works beautifully with it. Moving your databases onto your own infrastructure and managing them with MongoDB Ops Manager or MongoDB Cloud Manager is simple if you decide you want to take charge of operations again.[2]


You won't suffer any disruption if you choose to migrate to your own infrastructure because the user experience across MongoDB Atlas, Cloud Manager, and Ops Manager is consistent. For businesses considering MongoDB as a possible replacement for their relational database but without wanting to commit their operations staff to the evaluation process, MongoDB Atlas is the perfect solution.

And now its Alt Text

First, I converted our SQL cprg251 database that had Movies table to CSV using command line interface. This is the easiest way since we don’t need any additional software’s or plugins. To do this, just navigate to the database stored in your system using the following command
	USE dbName

Screenshots of me getting to my dbName(cprg251)

Select all data from the table, specify a location(path) for the output file to reside. Observe that you need a .csv extension for your output file. The LIMIT clause restricts the number of rows that is copied to the output file. You can also add an ORDER clause (next to table_name) to arrange the data in a particular way. In my case I had to select everything from the table.
SELECT * FROM movies
INTO OUTFILE 'D:/Netbeans/movie_data.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\n';

After converting SQL to CSV, open the file in MS Excel to add a top row and name all the columns. This will come in handy when the .csv file gets converted to JSON.

movies_data.csv

Open MongoDB compass/ Atlas, connect to your cluster, choose the database and collection where you want to add the documents. Click on ‘import file’ and select the ‘CSV’ option and import your output.csv file. It will get converted to JSON files and would look like this

BlogData/TinselTown/Movies collection

Watch the video below to see how I connect my MongoDB database to my java application and run some operations.

For some reason the embeded video is not responding.(Firefox browsers usually🙄) In that case, try this link

Another method to migrate using the Mongify tool is discussed in this blog on Digital Varys.

Some say, if you use 'this' feature in Oracle, then the migration will be hard! Or they say, if you only use 'these' features in Oracle, then the migration will be easy! My take is NO, there isn't a way to do a quick analysis. Unless your use of an RDBMS is extremely simple, like the Movie Database, it is almost certain that MongoDB cannot be a drop-in replacement, and that you will have to invest in rewriting parts of your application to make the switch.


A couple of major differences between MongoDB and any RDBMS is that as a NoSQL DB, MongoDB has no joins. You would have to look at your application and see where you depend on these and find ways to rewrite your application to avoid needing them. In some circumstances, both can be avoided by modelling detail situations using embedded documents. But if you need to model many-to-many relationships, you're going to have to add logic to your application to deal with that, because MongoDB don't do that.


To conclude MongoDB gets its performance gains through simplicity. It provides less functionality than an RDBMS, and in exchange, several other things emerge. Following some best practices will help project teams reduce the time and risk of database migrations, while enabling them to take advantage of the benefits of MongoDB and the document model. In doing so, they can quickly start to realize a more agile, scalable and cost-effective infrastructure, innovating on applications that were never possible before.

References

  1. https://s3.amazonaws.com/info-mongodb-com/RDBMStoMongoDBMigration.pdf
  2. https://oldctl.choicetechlab.com/blog/rdbms-to-mongodb-migration-guide
Written on July 24, 2022