How to join two index

Elastic Stack Elasticsearch
1

Hi,

I want to join two indexes or merge them into one index.

One index is being collected from the apache log and
the other from mysql.

I have to join the data through common key in two indexes and
visualize it through Kibana.

Please tell me know how I can take it.

2

If the data in MySQL is not also in elasticsearch, I'd use logstash with:

  • Elasticsearch input
  • Jdbc filter
  • Elasticsearch output

If the data is all in elasticsearch, you coul use:

3

I like to add another option: transform.

A transform in a nutshell is a task that runs aggregation queries and persists the result in an index. In order to join 2 indices you need compatible mappings for grouping, e.g. a key that is named the same way and has the same type. Note, this can be achieved with scripts or runtime fields, however if you want to run in it continuously at scale, it is advised to use proper ordinary mappings.

For the group_by use terms on the common key.

For the aggregation part you can choose between all kinds of aggregations. Simple numeric fields can use e.g. min or max. For more complicated cases you have to use a script again, some examples are given in this post:

enrich vs. transform

If you wonder weather enrich or transform is the better choice:

Use enrich if 1 source is static or almost static, for example you want to expand an id to a name.

Use transform if all sources are dynamic and can arrive in different order. E.g. for distributed tracing in a larger system you can't say if the database lookup happens 1st or the access to the web server, or it might be interleaved. In such a case - and it sounds like yours - it is better to use transform.

LBNL I assume you want to join the data not just once. For this, have a look at the continuous mode in transform.

2 Likes
4

Many thanks to you,
I solved the problem through your solution.!

5

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