Migrating MySql to AWS OpenSearch using DMS

Question

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
            }
       ]
    }
}

Answer 1

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.

Answer 2

AWS Database Migration Service (DMS) is primarily designed in general to replicate data from source relational database systems to target relational database systems. When it comes to migrating data to NoSQL databases like Amazon OpenSearch, you might face challenges due to differences in data models.

To achieve the desired structure, you may need to perform some data transformation, ETL, before or after migrating the data to OpenSearch.

You can write some custom SQL query to join the tables and use Logstash JDBC input and finally push your data into Elasticsearch/Opensearch efficiently.