I need to migrate our MySql(Aurora) database to AWS OpenSearch database using DMS. In the official document, it is mentioned that DMS creates indexes for each RDBMS table. But what I want to do is creating only one index by joining tables. For instance; suppose we have one-to-many related Order and OrderLine tables. Is it possible to migrate my data to a document like this?
"hits": [
{
"_index": "orders",
"_id": "12345",
"_score": 1,
"_source": {
"orderId": "12345",
"orderDate": "2023-10-23T21:05:37.263",
"store": "Store-1",
"orderLines": [
{
"lineNumber": 1,
"productName": "Product-1",
"quantity": 1,
"price": 119.99
},
{
"lineNumber": 2,
"productName": "Product-2",
"quantity": 1,
"price": 39.99
}
]
}
}
After discussing with a AWS consultant, I ensured that DMS does not have direct solution for joining tables. Instead, creating a view including a json string by combining necessary fields and converting this string to json via a pipeline will solve the problem.