Which is the best way to index the data from relational database

[girishts]

Hi ,

Can you please let me know which is the best way to index the records in elastic search for my scenario.

My Scenario is :

1. Need to index around 40 million records from oracle table which has entries having one to many relationship records. And the uniqueness of the records is based on the composite key with 4 columns

2. After indexing , Search should support "full text search" on all the fields

3. Filters and sorting on selected fields needs to be supported.

After going through the official documentation i found couple of options , but want to know which approach would be most useful among below

1. For each record in table create a entry in the elastic index
2. Create a nested json object based on the composite key and then add this elastic index
3. Parent child Relationship mechanism and application side joins are not suitable for my scenario

Thanks
Girish T S

[dadoonet (David Pilato)]

My advice:

Think about the use case, not the current implementation.
Basically ask yourself: "What type of data my user will be searching for?".

As an example, let's say that users want to search for employees. Then index employees.

2nd question is "What kind of attributes do my users will use to search?". Let's say "company name", "company website" and "employee name". Then just store those values within each document, like:

PUT employees/_doc/david
{
  "name": "David XYZ",
  "company": {
    "name": "elastic",
    "website": "https://elastic.co"
  }
}

So don't try to reimplement relational model if it's not absolutely needed but just focus first on the use case.

For the record I shared most of my thoughts there: http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/

[girishts]

Thanks for the reply David.

Please let me know whether the nested type will be suitable.

My object definition looks like below..

 {
      "primarDetails": {
               "attr1": "",
               "attr2": "",
               "attr3": ""
       },
  "AdditionalDetails": {
    "AdditionalDetailsList": [
      {
        "countryId": "",
        "countryName": "",
        "objOneDetails": {
          "objOneDetailsList": [
            {
              "x": "",
              "y": "",
              "z": ""
            },
            {
              "x": "",
              "y": "",
              "z": ""
            }
          ]
        },
        "objTwoDetails": {
          "objTwoDetailsList": [
            {
              "a": "",
              "b": "",
              "c": "",
              "d": ""
            },
            {
              "a": "",
              "b": "",
              "c": "",
              "d": ""
            }
          ]
        }
      }
    ]
  }
}

[dadoonet (David Pilato)]

I can't tell with a real example. May be. May be not.