Correlate two diffent types of indexes in Kibana

Elastic Stack Kibana
1

Hi,

I have two sets of data in our DB, say Scores and Players e.g.

  • Player index - will have a player id, name, type of player, age.
  • Score index - will have a score id, play date, played at (location), score and player id.

Both indexes only have 1 common field 'player id'. So I would like to have a dashboard that displays interesting Score information and likewise displays interesting Player information.

E.g.
Given a date range of 2017-01-01 to 2018-01-01,

I would like to create a Table visualization to display Max, Avg and Min scores (from Score index) as well as the stats on the player types and avg age on a different Table visualization.

I understand that if we further de-normalize the data by joining them into one, we can easily create the buckets for aggregation. However, my concern is that if we need to add new field or information that does not exists yet, we would have to re-index and it becomes a concern especially if the size of the data set is huge (over 20 million).

Appreciate if you could share similar experience and what approach did you take.

Thanks,
Kenneth

2

Hi Kenneth,

This should be doable as long as you don't need to pull data from the one type of document into another. It's even doable with both types of documents in one index. You can check the metricbeat indices which will get multiple types of data( CPU, Storage, Memory, etc) and store it all in the same index and then do viz on all of them.

3

Hi Marius,

We kind of needed to pull or relate score with player index.

For example I have the following data below

scores-index
{
“score_id”: 1,
“player_id”: 12345,
“score”: 5,
“play_date”: “2018-01-14”
},
{
“score_id”: 2,
“player_id”: 67890,
“score”: 5,
“play_date”: “2018-01-14”
},
{
“score_id”: 3,
“player_id”: 67890,
“score”: 3,
“play_date”: “2018-01-25”
}

players-index
{
“player_id”: 12345,
“category”: “Junior”
},
{
“player_id”: 67890,
“category”: “Senior”
}

I want to do a viz to show avg score and the count of players in a category from 01-Jan-18 to 31-Jan-18.

(from score-index)
image

(from member-index)
image

Is it possible to do this?

Thanks,
Kenneth