Integrating Elasticsearch with MS SQL, Logstash, and Kibana

Introduction

MS SQL Server holds the data in relational form or even multi-dimensional form (through SSAS) and proffers several out-of-the-box search features through Full Text Search (FTS).

However, the search function of modern-world applications has many complexities. The search specifications are hybrid and the queries demand full-scale searching over massive data sets. A better solution is required to perform such an advanced level of searches and that is where Elasticsearch grabs attention from technology experts.

Elasticsearch is a substantial REST HTTP service that enables scaling of operations even up to thousands of queries per second. Its features, such as Facets and Aggregation framework, assist in resolving many data analysis related issues as well. Hence, integration of Elasticsearch with any relational database can be proved a powerful value addition to the application.

How can we Integrate Elasticsearch with MS SQL?

The entire integration process of MS SQL and Elasticsearch along with Data-collection and log-parsing engine – Logstash, analytics and visualization platform – Kibana is described here in five simple steps.

Step 1: Environment Setup

Please find the directions to setup the integration environment with their purposes (where applicable):

• Download and install Java using the URL https://java.com/en/download - Set the Java path in Path Environment variable and set JAVA_HOME to "C:\Program Files\Java\jre1.8.0_151"

Purpose: Elasticsearch provides a Java API and it executes all operations in an asynchronous manner using the client object. The client object can cumulatively execute all operations in bulk. The Java API is used in order to execute all APIs in Elasticsearch.

• Download SqlServerJDBC Driver using the URL https://www.microsoft.com/en-us/download/details.aspx?id=55539

Purpose: Since Elasticsearch is developed in Java, we need to install JDBC driver in order to be connected with SQL Server

• Extract the driver to "C:\Program Files".

• Copy sqljdbc_auth.dll from "C:\Program Files\sqljdbc_6.0\enu\auth\x64" and paste to the location "C:\Program Files\Java\jre1.8.0_151\bin".

Purpose: This will authorize Java to access JDBC driver

• Add "C:\Program Files\sqljdbc_6.0\enu\auth\x64" to the Environment variable Path.

Purpose: Environment variables are set to enable processes such as:

• Allowing access to command line tools
• Enabling other tools to interact with SDKs more easily

Step 2: Elasticsearch Setup

Please find instructions to perform the setup for Elasticsearch:

• Add Elasticsearchjdbc to "C:\Program Files\Elastic\Elasticsearch\bin". The GitHub repo can be found here.

• Download and install Elasticsearch using the URL https://www.elastic.co/downloads/elasticsearch. Make sure you select MSI format and set Environment Path to "C:\Program Files\Elasticsearch\Elasticsearch-5.6.4\bin"

• Download and extract NSSM to Elasticsearch folder using the URL https://nssm.cc/download

Purpose: To install services, in our case we need Logstash to be installed as a service.

Step 3: Logstash Setup

Please find instructions to perform the setup for Logstash:

• Download Logstash in ZIP format and extract it to the C drive using the URL https://www.elastic.co/downloads/logstash

Purpose: Logstash enables the application to collect data from different systems. Moreover, it normalizes different schemas. It enables you to keep the data gathered from various systems into a common format. As a result:

• You can interact with data collected from different systems simultaneously. Additionally, you can compare data sets or even see how they influences one another

• Visualization tools such as Kibana and analytics engines such as Elasticsearch can make the best out of complex data

• Add jre7 and jre8 to "C:\Program Files\sqljdbc_6.0\enu" using the URL http://www.java2s.com/Code/Jar/s/Downloadsqljdbc420jar.htm

• Create a file having the name logstash.conf, add this file under the "logstash/bin" folder.

• Open Command prompt with Administrator rights, navigate to the "nssm\win64" folder and write nssm install Logstash

• Navigate to the Logstash folder and provide argument as below:

• Open logstash.conf using Notepad (or any other text editor) and add following configuration:

input {
    jdbc {
        # SqlServer jdbc connection string to our database, employeedb
        #  "jdbc:sqlserver://HostName\instanceName;database=DBName;user=UserName;password=Password" 
        jdbc_connection_string => "jdbc:sqlserver://localhost\SQLExpress;database=employeedb;user=sa;password=test@123"
        # The user we want to execute our statement as
        jdbc_user => nil
        # The path to our downloaded jdbc driver
        jdbc_driver_library => "C:/Program Files/sqljdbc_6.0/enu/jre8/sqljdbc42.jar"
        # The name of the driver class for SqlServer
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        # Query for testing purpose
        statement => "SELECT * from employee"
    }
}
output {
    stdout { codec => json_lines }
}

• Navigate to the Logstash bin folder from command prompt and provide command "logstash -f logstash.conf". It should return Query result:

Step 4: Kibana Setup

Please find instructions to create setup for Kibana:

• Download Kibana and Unzip to C: drive.

• Go to the folder of Kibana "C:\kibana-5.6.4-windows-x86\config", remove '#' icon from kibana.yml for uncommenting the properties of Kibana

• Install Kibana using NSSM

• Navigate to Kibana folder as mentioned below:

To check whether Kibana installed or not, explore the local host URL: http://localhost:5601/app/kibana#/management/kibana/index?_g=( )

Step 5: Connecting Elasticsearch with the Application

Install the Nest plugin to utilize Elasticsearch in Visual Studio. To enable database operations through Elasticsearch it is also required to attach ElasticsearchCRUD plugin using the URL https://www.nuget.org/packages/ElasticsearchCRUD/. Download the DLL files and provide a reference.

Conclusion

In this article, I have described the systematic process of integrating Elastic Stack (Elasticsearch, Logstash, and Kibana) with MS SQL database to make the best out of data sets. I have also tried to share the purpose of each action wherever it is applicable.

These steps are not necessarily limited to MS SQL databases, however. As long as you download the relevant drivers, you can integrate Elasticsearch with any other database by following the same procedure.

Due to its excessive data management capabilities, Elasticsearch has potential to deal with the modern era of data eruption challenges, I believe. No wonder, the high-end clientele including Netflix, Uber, Dell, BBC, LinkedIn and eBay, is leveraging it. However, it is still in a young phase and much better is yet to come.

About the Author

Mayank Gaur is a Sr. Software Engineer at Azilen technologies and has about 5 years of experience along with distinctive expertise over .Net and SQL Server. Mayank holds adequate skills for converting ideas into reality along with a solution oriented attitude.