How to Merge Data from Two Tables in One Visualization?

Elastic Stack Kibana
1

Hi Kibana Jedi Masters,

Suppose I have a Kibana data chart Visualisation tracking the inventory of my fruit store:

| Product Code |  Qnty  |
=========================
| 100          |   15   |
| 400          |    8   |
| 200          |   20   |
| 500          |    6   |

Every kind of fruit is tracked by a hard-to-remember Product Code. On a separate data source - not within an Elasticsearch index - I have the Product Code-to-fruit mapping:

| Product Code | Name    |
==========================
| 100          | Apples  |
| 200          | Oranges |
| 300          | Bananas |

You see where this is going; I need to modify the original data chart to be this:

| Product Code | Name      | Qnty |
===================================
| 100          | Apples    | 15   |
| 400          | (unknown) | 8    |
| 200          | Bananas   | 20   |
| 500          | (unknown) | 6    |

I originally thought I could do this with a Scripted Field, where:

Name: FruitName
Language: painless
Type: string
Format: Default
Popularity: 0

Script:
IF doc['ProductCode'].value == 100 then "Apples"
ELSE IF doc['ProductCode'].value == 200 then "Oranges"
ELSE IF doc['ProductCode'].value == 300 then "Bananas"
ELSE "(unknown)"

The above generates syntax errors, obviously. I could figure out and fix them, but this whole approach seems pretty unwieldy to me. The Product Codes/Fruit Name list could have hundreds of items, possibly more. From a coding perspective, I don’t like the idea of running so many If/Else comparisons for every individual data record.

A much better solution would be the way they do it in SQL-like databases: The original inventory would be in one data chart, and the Product Codes/Fruit Name list would be in a separate table, somewhere in Elasticsearch or Kibana, I guess. When Kibana goes to generate the inventory chart, it would cross-reference both lists to populate the “Name” column.

Is there a tutorial that shows how to do this? Or can someone suggest how I can start? Thanks!

2

In general, Elasticsearch needs your data to be indexed in its searchable form. Joining queries is expensive. Like many document stores, each document should have as much information as you want to search.

If you are looking for advice on how to do a join on your data as it's being indexed, you may want to ask in the Elasticsearch or Logstash forums.

There are some alternatives here, but I don't think any of them are as full-featured as indexing this mapping:

  • Build a scripted field, like you were showing
  • You might be able to use a transform
  • In a future release you will have access to the enrich processor
3

Thanks Wylie,

Your explanation makes all the sense in the world. Appreciate this perspective.

I did the research, and I think the scripted fields or using transforms will be more trouble than they are worth. Plus they would be hard to manage once deployed into production. I'm going to post on the ES and LS forums, as you suggested.

Thank you! LOVE this forum!!!

1 Like
4

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