Join on the same index possible?

[german23]

Hi guys,

is it possible to make a join on the same index with the same type? We are using daily indices in our ELK-stack with a single document-type (since no more than 1 is supported in ES6.X) and would like to make a join so we get an output between 2 different events in the same index.

E.g.

SELECT c.to , h.hash FROM logstash-2019.12.03/eventtype c JOIN logstash-2019.12.03/eventtype h ON c.id = h.id

Howeve we only get "null" as results for the 2 colums. Is this not possible in general or we got an error in our syntax?

Thank you very much.

[dai-chen]

@german23 I suppose it should work in ES-SQL. Not sure what happened in your case. Anyway you can try out my project https://github.com/opendistro-for-elasticsearch/sql which I believe has the support.

[german23]

Hi @dai-chen , thanks for pointing out to your project, since we are not 100% sure after reading the documentation: Can we install that as a normal elasticsearch plugin (by building the module from source as described) or is this a add-one to opendistro and we need to have opendistro running on our elasticsearch-cluster? Also do you have a web front like the one from NLPchina? Thank in advance.

[dai-chen]

Hi @german23 Yes, you can either install our plugin just as other normal plugin or build the zip file from source and unzip it to es/plugins folder directly. Here is the instructions: https://opendistro.github.io/for-elasticsearch-docs/docs/install/plugins/#sql. Our README needs to clarify this :) Please open issue to us if any question.

For the web front, we don't have it for now. But a Kibana GUI is almost done and will be coming soon. Please stay tuned.