ElasticSearch – Logstash – SQL Server

Background

Elastic’s Logstash is an ETL tool that allows us to “Request, Collect, Parse, and Send” Data.

Logstash

Definition

Elastic

Here is Elastic’s own definition

Link

definition_logstash_20180808_0125PM

Artifact

Logstash is available here.

The current version is 6.3.2 and it is a very recent release ( 2018-July-24th).

artifact_6_3_2

Download

For MS Windows, please choose the ZIP Version.

Extract

Please extract the compressed file.

JDBC Driver

Microsoft SQL Server

Download

Please download Microsoft SQL Server JDBC Driver from here.

Extract

Extract the downloaded file.

Usage

Files

Configuration File

Outline

  1. Input
    • jdbc
      • Connection String
        • Syntax :- jdbc:sqlserver://[host]:[port-number]
        • Sample :- jdbc:sqlserver://localhost:1433
      • jdbc_user :- database user
      • jdbc_password :- database user passsword
      • jdbc_driver_library :- full file name of JDBC Driver
      • jdbc_driver_class
        • com.microsoft.sqlserver.jdbc.SQLServerDriver
      • statement
        • select top 10 * from [StackOverflow2010].[dbo].[Users] tblU order by tblU.[Id] asc
  2. Output
    • elasticSearch
      • hosts
        • Syntax :- Elastic Search host and Port
        • Sample :- localhost:9200
      • Index
        • Sample :- stackoveflow2010user
      • Document Type
        • Sample :- _doc
      • document_id
        • Syntax :- %{[column-name]}
        • Sample :- %{[id]}

Configuration

input {
  jdbc {
    jdbc_connection_string => "jdbc:sqlserver://localhost:1433"
    # The user we wish to execute our statement as
    jdbc_user => "stackoverflow"
    jdbc_password => "hIy8jA2lNl"
    # The path to our downloaded jdbc driver
    jdbc_driver_library => "C:\Downloads\Microsoft\Java\jdbc\v6.0.8112.200\extract\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    # our query
    statement => "select * from [StackOverflow2010].[dbo].[Users] tblU order by tblU.[Id] asc"
    }
  }
output {
  stdout { codec => json_lines }
  elasticsearch {
  "hosts" => "localhost:9200"
  "index" => "stackoveflow2010user"
  "document_type" => "_doc"
  document_id => "%{[id]}"
  }
}

Command File


setlocal

REM 2018-08-08 11:16 AM Daniel Adeniji ( dadeniji)
REM SET JAVA_HOME to Version 1.8
set "JAVA_HOME=C:\Program Files\Java\jdk1.8.0_181"

set "_binfolder=C:\Downloads\Elastic\Logstash\v6.3.2\extract\bin"
set "_configuration=stackOverflow2010.User.conf"

call %_binfolder%\logstashImpl.bat -f %_configuration%

endlocal

Processing

Script


stackOverflow2010.User.cmd

Output

Output – 01

processing_20180808_0132PM

Output – 02

processing_20180808_0134PM

Output – 03

processing_20180808_0136PM

Output – 04

processing_20180802_011PM

 

Validation

Tools

Postman

Queries

Query – Microsoft

Objective

Find matches for Microsoft

Query


http://localhost:9200/stackoveflow2010user/_doc/_search?q=Microsoft

Design

query_Microsoft_20180808_0117PM

Output

Microsoft_Result_20180808_0118PM

 

References

  1. Elastic
    • Docs
      • Logstash
        • Running Logstash from the Command Line
          Link
    • Blog
      • Suyog Rao
        • Little Logstash Lessons: Handling Duplicates
          Link
  2. StackOverflow
    • Logstash to Keep Two Databases Synced – Cannot Access %{document_id}
      Link
  3. QBox
    • Vineeth Mohan
      • Migrating MySql Data Into Elasticsearch Using Logstash
        Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s