Migration from mysql to elasticsearch

[Omarbek_Dinassil]

Hello!

Right now I have three tables A, B and C in mysql database:

Table A   Table B   Table C
id            id          id       
              FK_A                  
              FK_C

I should write a service which returns results from table A giving to it ids from table C:

request:{
ids_from_tableC: []
}
response:{
ids_from_tableA:[]
}

So I have some questions:

1. When I do migration, should I migrate joined query for one indice or two different queries for two indices?
2. If two indices, can I join indices and get desirable result?

It would be cool, if you could help me

I attempted to write joined query, but there are only one data (1 row), but in MySQL there are more than 10k

[carly.richmond]

Hi @Omarbek_Dinassil,

Welcome to the community! I would recommend trying to denormalize your data into a single or the minimum number of indices. So in this case I would try and have the attributes from A, B and C in a single index if you can.

Elasticsearch works differently from a traditional database in the sense that joins over indices are not possible. The alternative if to use enrichment policies or ILM to enrich an index with the fields you need from another.

Hope that helps!

[Omarbek_Dinassil]

@carly.richmond Hello!

Thank you!

I've written joins and group concat to denormalize tables. Now my ES looks like this:

"hits" : [
      {
        "_index" : "books",
        "_type" : "_doc",
        "_id" : "5838",
        "_score" : 1.0,
        "_source" : {
          "foreign_ids" : "1,2,3",
          "id" : 5838
        }
      },

Can I write query to filter by foreign_ids, for example:

request:[1,2,3]
response:[5838]

request:[1,2]
response:[5838]

request:[1,4]
response:[]

Because, book with id 5838 doesn't contain foreign_id 4.

Should I convert my string "1,2,3" to array [1,2,3]? If yes, how?

[carly.richmond]

Hi @Omarbek_Dinassil,

Pushing multiple values for a field in the document is possible, but you need to consider whether you want to query each element independently, which from your example it looks like you do. I would recommend having a look at the arrays documentation which shows how to push documents with this type to a new index. But it would look a bit like this:

POST array-test/_doc
{
  "foreign_ids" : [1,2,3],
  "id" : 5838
}

Once you have them in an array format, a simple match query will allow you to find the documents:

GET array-test/_search
{
  "_source": ["id"], 
  "query": {
    "match": {
      "foreign_ids": 1
    }
  }
}

Hope that helps!