0

Suppose I have a huge database (table a) about employees in a certain department which includes the employee name in addition to many other fields. Now in a different databse (or a different table, say table b) I have only two entries; the employee name and his ID. But this table (b) contains entries not only for one department but rather for the whole company. The raw format for both tables is text-files so I parse them with logstash into Elasticsearch and then I visualize the results with Kibana.

Now after I created several visualizations from table (a) in Kibana where the x-axis shows the employee name, I realize it would be nice if we have the employee IDs instead. Since I know I have this information in table (b), I search for someway to tell Kibana to translate the employee name in the graphs generated from table (a) to employee ID based on table (b). My questions are as follows:

1) Is there a way to do this directly in Kibana? If yes, can we do it if each table is saved in a separate index or do we have to save them both in the same idnex?

2) If this cannot be done directly in Kibana and has to be done when indexing the data, is there a way to still parse both text files separately with logstash?

I know Elasticsearch is a non-relational database and therefore is not designed for SQL-like functionalities (join). However there should be an equivalent or a workaround. This is just a simple use case but of course the generic question is how to correlate data from different sources. Otherwise Elasticsearch would be honestly not that powerful.

mshabeeb
  • 577
  • 2
  • 9
  • 25

3 Answers3

3

Similar questions have been asked and answered.

Basically the answer is that -- no you can't do joins in Kibana, you have to do them at indexing time. Space is cheap and elasticsearch handles duplicate data nicely, so just create any fields you need to display at indexing time.

Community
  • 1
  • 1
Alcanzar
  • 16,985
  • 6
  • 42
  • 59
  • Ok so the answer is number 2. However how can I do this in logstash? I have seen some examples in the documentation where one can do that based on parent-child relationships for example but for that one has to define manually which child belongs to which parent so this is not a real joining! And of course I cannot upload my data manually since we are talking about millions of records – mshabeeb Sep 28 '16 at 11:37
2

You might want to give Kibi a try.

Vadeg
  • 1,156
  • 10
  • 22
qxip
  • 21
  • 1
  • 1
1

The answer, unfortunately that I know of, is either write your own plug-in OR as we have had to do, downgrade to ES 2.4.1 and install Kibi
(https://siren.solutions/new-release-siren-join-2-4-1-compatible-with-es-2-4-1/)
and then install the kibi join plugin
(http://siren.solutions/relational-joins-for-elasticsearch-the-siren-join-plugin/)
This will allow you to get the joins you seek from a relational DB.

Mattcul
  • 242
  • 1
  • 8