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

 

ElasticSearch – Data Management – Day 1

Background

Wanted to cover the steps one can take to add data to ElasticSearch.

In this post, we will be using Postman to post JSON payloads.

 

Adding Data

Let us add data

Outline

  1. HTTP Verb
    • POST
  2. URL
    • Syntax :-
      http://[server]:[portNumber]/[index]/[type]/[id]
    • Sample :-
      http://localhost:9200/bible/biblekjv/5

 

Sample

Genesis – Chapter 1 – Verse 1

Image

Payload

  1. Method :- POST
  2. URL :- http://localhost:9200/bible/biblekjv/1

 

JSON/ (application/json)

 


{

	  "bookID"   : 1

	, "book"     :  "Genesis"

	, "chapterID"  : 1

	, "verseID"  : 1

	, "passage"  : "Genesis 1:1"

	, "verse"    : 
		"In the beginning God created the heavens and the earth"

}



 

Genesis – Chapter 1 – Verse 2

Image

Payload

  1. Method :- POST
  2. URL :-
    http://localhost:9200/bible/biblekjv/2

JSON/ (application/json)

 


{

	  "bookID"   : 1

	, "book"     :  "Genesis"

	, "chapterID"  : 1

	, "verseID"  : 2

	, "passage"  : "Genesis 1:2"

	, "verse"    : 
		"Now the earth was formless and empty, darkness was over the surface of the deep, and the Spirit of God was hovering over the waters."

}

Genesis – Chapter 1 – Verse 3

Image

 

Payload

  1. Method :- POST
  2. URL :-
    http://localhost:9200/bible/biblekjv/3
    

JSON/ (application/json)


{

	  "bookID"   : 1

	, "book"     :  "Genesis"

	, "chapterID"  : 1

	, "verseID"  : 3

	, "passage"  : "Genesis 1:3"

	, "verse"    : 
		"Now the earth was formless and empty, darkness was over the surface of the deep, and the Spirit of God was hovering over the waters."

}


Genesis – Chapter 1 – Verse 4

Image

 

Payload

  1. Method :- POST
  2. URL :-
    http://localhost:9200/bible/biblekjv/4
    

JSON/ (application/json)


{

	  "bookID"   : 1

	, "book"     :  "Genesis"

	, "chapterID"  : 1

	, "verseID"  : 4

	, "passage"  : "Genesis 1:4"

	, "verse"    : 
		"And God said, 'Let there be light', and there was light."

}


Genesis – Chapter 1 – Verse 5

Image

 

Payload

  1. Method :- POST
  2. URL :-
    http://localhost:9200/bible/biblekjv/5
    

JSON/ (application/json)

 


{

	  "bookID"   : 1

	, "book"     :  "Genesis"

	, "chapterID"  : 1

	, "verseID"  : 5

	, "passage"  : "Genesis 1:5"

	, "verse"    : 
		"God called the light 'day', and the darkness he called 'night'. And there was evening, and there was morning—the first day."

}


 

 

Display Data

Let us display the data we have added

List All

Outline

  1. HTTP Verb
    • GET
  2. URL
    • Syntax :-
      get://[server]:[portNumber]/[index]/[type]/_search
    • Sample :-
      http://localhost:9200/bible/biblekjv/search

 

Sample

Bible/biblekjv

 

List All – Ordered By

Outline

  1. HTTP Verb
    • GET
  2. URL
    • Syntax :-
      get://[server]:[portNumber]/[index]/[type]/_search
    • Sample :-
      http://localhost:9200/bible/biblekjv/_search

 

Sample

Bible/biblekjv

 

Payload

  1. Method :- POST
  2. URL :-
    http://localhost:9200/bible/biblekjv/_search
    

JSON/ (application/json)


{
    "sort" : 
	{
          "bookID": {"order": "asc"}
        , "chapterID": {"order": "asc"}
        , "verseID": {"order": "desc"}
	}
}

 

Output

 

Delete Record

Outline

  1. HTTP Verb
    • DEL
  2. URL
    • Syntax :-
      del://[server]:[portNumber]/[index]/[type]/[id]
      
    • Sample :-
      http://localhost:9200/bible/biblekjv/zbAI6GQBFV2VUI1OYvhg
      

 

Sample

Bible/biblekjv

 

Payload

  1. Method :- DEL
  2. URL :-
    http://localhost:9200/bible/biblekjv/zbAI6GQBFV2VUI1OYvhg
    

JSON/ (application/json)

 

Output

Postman, An API Development Environment ( ADE )

Background

Post Installation of ElasticSearch have a need to familiarize with a tool that allows me to post and receive JSON data.

Googling brought me to PostMan.

 

Artifacts

Postman is available here.

Native apps are available for the three major OS Client platforms ( Apple Mac, Microsoft Windows, and Linux).

.

 

 

Installation

Installation is straight forward.

 

Sign Up

Please sign-up for an Account.

Doing so will allow you to use and preserve your settings across devices.

 

Usage

 

YouTube

YouTube – Video

Payload

  1. Verb

JSON

 

Nobel

Payload

  1. Verb – GET

JSON