Migration from relational database to NoSQL database

Do we really need NoSQL databases?

Relational database model was proposed in 1970, since then we are using RDBMS for most of the applications. But this model is having a hard time keeping pace with the volume, velocity, and variety of data. To keep pace with growing data storage needs, NoSQL databases were introduced in which the focus has shifted from relationships in data, to have a scalable solution to store large volumes of data.

Relational databases focus on ACID (Atomicity, Consistency, Isolation, and Durability) property whereas NoSQL focus on CAPs (Consistency, Availability, and Partition tolerance) theorem.

Market share of Relational and NoSQL database

The chart shows the Relational databases were used 51% whereas NoSQL databases were used 49% during the period of March 2013 – 2014

During period of March 2014 – 2015 the use of NoSQL databases increased to 59% whereas the use of Relational database reduced to 41%

Advantages of NoSQL Database

Fast

Each table in NoSQL is independent of the other. NoSQL provides us the ability to scale the tables horizontally, so we can store frequently required information in one table. All the table joins need to be handled at application level. Thus, data retrieval is fast.

Scalable

As the user base grows and we require a database which has capabilities to handle the added load, most of the NoSQL databases have the capabilities to scale as the data grows.

There are various types of NoSQL databases available in the market, and the scalability of database varies among different types, we have to choose the database as per our application needs. Scalability can be categorized based on:

Read Scaling: Large number of Read operations
Write Scaling: Large number of Write operations

Schemaless

In relational databases, for each table we have to define a schema, where we specify the number of columns and the type of data it holds. It is difficult to change the datatype of the column, and adding new column will result in lots of null values in the table. In NoSQL databases, adding/removing column is easy because we don’t have to specify schema on table creation.

Challenges faced while moving from relational system to NoSQL

Which type of NoSQL Database to choose – NoSQL itself are divided into 4 different types.

- Key-Value pair – This type of database store various key-value pairs within a record. A Key can be string or numeric value, which needs to be unique within a record. A Value holds string, binary, numeric, BLOB, JSON.

Examples: Redis, Dynamo, CouchDB, Oracle NoSQL Database, Riak

- Document – This type of database is generally used for storing Semi-Structured data. The database implementation details differ for different document store databases. The data are encapsulated and encoded in standard formats, encoding of a document includes XML, YAML, JSON, and BSON for binary data.

Examples: Couchbase, DocumentDB, MongoDB, OrientDB, HyperDex

- Column – In this type of database, data is stored column-wise instead of row-wise, which results in easy analysis and aggregation of the data. Columns are grouped into families logically and can contain an unlimited number of columns that are created at runtime. Read and Write is carried out using columns instead of rows.

Examples: Vertica, Cassandra, Accumulo, Druid, HBase

- Graph – The graph database is specially designed for an application where relations are represented as graph structures in which elements are interconnected and have a relationship between them. Graph database consists of nodes, properties, and edges. The application containing social relations, public transport links, network topologies are best suited for Graph databases.

Examples: Stardog, Virtuoso, Allegro, InfiniteGraph

Each of the databases has its own advantages; we can choose the database type based on the application requirement.

Data Modeling – We need to see how we will map our existing data types to new databases, most of the NoSQL have very rich support for array, list and maps data types which will help us in modeling our data in effective manner. Mostly all the data types supported by SQL will have an equivalent option in NoSQL databases. Additionally, most of the times there is no hard limit on length of data-type but, there would be a limit on row size.

Denormalization – All our life we had learnt to normalize the database but, in NoSQL, repetition of data is acceptable, this will increase the read efficiency and query would be fast because we don’t have to read data from multiple tables. We have to be careful while designing the databases and should repeat data only if required because, in NoSQL, tables are not related with each other if data is updated at one place our application needs to synchronize the data.

Table Usage – Most of the application have some tables which are read heavy or write heavy to model our database in NoSQL we have to keep in mind these two things so that we can manage accordingly. Try to choose databases that would be best suited for the type of application which you are building.

Indexing – Indexing is very important part of the database. Most of the applications require good searching capabilities. Some of the NoSQL databases index column by itself, some required to be specified externally as per requirement. Try to define indexes on all the columns that require searching/sorting, but don’t overdo things and define indexes on all the columns because this will affect the performance of the database.

No Relationship – Tables in the NoSQL do not have any relationship between them. All your relationship needs to be handled at the application level. You need multiple queries if you want to get data from multiple tables and process it at application level to get the desired output.

Procedure, Function, and Triggers – You might have a complex function and procedure written in your existing system that need to be moved to NoSQL. Some of the NoSQL databases support functions and triggers, some of them provide alternative services that will help in achieving the output for other we need to manage at the application level.

Moving existing data – This might be a tricky situation if you have a huge dataset that requires migration. Some of the NoSQL databases support various tools for data migration. Another common way to migrate data would be by converting data to CSV format and import the data as JSON.

Thus, while moving from SQL database to NoSQL database we have to consider the above mentioned points.

If you have any questions, feel free to add your comments.