Industry Voice: Mapping The Great Relational Migration

clock • 3 min read
Industry Voice: Mapping The Great Relational Migration

The cliché is that data is the new oil and if recent events have taught us anything, it is that it can get very expensive, very quickly. This is especially the case if it's not refined the right way for what people actually need. And what people need more and more is data refined for the document model. Databases such as MongoDB offer global scalability combined with rapid development.

While relational databases work for a number of scenarios, more and more companies are discovering the document model as a more flexible way of storing and accessing their information. For them the issue is how to move their data to the document model.

The obvious, but incorrect, answer is to import the data straight into the document database. This is akin to taking diesel and putting it into the tank of a petrol engine. Although both are hydrocarbon fuels, they have physical attributes which make them work very differently. They are not, as many drivers discover, interchangeable.

The same goes for data. The essential components are the same, but just swapping it over and feeding it into a document database will clog up the database's engine with relational lookups which, while you can do them, are counter to an efficient document database. What you need to do is to refine the data into documents, documents that match both your business and database.

That's why the real answer to moving data from table-centric relational stores to a document database is to perform a relational migration. At Studio 3T we've developed SQL to MongoDB and MongoDB to SQL migration tools. These let you take your SQL database's foreign keys and other tables and turn them into embedded arrays or documents within your new database's collections. This mapping of relational to document is under your complete control so you can fine-tune the results.

So the "customer account" records and table of "all transactions" can turn into a document per customer, with all the customer's transactions embedded in that document. This highly localized model for data works well because once you've retrieved the customer's account, you don't have to do an expensive join (in CPU and IO terms) to pull in all the transactions. They are already in the document you just retrieved.

It's not a one-shot process; we've worked with developers who've used Studio 3T's Tasks to automate and tune their migrations and perfect their mapping so they can obtain the optimal document structure for their data in a document world. And we've worked with Hackolade to bring their powerful modeling tool into the process, allowing a user to graphically redesign their models then passing that redesign onto Studio 3T to put it into practice.

A similar mapping technology also powers Studio 3T's Reschema tool. That can be applied to already migrated documents so they can be restructured in-situ and reduce the number of times the whole migration has to be run. We think that the more opportunities for tuning the data and its schema, the better for everyone.

We've been doing this for a while, so we were pleased to see the behemoth of document databases, MongoDB themselves, announce that they plan to release their own relational migration product at some point in the future. It validates what our customers have been doing with great efficiency and success for years using our battle-tested tooling. And the great thing is the tools are available for download today.

It also shows that the document database is expanding its reach, from being the choice of plucky startup, to the next database paradigm for enterprises.

Legacy relational data is facing the combined challenges of global agility, global compliance and the need for rapid development.

The tools to enable an effective migration to the document model are already here.

This post was funded by 3T Software Labs

Sign up to our newsletter

The best news, stories, features and photos from the day in one perfectly formed email.

More on Big Data and Analytics

Belfast to spearhead UK's digital revolution with £37m Digital Twin Centre

Belfast to spearhead UK's digital revolution with £37m Digital Twin Centre

Aim is to foster innovation across engineering sectors

clock 02 May 2024 • 2 min read
Even CERN has to queue for GPUs. Here's how they optimise what they have

Even CERN has to queue for GPUs. Here's how they optimise what they have

'There's a tendency to say that all ML workloads need a GPU, but for inference you probably don't need them'

John Leonard
clock 17 April 2024 • 4 min read
Partner Content: Why good data is the foundation of AI success

Partner Content: Why good data is the foundation of AI success

Does your organisation have the right quantity and quality of data to make its AI ambitions a reality?

clock 04 April 2024 • 2 min read