Joins in ElasticSearch

Elastic Stack Elasticsearch
1

Now i need to create parent-child relation in my documents.

There are 2 tables in MySQL.

Employee
    +------+-------------+--------+------+
    | id   | name        | salary | age  |
    +------+-------------+--------+------+
    |    0 | Dxgow Xkiuq |  40414 |   33 |
    |    1 | Kouni Pecre |  44814 |   41 |
    |    2 | Jtwfl Peiyw |  76611 |   37 |
    |    3 | Mnpxt Cpprc |  32538 |   31 |
    |    4 | Ukhey Uosqg |  90272 |   40 |
    |    5 | Eavcg Gqfoo |  43957 |   48 |
    |    6 | Bcllu Vhara |  42928 |   32 |
    |    7 | Bafna Qeymj |  91398 |   23 |
    |    8 | Qxwni Tgwdw |  60973 |   31 |
    |    9 | Jpcqc Qtyum |  54696 |   41 |
    +------+-------------+--------+------+


Age
    +--------------+
    | employee_age |
    +--------------+
    |           20 |
    |           21 |
    |           22 |
    |           23 |
    |           24 |
    |           25 |
    |           26 |
    |           27 |
    |           28 |
    |           29 |
    +--------------+

Now while importing this data to elasticsearch via logstash i need to create parent child relationship between these two tables as AGE being the parent and Employee being child.

Similarly to have parent-child relationship to the data which is already in the index.

These are the 2 situation where I am stuck.

2

Why do you need to do this?

3

For performing analysis.

4

From your example, all of the data on the "Age" table is already available on the "Employee" table.

Putting technical details aside, what is the business question you are trying to answer?

5

I have a large dataset of public welfare department in MySQL database.
Which has 7-8 tables and each tables has 40-50 columns.
So I need to perform joins in elasticsearch while migrating the data form RDMS to ES through logstash.
As I dont want to widen the column for redundancy and performance issue.

This was an example that i was trying to work on first than directly implementing on my main data.

6

Do joins at index time. Specifically if you want it to be fast as you asked for.
Redundancy is not an issue IMO.

Here is a blog post I wrote about connecting sql to nosql: http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/

7

I still don't get it. Normally a join uses a key to gain access to more values. Here there is only one value (age) and the key is the same as the value. Again, I would ask:

Putting technical details aside, what is the business question you are trying to answer?

An example question might be:

What is the average age of people with salaries > 100k?

etc.

8

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.