Can we use SQL Join in elastic query?

Elastic Stack Elasticsearch
21

is it possible to read two input
if you find user.input1 = user.input2 then put record in different index.

logstash allows multiple input section.. I have use that.

I have four jdbc connection going to one database.
and depending on value of field1 I do different processing.

22

As a workaround, you could create a view on the SQL side that performs the join and then pull that view with logstash. The view is a virtual table, no added storage.

23

How to create view on SQL side and then how to pull that from logstash ?
Can you please explain via example ?

24

on logstash config file

input {
   jdbc {
       jdbc_validate_connection => true
       jdbc_connection_string => "jdbc:mysql://server1:3306/db_name"
        jdbc_user => "xxx"
        jdbc_password => "xxx"
        jdbc_driver_library => "/root/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        ### get query with joining two table. you can do what ever your select suppose to do. contact your database guy for it if you not sure.
        statement => "select p.username, q.host, q.port from table1 p, table2 q where p.name=q.name"
  }
}
25

what should be db_name ?

jdbc_connection_string => "jdbc:mysql://server1:3306/db_name"

26

what ever mysql/sql database name ?

27

My ELK stack is on 7.0 version . So for current join requirement , do i need to install SQL JDBC connector or it's pre installed ?
what else i need to install to do join of two indexes ?

28

Hi Sejal,
you are going way out of original question.

  1. currently you can't join two indice to get a result using elastic sql.

to solve that everyone said denormalize the data. that means combine two indice to create new indice because your have common field called username

for that you have to pull the data from it's source combining it.

Then I assume you have SQL server where these data are residing. But looks like I am wrong there.

So ignore all that what I said about jdbc.

here is what you have to do in nut-shell

read index1
read index2

filter {
if index1.username = index2.username ; merge data.
}

I do not know how to achieve that. some testing needed.

https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/6

29

I hope someone else with better knowledge can help you.

30

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