Index data from MSSQL server to Elasticsearch using Logstash as nested documents

[Mohan_vel]

Hi All i am new to elasticsearch i am trying to index data from MSSQL server to elasticsearch using logstash as nested documents i am having some issues while creating a topic so i have created the issue in stack overflow and shared the link below.

link posted in stackoverflow

Could any one help me to sort out the issue?

[inhinyera16 (Archelle Pagapulan)]

I believe you can do this with jdbc_streaming filter

you can check it out
https://www.elastic.co/guide/en/logstash/current/plugins-filters-jdbc_streaming.html

Ps: Please mark as "Solution" if you think this helps

[Mohan_vel]

Hi thanks for the reply Archelle. I gone through the given link since i was new to this logstash env i was unable to get through it sample code attached below. You have attached a screen shot of the result which is needed. If you have any sample code kindly post it it will be really helpful parallely i will look into the link and try to create a solution. Thanks

Note : i have pasted the jdbc driver with in logstash-7.2.0\logstash-core\lib\jars that is why my
Jdbc_driver_libirary is empty

input {
jdbc {
jdbc_driver_library => ""
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://DESKTOP-VHDBS6E\SQLEXPRESS:1433;databaseName=sa;"
jdbc_user => "sa"
jdbc_password => "sa"
statement => "Select id,name from department"
}
}

filter{
jdbc_streaming {
jdbc_driver_library => ""
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://DESKTOP-VHDBS6E\SQLEXPRESS:1433;databaseName=sa;"
jdbc_user => "sa"
jdbc_password => "sa"
statement => "select Id as empid,emp_Name as empname from Employee where dept_Id = :code"

parameters => { "code" => "id"}
target => "employee_details"

}
}
output {
stdout { codec => rubydebug }
}

[inhinyera16 (Archelle Pagapulan)]

There you have it, just a few points though.

1. Use deptid as alias of id field in input block.
2. In your filter block, replace :code with :deptid, then under parameter,

parameters => { "deptid => "deptid”}
target => "employee_details"

[Mohan_vel]

Thanks Archelle. I have tried the above code but now i am getting error related to driver so the error is quite different so i have opened another topic i have pasted the link below for your reference

invalid setting jdbc_streaming

Once the issue get resolved i will mark it as solution. As of now both topic is open. Hopefully it will be resolved. Kindly help me to close this. Thanks in advance.

[inhinyera16 (Archelle Pagapulan)]

Hi @Mohan_vel

Here's my config

input {
	jdbc {
                jdbc_driver_library => "/tmpBIS/sqljdbc4.jar"
                jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
                jdbc_connection_string => "jdbc:sqlserver://localhost:1433;database=db;user=arc;password=mipw"
                jdbc_user => "arc"
                statement => "select id as deptid, Name as deptname from department"
        }
}
filter {

	jdbc_streaming {
                jdbc_driver_library => "/tmpBIS/sqljdbc4.jar"
                jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
                jdbc_connection_string => "jdbc:sqlserver://localhost:1433;database=db;user=arc;password=mipw"
                jdbc_user => "arc"
                statement => "SELECT ID as emp_id, emp_name FROM Employee where dept_id = :deptid"
		parameters => { "deptid" => "deptid"}
                target => "employee_details"
  	}

	mutate {
		remove_field => [ "@version","@timestamp" ]
	}

}
output {
	stdout {codec => rubydebug}

}

[Mohan_vel]

Hi @inhinyera16 ,

Driver issue not resolved so i go upgraded my logstash from 7.2.0 to 7.5.2 now the above code is working fine. i have given my config below for your reference.

input {
jdbc {
jdbc_driver_library => "D:/Users/mmurugesan/Desktop/driver/mssql-jdbc-7.4.1.jre12-shaded.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxx;"
jdbc_user => "xxxx"
jdbc_password => "xxx"
statement => "Select Policyholdername,Age,Policynumber,Dob,Client_Address from policy"
}
}
filter{
jdbc_streaming {
jdbc_driver_library => "D:/Users/mmurugesan/Desktop/driver/mssql-jdbc-7.4.1.jre12-shaded.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxxx;"
jdbc_user => "xxxx"
jdbc_password => "xxxx"
statement => "select claimnumber,claimtype from claim where policynumber = :policynumber"
parameters => {"policynumber" => "policynumber"}
target => "claim_details"
}
}
output {
stdout { codec => rubydebug }
}

Thanks a lot. i will notify you if i raise any other topic related to my work thanks a lot.

[Mohan_vel]

Hi @inhinyera16 ,

I have created another topic Fyi

remove nested documents based on column flag

Kindly look into this. Thank you.

As discussed i have marked solution for this topic.