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?