Generate interactive dashboards for relational data

Elastic Stack Kibana
1

I am having search engine data (Searches and Clicks), there are two tables(as table field in Elasticsearch) searches and clicks. I have a mapping between them (Same id in corresponding data), now I want to generate the various interactive dashboards using Kibana, some examples are

  1. Pie chart using range filter for clicked positions. (1-5,5-10 etc.)
  2. Data table containing Top queries for selected position buckets from both table.
  3. Data table for Top queries and their clicked urls with URL count.

All these graphs should be interactive, i.e. if I click on position bucket then search table data should be displayed as well. (Note there are no position field in search table, we have only same id mapping between these records.)

So my question is can we use Kibana for such use case?? Currently we are using tableau which supports sql joins, so we are able to achieve this functionality.

2

Elasticsearch does not support joins.
You need to look at parent/child or nesting, however Kibana does not currently support either of these.

3

ELK stack supports co-relation through timestamp.

4

ELK stack supports co-relation through timestamp.

Not sure what you mean here—could you elaborate?

5

Correlation maybe? If so that is not a relational join, it's just saying "I can see this event at time A and this event at time B so they may be related".

6

Yes that is what I meant. All data are related through timestamp.

7

Hey Guys, thank you for your inputs. Will try to denormalize the data and then feed it to ES so that Kibana can easily display it.

8

I Have done implementation using same use case in Kibana

Use JDBC River, give the sql query that will have join between those two tables and bring in all the columns in ES mapping field, in this way everything will start reflecting in kibana UI and create all your interactive reports

9

Hey @rkhapre, Sorry I guess I didn't write my question in proper way, here I don't have RDBMS tables, my data comes in log files I am parsing them using logstash and assign table field values. So in my case JDBC river won't help.. Right?? Please correct me if I am wrong because I am not much familiar with JDBC river.

10

as you wrote in your question, there are two tables.
Yes JDBC river is for RDBMS tables only...

Other way you can do is, load in Hadoop==>ES==> Kibana