amazon-rdsdatabase-migrationopensearchaws-dms

Migrating MySql to AWS OpenSearch using DMS


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

Solution

  • 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.