By using AWS re:Post, you agree to the Terms of Use

How to migrate relational data from a relational SQL DB to NoSQL DB with DMS


We're testing DMS for syncing our main SQL (PostgreSQL) database into NoSQL (OpenSearch). I've read about tasks, rules & table mappings, but it's not clear what is the preferred way to sync relational data into a non-relational DB, specifically PostgreSQL into OpenSearch.

First of all, a few PostgreSQL tables will be migrated to the same OpenSearch index, by flattening the relations into the main document. This means that there should be probably be an order to the sync.

For example, let's say in PostgreSQL there is a "Users" table and "UserRoles" table (say 1 to many) where each user role has a user id. And in OpenSearch we have only a "Users" index where each user document can have an array of nested "role" objects (or just an array of strings).

Then I would assume that first the "Users" table should be migrated to the "Users" index, and only after that the "UserRoles" table should be migrated by adding a nested object in the "Users" index. How would that be done? Is there a way to control the order DMS syncs the tables? Also, how do I tell DMS that "UserRoles" should be migrated as a nested object and that it should first find the existing user by the user id?

2 Answers

There is a good blog that talks about migrating from a relational database to DocumentDB, which is a Document Datastore -

profile picture
answered 22 days ago

Unfortunately he blog just says how to setup migration between a relational DB & a NoSQL, but doesn't go into details about transforming SQL relations tables into a flattened NoSQL collection (or index, in case of OpenSearch). See my original question for the exact problems I'm facing.

answered 22 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions