The following two tabs change content below.
Hi, I have written and developed this site to share my experience and ideas with other colleagues. I also started to prepare interview questions and answers for job seekers. I hope it will help you a lot.

I was implementing Elasticsearch in my project during this time period I have learned few things which I want to share with you. Like how to setup Elastic? How to make connection between ElasticSearch and Logstash? How to connect with Mysql?

Prerequisites below to start Elastic
Elasticsearch requires at least Java 7. Specifically as of this writing, it is recommended that you use the Oracle JDK version 1.8.0.*.

java -version
java -version

 

1. How to setup Elasticsearch? Below are the steps to setup Elastic Search.

a. Download Elasticsearch .Zip (if you are using Linux or any other operating system then please download appropriate format file) from Elasticsearch official website.

b. Once you download and extraction are done then please setup the elastic search path variable till /bin folder. Like below:

Elastic Path Variable
Elastic Path Variable

For linux user export PATH=$PATH:<<elasticsearch path>>

c. Once you done path setup then open command and type cmd below command to start elasticsearch service. For Linux user run ./elasticsearch.sh

start elastic service
start elastic service

Once service got successfully started below

service started
service started

c. Now we can test on URL to check if all are working fine. Open the browser and type below URL

http://localhost:9200/  and below will be the output on browser:

Elastic Output
Elastic Output

 

2. Now going to explain how to integrate Logstash with Elasticsearch?

a. Logstash is an open source tool for collecting, parsing, and storing logs for future use. Similar to Elasticsearch download the logstash .Zip from official elastic website and setup the path variable till /bin of logstash folder like blow:

logstash path variable
logstash path variable

b. Once path is updated then open another command prompt to validate the logstash setup type below command

logstash setup
logstash setup

 

3. Once we done with Logstash then we are ready to connect with MySql. Please make sure you have mysql-connector-java-bin.jar with you to make connection between logstash and mysql.

a. In my example I have put mysql-connector-java-bin.jar under bin folder and create filename.conf file with below configuration. I have created simple-out.conf under bin folder. Below is the structure of bin folder:

bin folder structure
bin folder structure

 

b Under simple-out.conf I am using below configuration:

input { jdbc { 
 jdbc_driver_library => "mysql-connector-java-5.1.43-bin.jar" 
 jdbc_driver_class => "com.mysql.jdbc.Driver" 
 jdbc_connection_string => "jdbc:mysql://<ip address>>:3306/iprice" 
 jdbc_validate_connection => true
 jdbc_user => "<<mysql User>>" 
 jdbc_password => "<<password>>" 
 statement => "select * from employee" 
}} 
output {
 elasticsearch {
 index => "employes"
 document_type => "employee"
 document_id => "%{empid}"
 hosts => "localhost:9200"
 }
}

Note: document_id will a ID filed name of your table 

c. Now in newly opened command prompt type below command to get the table data from Mysql

Note: Make sure before to run this command your elasticsearch service is running. If elasticsearch not running then you will get error message.

 

mysql configuration
mysql configuration

c. Now all this are done. its time to test the real feature of elastic.

d. Open this http://localhost:9200/employes/employee/_search?size=1&sort=empid  in browser you will see below output

query output
query output

e. In this URL you can pass query parameter as per your requirement like if you want to filter response use “filter_path” option

filter_path
filter_path

If you want to give any condition then use “q” parameter in URL like below:

query

 

4 Now if you want to write a Java wrapper for elasticsearch then its also very easy to do. Please create maven project and add following dependency in pom.xml

 <dependency>
 <groupId>org.elasticsearch.client</groupId>
 <artifactId>rest</artifactId>
 <version>5.5.1</version>
</dependency>

below is the java class for connection and to fire the query:

package com;

import java.util.HashMap;
import java.util.Map;

import org.apache.http.HttpHost;
import org.apache.http.util.EntityUtils;
import org.elasticsearch.client.Response;
import org.elasticsearch.client.RestClient;
import org.json.JSONArray;
import org.json.JSONObject;

public class ElasticMain {

public static void main(String[] args) {
 
 RestClient restClient = RestClient.builder(
 new HttpHost("localhost", 9200, "http"),
 new HttpHost("localhost", 9205, "http")).build();
 Map<String, String> paramMap = new HashMap<String, String>();
 paramMap.put("q", "ename:khushvinder");
 paramMap.put("pretty", "true");
 try{ 
 Response response = restClient.performRequest("GET", "/employes/employee/_search", paramMap);
 String s = (EntityUtils.toString(response.getEntity()));
 
 JSONObject jsonObject = new JSONObject(s);
 JSONObject object = jsonObject.getJSONObject("hits");
 JSONArray array = object.getJSONArray("hits");
 JSONObject jsonObject2 = new JSONObject();
 
 //Customized response so now in response on source attribute value will be display.
 //similar we can do more customization as per requirement.
 
 jsonObject2.put("source", array.getJSONObject(0).getJSONObject("_source"));
 System.out.println(jsonObject2.toString());
 }
 catch(Exception exception){
 exception.printStackTrace();
 }
}
}

 

Source code is available Elastic

798 total views, 7 views today

2 thoughts on “Elasticsearch with MySql using Logstash”

Leave a Reply

Your email address will not be published. Required fields are marked *