Simplifying Data Migrations From Legacy SQL to MongoDB Atlas with Studio 3T and Hackolade

Migrating data from SQL relational databases to MongoDB Atlas may initially seem to be a straightforward process. Export the data from the relational database, import the tables into MongoDB Atlas, and then start writing queries for it. But the deeper you look, the more it can start to feel like an overwhelming task.

Decades of irrelevant indexes, rare relationships and forgotten fields that need to be migrated all make for a more complicated process. Not to mention, converting your old schemas to work well with the document-based world of MongoDB can take even longer. Making this process easier and more achievable was one of Studio 3T’s main goals when they created their SQL to MongoDB migration tools.

In fact, Studio 3T's SQL migration doesn't just make this process easier; it also makes it reliably repeatable. This means you can tune your migration to produce the perfect documents in an ideal schema. There's no big bang cut over; you can proceed at your own pace. Delivering the ability to perfect your new schema is also why we've integrated with Hackolade's schema design and data modeling tools.

In this article, we look at how the data modernization process works with a focus on the SQL migration powers of Studio 3T and the data modelling technology of Hackolade.

So the problem was, how do I migrate the data that’s been collected over the last 10 years from MySQL over to MongoDB? And that’s when I found Studio 3T. I could not imagine trying to do it myself by hand... If it hadn’t been for Studio 3T, I probably would have just left it all in the SQL database.

Rand Nix, IT Director, Wakefield Inspection Services

Why MongoDB Atlas

Building on MongoDB’s intuitive data model and query API, MongoDB Atlas gives engineering organizations the versatility they need to build sophisticated applications that can adapt to changing customer demands and market trends.

Not only is it the only multi-cloud document database available, it also delivers the most advanced security and data distribution capabilities of any fully managed service. Developers can get started in minutes and leverage intelligent automation to maintain performance at scale as applications evolve over time.

Mapping the move

The crucial component to a SQL migration is mapping the SQL tables and columns to JSON documents and their name/value fields. While the rigid grid of relational tables has a familiar feeling, a document in MongoDB can be any shape and, ideally, it should be the shape that makes sense for your business logic.

Doing this by hand can prove extremely time-consuming for developers. Which situations call for reshaping your data into documents and which should you construct new documents for? These are important questions which require time and expertise to answer.

Or, you can simply use Studio 3T's SQL Migration tool which is a powerful, configurable import process. It allows you to create your MongoDB documents with data retrieved from multiple relational tables. Using foreign keys, it can capture the relationships between records in document-centric form.

"When we started out, we built it on SQL Server. All was good to start, but by the time we got up to fifty customers and more, each with some thousands of their staff logging into the system concurrently, we ran into scaling issues.

"Now we’re using a hosted, paid-for subscription on MongoDB Atlas. They do all the management and the sharding and all that stuff. And when we switch to regional provisioning, they can handle all that too, which is a huge relief.

"We all use Studio 3T constantly, it’s used as extensively as Visual Studio. We use it for all sorts of things because Studio 3T makes it really easy to navigate around the data."

Dan Cummings, Development Mgr. Terryberry Inc.

For example, a relational database may have a table of customers and a table of orders by each customer. With the SQL Migration tool, you can automatically create a collection of customer documents, containing an array of all the orders that customer has placed. This exploits the power of the document model by keeping related data local. You can also preview the resulting JSON documents before running a full import so you are sure you're getting what you want.

Figure 1. Studio 3T’s SQL to MongoDB Migration

”

In Figure 1 (see above), we can see Studio 3T's SQL Migration pulling in multiple SQL tables, embedding rental records into the customer records, and previewing the JSON output.

For more radical restructuring, SQL queries can also provide the source of the data for import. This allows for restructuring of the data by, for example, region or category.

Hackolade integrated

As you can see, Studio 3T is fully capable of some complex migrations through its tooling, SQL Migration, Import and Reschema. However, if you are of the mindset that you should design your migration first, then this is where Hackolade comes in. Hackolade is a "polyglot data" modelling application designed for a world where models are implemented in everything from graphs to APIs to storage formats.

Figure 2. Entity Relationships Mapped in Hackolade

”

Using Hackolade, you can import the schema with relationships from an SQL database and then visually reassemble the SQL fields to compose your MongoDB document schema.

Watch this demo video for more details.

Once you've built your new models, you can use Studio 3T to put them into practice by importing a Hackolade schema mapping into a SQL Migration configuration.

Figure 3. Importing Hackolade Models into Studio 3T

”

This helps eliminate the need to repeatedly query and import from the SQL database as you fine-tune your migration. Instead, you can construct and document the migration within Hackolade, review with your team, and then implement with confidence.

Once the data is in MongoDB Atlas, support from Studio 3T continues. Studio 3T's Reschema tools allow you to restructure your schema without re-importing, working entirely on the MongoDB Atlas data. This can be particularly useful when blending data from existing document databases with freshly imported, formerly relational data.

The idea that migrations have to be team-breaking chores no longer holds. It is eminently possible, with tools such as Studio 3T's SQL Migration and Hackolade, to not only perform more complex migrations easily, but to be able to design them up front and put them into practice as often as is needed. With both tools working in integrated harmony, the future of migration has arrived.

← Previous

Sustainability in Action: Meet MongoDB's Green Team

Editor's note: Derek no longer works at MongoDB but his words remain true for the ongoing goals and initiatives of MongoDB's Green Team MongoDB affinity groups are employee-led resource groups that bring together employees with similar backgrounds, interests, or goals. They play an important role in our company and culture. Our affinity groups build community and connections, help us raise awareness of issues unique to their members’ experiences, and offer networking and professional development opportunities. Green Team is a MongoDB affinity group focused on sustainability and environmental responsibility. Hear from Derek Lowry , Senior Director of International Finance and the global Green Team lead based in Dublin, to learn about Green Team’s initiatives and goals, and the impact it is making on our company and our planet. Going green Green Team began in April 2020 after a group of people across departments decided it was time our company took greater action to positively impact the climate crisis. We believe businesses are one of the greatest levers for change in our society, and we want MongoDB to be part of that change. Green Team is committed to driving MongoDB to become a sustainable, social, and environmentally responsible company that is relentless in its pursuit of reducing its negative impact on the environment. How we're making an impact At the outset of Green Team, we determined there were two work streams to concentrate on: education on sustainability topics and collecting data on our carbon footprint as a company. We called these groups “Community” and “Collaboration,” respectively. Our Community group has held multiple webinars and workshops on sustainability for our employees over the past year covering topics such as how to live a more sustainable lifestyle, the problem of plastic pollution, and how to have a sustainable holiday season, as well as a “grow it yourself” workshop and a deep dive into recycling centers for our New York City employees. The team also compiled a plant-based cookbook that received a lot of positive feedback and engagement from employees across the globe. We continuously enforce the message of the “power of one” by highlighting that all our voices and actions together can have a positive impact on the climate crisis. We are not just relying on our employee base, but also focusing on taking action as a company. Our Collaboration team led a project to calculate our carbon footprint for 2019 and 2020 together with our climate partners, Watershed. Our carbon footprint baseline project is just the start of a climate strategy designed to help us significantly reduce our impact on the environment over the coming years, especially as we grow and scale as a company. We strive to incorporate sustainability into every aspect of the business, from product development to office selection, and we hope to make more announcements regarding this strategy over the coming months. Green Team's plans for the future Due to the commitment of more than 250 Green Team members globally — particularly the Green Champions who lead individual projects — we believe we are well positioned to execute on our projects and strategies over the coming year. We have had strong support from our executive sponsor, Michael Gordon, and we believe there is an appetite within the company to address the climate crisis and be part of the positive change. There also has been an increase in demand from both internal and external stakeholders for us to take action on the climate crisis, consistent with overall regulatory and market trends. Our future plans revolve around the two main work streams we initially created: educating our workforce and taking action as a company to reduce the overall impact we have on the environment. Hear from Green Team members Annie Black , Software Engineer, New York City "I joined Green Team because I thought there was more we, as a company, could be doing about sustainability. I realized I knew very little about day-to-day sustainability practices after joining Green Team, so most of my involvement now is with the Community side of the group, which focuses on educating employees." "I love seeing how people share their ideas and the things they’re passionate about in the #greenteam Slack channel, and I’ve really enjoyed learning and growing with everybody! One of my favorite recent projects was our Green Team Holiday Recipe Book. The focus was on meat reduction and an introduction to more plant-based diets, which was a new topic for me. I think it was a great way to help people feel connected to their colleagues during the remote holiday season." "The Collaboration side of Green Team is also doing a great job with our Watershed collaboration. The team at Watershed is assisting us with calculating our carbon footprint across the company and advising us on how to reduce it. This has such a big impact, and it’s great to see MongoDB investing in our planet!" "For Earth Week 2021, Green Team hosted webinars and activities as a way for MongoDB employees to learn more about sustainability and other topics related to environmental responsibility. I’m also very excited for the day we’re able to organize some in-person events! Nothing is planned yet, but I hope to help initiate plant-based tastings, park cleanups, and tree plantings." Shakti Sharma , Data Governance Associate, Gurugram "I joined Green Team because I wanted to help drive sustainability and green choices at an organizational level. We have some super energetic folks around the world at MongoDB who are passionate about bringing positive change. I am fairly new to MongoDB, so this group has also helped me build a network across the organization and constantly supports my personal growth." "Green Team encourages employees to make sustainable choices every day, whether it’s choosing eco-friendly items for gifts or wearing reusable masks. I believe organizations have some corporate responsibility to ensure their employees are safeguarding the environment as much as possible, and I’ve started focusing on how I can make a positive difference for the environment." Interested in pursuing a career at MongoDB and joining Green Team? We have several open roles on our teams across the globe and would love for you to transform your career with us!

August 20, 2021
Next →

Retrieval Augmented Generation for Claim Processing: Combining MongoDB Atlas Vector Search and Large Language Models

Following up on our previous blog, AI, Vectors, and the Future of Claims Processing: Why Insurance Needs to Understand The Power of Vector Databases , we’ll pick up the conversation right where we left it. We discussed extensively how Atlas Vector Search can benefit the claim process in insurance and briefly covered Retrieval Augmented Generation (RAG) and Large Language Models (LLMs). MongoDB.local NYC Join us in person on May 2, 2024 for our keynote address, announcements, and technical sessions to help you build and deploy mission-critical applications at scale. Use Code Web50 for 50% off your ticket! Learn More One of the biggest challenges for claim adjusters is pulling and aggregating information from disparate systems and diverse data formats. PDFs of policy guidelines might be stored in a content-sharing platform, customer information locked in a legacy CRM, and claim-related pictures and voice reports in yet another tool. All of this data is not just fragmented across siloed sources and hard to find but also in formats that have been historically nearly impossible to index with traditional methods. Over the years, insurance companies have accumulated terabytes of unstructured data in their data stores but have failed to capitalize on the possibility of accessing and leveraging it to uncover business insights, deliver better customer experiences, and streamline operations. Some of our customers even admit they’re not fully aware of all the data in their archives. There’s a tremendous opportunity to leverage this unstructured data to benefit the insurer and its customers. Our image search post covered part of the solution to these challenges, opening the door to working more easily with unstructured data. RAG takes it a step further, integrating Atlas Vector Search and LLMs, thus allowing insurers to go beyond the limitations of baseline foundational models, making them context-aware by feeding them proprietary data. Figure 1 shows how the interaction works in practice: through a chat prompt, we can ask questions to the system, and the LLM returns answers to the user and shows what references it used to retrieve the information contained in the response. Great! We’ve got a nice UI, but how can we build an RAG application? Let’s open the hood and see what’s in it! Figure 1: UI of the claim adjuster RAG-powered chatbot Architecture and flow Before we start building our application, we need to ensure that our data is easily accessible and in one secure place. Operational Data Layers (ODLs) are the recommended pattern for wrangling data to create single views. This post walks the reader through the process of modernizing insurance data models with Relational Migrator, helping insurers migrate off legacy systems to create ODLs. Once the data is organized in our MongoDB collections and ready to be consumed, we can start architecting our solution. Building upon the schema developed in the image search post , we augment our documents by adding a few fields that will allow adjusters to ask more complex questions about the data and solve harder business challenges, such as resolving a claim in a fraction of the time with increased accuracy. Figure 2 shows the resulting document with two highlighted fields, “claimDescription” and its vector representation, “claimDescriptionEmbedding” . We can now create a Vector Search index on this array, a key step to facilitate retrieving the information fed to the LLM. Figure 2: document schema of the claim collection, the highlighted fields are used to retrieve the data that will be passed as context to the LLM Having prepared our data, building the RAG interaction is straightforward; refer to this GitHub repository for the implementation details. Here, we’ll just discuss the high-level architecture and the data flow, as shown in Figure 3 below: The user enters the prompt, a question in natural language. The prompt is vectorized and sent to Atlas Vector Search; similar documents are retrieved. The prompt and the retrieved documents are passed to the LLM as context. The LLM produces an answer to the user (in natural language), considering the context and the prompt. Figure 3: RAG architecture and interaction flow It is important to note how the semantics of the question are preserved throughout the different steps. The reference to “adverse weather” related accidents in the prompt is captured and passed to Atlas Vector Search, which surfaces claim documents whose claim description relates to similar concepts (e.g., rain) without needing to mention them explicitly. Finally, the LLM consumes the relevant documents to produce a context-aware question referencing rain, hail, and fire, as we’d expect based on the user's initial question. So what? To sum it all up, what’s the benefit of combining Atlas Vector Search and LLMs in a Claim Processing RAG application? Speed and accuracy: Having the data centrally organized and ready to be consumed by LLMs, adjusters can find all the necessary information in a fraction of the time. Flexibility: LLMs can answer a wide spectrum of questions, meaning applications require less upfront system design. There is no need to build custom APIs for each piece of information you’re trying to retrieve; just ask the LLM to do it for you. Natural interaction: Applications can be interrogated in plain English without programming skills or system training. Data accessibility: Insurers can finally leverage and explore unstructured data that was previously hard to access. Not just claim processing The same data model and architecture can serve additional personas and use cases within the organization: Customer Service: Operators can quickly pull customer data and answer complex questions without navigating different systems. For example, “Summarize this customer's past interactions,” “What coverages does this customer have?” or “What coverages can I recommend to this customer?” Customer self-service: Simplify your members’ experience by enabling them to ask questions themselves. For example, “My apartment is flooded. Am I covered?” or “How long do windshield repairs take on average?” Underwriting: Underwriters can quickly aggregate and summarize information, providing quotes in a fraction of the time. For example, “Summarize this customer claim history.” “I Am renewing a customer policy. What are the customer's current coverages? Pull everything related to the policy entity/customer. I need to get baseline info. Find relevant underwriting guidelines.” If you would like to discover more about Converged AI and Application Data Stores with MongoDB, take a look at the following resources: RAG for claim processing GitHub repository From Relational Databases to AI: An Insurance Data Modernization Journey Modernize your insurance data models with MongoDB and Relational Migrator

April 18, 2024