ElasticSearch – Type – Mapping

Background

It is very easy to create new types, tables in traditional tables, and corresponding columns in ElasticSearch.

ElasticSearch

If we do not create mappings before hand, ElasticSearch will create the type, columns and column types for us.

Schema-Less

The ElasticSearch exercises we have tackled thus far relies on the ability of ElasticSeach to create columns upon usage; the first time a record is added.

biblekjv

Here is the schema created for our biblekjv example.

Image

biblekjv_mapping_20180806_1024PM_01

Textual

  1. Columns
      • book
        • type is text
        • And, type is keyword
    • bookID
      • type is long
    • chapter
      • type is long
    • chapterID
      • type is long
    • passage
      • type is text
      • And, type is keyword

Explanation

  1. Numeric entries
    • Are codified as long
    • The largest available datatype for numbers
  2. Strings
    • Represented as both text and keyword
    • Datatype
      • Text
        • Tokenizable
      • Keyword
        • Exact Matches

Schema

Definition

Definition – ElasticSearch

Link

Mapping is the process of defining how a document, and the fields it contains, are stored and indexed. For instance, use mappings to define:

  • which string fields should be treated as full text fields.
  • which fields contain numbers, dates, or geolocations.
  • whether the values of all fields in the document should be indexed into the catch-all _all field.
  • the format of date values.
  • custom rules to control the mapping for dynamically added fields.

Sample – event

Goal

In this post we will create a schema to underine an event.

Sample

Sample – Periscope

Single Event Tables and Common Analysis Queries
Link

event_periscope_20180807_0238PM

Code

Create Type
Outline
  1. We will have two files, the JSON file and the command file
  2. The JSON file
    • Mapping
      • Top – mappings
      • Type :- _doc
      • Properties
        • Property
          • userID :- long
          • loginFirst :- date
          • loginLast :- date
          • firstSpendAt :- date
          • totalSpend :- double
          • dob :- date
          • gender :- keyword ( m/f/etc)  {Gender Inclusive}
          • platform :- keyword ( ios/android)
json

{
    "mappings":
    {
        "_doc":
        {
            "properties":
            {

                "userID":
                {
                    "type": "long"
                }

                , "loginFirst":
                {
                    "type": "date"
                }               

                , "loginLast":
                {
                    "type": "date"
                }                               

                , "firstSpendAt":
                {
                    "type": "date"
                }                                               

                , "totalSpend":
                {
                    "type": "double"
                }                   

                , "dob":
                {
                    "type": "date"
                }   

                , "gender":
                {
                    "type": "keyword"
                }   

                , "platform":
                {
                    "type": "keyword"
                }
            } // "properties"

        } // mapping name

    } //mappings

}  

Batch file

setlocal

set "_binfolder=C:\Downloads\Curl\v7.61.0\Windows\extract\curl-7.61.0-win64-mingw\bin"
set "_binapp=curl.exe"
set "_bin=%_binfolder%\%_binapp%"

set "_url=http://localhost:9200"

set "_esindex=/event"
set "_estype="

set "_espath=%_esindex%%_estype%"

set "_contentType=Content-Type: application/json

Rem Index - Delete

%_bin% -XDELETE -i %_url%%_espath% 

Rem Index / Mapping - Create

set "_jsonfile=jsonfile\event_mapping.json"

set "_jsonfileFQDN=%cd%\%_jsonfile%"

%_bin% -i  -XPUT %_url%%_espath%  -H "%_contentType%" --data-binary @%_jsonfileFQDN%

endlocal

Output
Output – Delete

type_create_delete_20180807_0253PM.png

Output – Create

typeMappingCreate_20180807_0253PM

Display Type
Outline
  1. We will have a lone file, the command file
  2. Command file
  3. Payload
Batch file

setlocal

set "_binfolder=C:\Downloads\Curl\v7.61.0\Windows\extract\curl-7.61.0-win64-mingw\bin"
set "_binapp=curl.exe"
set "_bin=%_binfolder%\%_binapp%"

set "_url=http://localhost:9200"

set "_esindex=/event"
set "_estype="
set "_esoperator=/_mapping"

Rem Index / Mapping - List

set "_espath=%_esindex%%_estype%%_esoperator%?pretty"

%_bin% -i  %_url%%_espath% -H "%_contentType%" 

Output
Output – Browse

typeMappingBrowse_20180807_0320PM

Load Data
Outline
    1. There are two files, the JSON file and the command file
    2. The JSON file
      • Two lines for each record
        • The first record will contain the _id
        • The second record sill contain the actual data
          • Property/Value pair
    3. Command file
      • Syntax
        • curl.exe -i -H “Content-Type: application/x-ndjsonhttp://%5Bserver}:{port}/{index}/{type}/_bulk?pretty –data-binary @[filename]
      • Sample
JSON

{"index":{"_id":1}}
{"userID":137314,"loginFirst":"2015-04-01","loginLast":"2015-04-05","firstSpendAt":"2015-04-03","totalSpend":50,"dob":"1994-01-01","gender":"f","platform":"ios"}
{"index":{"_id":2}}
{"userID":137312,"loginFirst":"2015-04-01","loginLast":"2015-04-02","firstSpendAt":"2015-04-02","totalSpend":2500,"dob":"1987-01-01","gender":"m","platform":"android"}
{"index":{"_id":3}}
{"userID":137310,"loginFirst":"2015-04-01","loginLast":"2015-04-01","dob":"2000-01-01","gender":"m","platform":"ios"}
{"index":{"_id":4}}
{"userID":137311,"loginFirst":"2015-04-01","loginLast":"2015-04-02","dob":"1995-01-01","gender":"f","platform":"ios"}
{"index":{"_id":5}}
{"userID":137313,"loginFirst":"2015-04-01","loginLast":"2015-04-04","dob":"1976-01-01","gender":"m","platform":"andriod"}

Command

setlocal

set "_binfolder=C:\Downloads\Curl\v7.61.0\Windows\extract\curl-7.61.0-win64-mingw\bin"
set "_binapp=curl.exe"
set "_bin=%_binfolder%\%_binapp%"

set "_url=http://localhost:9200"

set "_esindex=event"
set "_estype=_doc"
set "_esoperation=_bulk"

set "_espath=/%_esindex%/%_estype%/%_esoperation%"

set "_jsonfile=jsonldfile\event.json"
set "_jsonfileFQDN=%cd%\%_jsonfile%"

set "_contentType=Content-Type: application/x-ndjson"

%_bin% -i -H "%_contentType%"  %_url%%_espath%?pretty --data-binary @%_jsonfileFQDN%

endlocal
Browse Data
Outline
  1. There are two files the json file and command file
  2. Payload
    • Syntax :-
      • curl.exe -i -H “Content-Type: application/json” http://{server}:{port}/{type}/_search?pretty –data-binary @{filename}
    • Sample :-
JSON

{
   "sort" :
  {
      "_id": {"order": "asc"}

  }
}

Command

setlocal

set "_binfolder=C:\Downloads\Curl\v7.61.0\Windows\extract\curl-7.61.0-win64-mingw\bin"
set "_binapp=curl.exe"
set "_bin=%_binfolder%\%_binapp%"

set "_url=http://localhost:9200"

set "_esindex=event"
set "_estype=_doc"
set "_esoperation=_search"
set "_espath=/%_esindex%/%_estype%/%_esoperation%"

set "_jsonfile=jsonfile\event_data_browse.json"
set "_jsonfileFQDN=%cd%\%_jsonfile%"

set "_contentType=Content-Type: application/json"

%_bin% -i -H "%_contentType%"  %_url%%_espath%?pretty --data-binary @%_jsonfileFQDN%

endlocal
Output

dataBrowse_20180807_0357PM

Source Control

GitHub

DanielAdeniji/elasticSearchMappingEvent
Link

References

  1. Elastic
    • Docs
      • Mapping
        • Definition
        • Field Datatypes
          • Elasticsearch Reference [6.3] » Mapping » Field datatypes
            Link
          • Elasticsearch Reference [6.3] » Mapping » Field datatypes »Data datatypes
            Link
          • Elasticsearch Reference [6.3] » Mapping » Field datatypes » Numeric datatypes
            Link
          • Elasticsearch Reference [6.3] » Mapping » Field datatypes » Boolean datatype
            Link
        • Indices Mapping
          • Get Mapping
            • Indices Get Mapping
              Link
          • Put Mapping
            • Elasticsearch Reference [5.0] » Indices APIs » Put Mapping
              Link
      • Indices
        • Delete Index
          • Elasticsearch Reference [2.3] » Indices APIs » Delete Index
          • Elasticsearch Reference [6.3] » Indices APIs » Delete Index
      • Reference
      • Elastic for Apache Hadoop
        • Elasticsearch for Apache Hadoop [6.3] » Elasticsearch for Apache Hadoop » Mapping and Types
          « Apache Storm support
          Link
    • Github
      • elastic/elasticsearch
        • INDEX DELETE with wildcard doesn’t delete all matching indexes #7295
          Link
        • v5.3.1 Entire Bulk request is rejected if containing a single invalid index request #24319
          Link
    • Discuss
      • Index a new document/35281
        Link
  2. Periscope
    • Single Event Tables and Common Analysis Queries
      Link
  3. GitHubGist
    • kuznero
      • kuznero/elasticsearch-curl.md
        Link
  4. StackOverflow
    • curl command to close all indices at once in elastic search
      Link
    • Reindex ElasticSearch index returns “Incorrect HTTP method for uri [/_reindex] and method [GET], allowed: [POST]”
      Link
    • Elasticsearch : Root mapping definition has unsupported parameters index : not_analyzed
      Link
  5. logz.io
    • Daniel Berman
      • ElasticSearch Mapping
        Link

 

ElasticSearch – cURL – Batch Operations – Day 1

Background

In earlier posts we exclusively used Postman to perform Data Entry against Elastic Search.

Batch

Preface

In this post we will discuss using cURL, a command line client tool.

What is cURL?

Wikipedia

Link

cURL is a computer software project providing a library and command-line tool for transferring data using various protocols. The cURL project produces two products, libcurl and cURL. It was first released in 1997. The name stands for “Client URL”.

Get cURL

Please get cURL from here.

Please choose the binary specific to your OS, download and extract it.

No need to install, just reference the folder you extracted it into.

 

Use Scenario

Remove Data

Remove Data – Based on Query

Overview

  1. Command File
    • ElasticSearch
      • URL
        • Index :- bible
        • Type :- biblekjv
        • Method :- _delete_by_query
    • cURL
      • Content-Type: application/json
  2. JSON File
    • Query
      • Match on
        • book=Revelation

Command File


REM Retrieving a document
rem https://www.elastic.co/guide/en/elasticsearch/guide/current/get-doc.html

setlocal

set "_binfolder=C:\Downloads\Curl\v7.61.0\Windows\extract\curl-7.61.0-win64-mingw\bin"
set "_binapp=curl.exe"
set "_bin=%_binfolder%\%_binapp%"

REM ElasticSearch - URL
set "_url=http://localhost:9200"

REM ElasticSearch - Index,Type,Operation
set "_esindex=bible"
set "_estype=biblekjv"
set "_esoperation=_delete_by_query"
set "_espath=/%_esindex%/%_estype%/%_esoperation%"

REM ElasticSearch - Jsonfile
set "_jsonfile=jsonfile\deleteByQuery_book_Revelation.json"
set "_jsonfileFQDN=%cd%\%_jsonfile%"

REM HTTP - ContentType
set "_contentType=Content-Type: application/json"

%_bin% -i -H "%_contentType%"  %_url%%_espath%?pretty --data-binary @%_jsonfileFQDN%

endlocal 

JSON File

{

  "query":
  {
    "bool":
    {
      "must":
      [
        {
            "match":
            {
                "book":"Revelation"
            }
        }
      ]
    }
  }

}

Output

deleteUsingQuery_20180803_0330PM

Remove Data – Based on Bulk

Overview

  1. Command File
    • ElasticSearch
      • URL
        • Index :- bible
        • Type :- biblekjv
        • Method :- _bulk
    • cURL
      • Content-Type: application/x-ndjson
  2. JSON File
    • Content
      • {“delete”:{“_id”:1000}}
        • Remove record whose ID is 1000

Command File


setlocal

set "_binfolder=C:\Downloads\Curl\v7.61.0\Windows\extract\curl-7.61.0-win64-mingw\bin"
set "_binapp=curl.exe"
set "_bin=%_binfolder%\%_binapp%"

set "_url=http://localhost:9200"

set "_esindex=bible"
set "_estype=biblekjv"
set "_esoperation=_bulk"
set "_espath=/%_esindex%/%_estype%/%_esoperation%"

set "_jsonfile=jsonldfile\deleteBatch.json"
set "_jsonfileFQDN=%cd%\%_jsonfile%"
set "_contentType=Content-Type: application/x-ndjson"

%_bin% -i -H "%_contentType%" %_url%%_espath%?pretty --data-binary @%_jsonfileFQDN%

endlocal

Json file ( line delimited)


{"delete":{"_id":1000}}

Output

deleteUsingBatch_20180803_0351PM.png

Add Data

Add Batch Data based on JSON file (line delimited)

Overview

  1. Command File
    • ElasticSearch
      • URL
        • Index :- bible
        • Type :- biblekjv
        • Method :- _bulk
    • cURL
      • Content-Type: application/x-ndjson
  2. JSON File
    • Content
      • {“index”:{“_id”:1001}}
        {“bookID”:66,”book”:”Revelation”,”chapterID”:1,”verseID”:1,”passage”:”Revelation 1:1″,”verse”:”The Revelation of Jesus Christ, which God gave unto him, to shew unto his servants things which must shortly come to pass; and he sent and signified it by his angel unto his servant John:”}

        • Record – 1
          • Index ID
        • Record – 2
          • Column Value pair
          • Comma delimited
        • Record – N
          • Empty line
          • Make sure not ended with LF nor CR/LF
Command File

setlocal

set "_binfolder=C:\Downloads\Curl\v7.61.0\Windows\extract\curl-7.61.0-win64-mingw\bin"
set "_binapp=curl.exe"
set "_bin=%_binfolder%\%_binapp%"

set "_url=http://localhost:9200"

set "_esindex=bible"
set "_estype=biblekjv"
set "_esoperation=_bulk"
set "_espath=/%_esindex%/%_estype%/%_esoperation%"

set "_jsonfile=jsonldfile\revelation.json"
set "_jsonfileFQDN=%cd%\%_jsonfile%"

set "_contentType=Content-Type: application/x-ndjson"

%_bin% -i -H "%_contentType%"  %_url%%_espath%?pretty --data-binary @%_jsonfileFQDN%

endlocal 

XML File

{"index":{"_id":1001}}
{"bookID":66,"book":"Revelation","chapterID":1,"verseID":1,"passage":"Revelation 1:1","verse":"The Revelation of Jesus Christ, which God gave unto him, to shew unto his servants things which must shortly come to pass; and he sent and signified it by his angel unto his servant John:"}
{"index":{"_id":1002}}
{"bookID":66,"book":"Revelation","chapterID":1,"verseID":2,"passage":"Revelation 1:2","verse":"2Who bare record of the word of God, and of the testimony of Jesus Christ, and of all things that he saw."}
{"index":{"_id":1003}}
{"bookID":66,"book":"Revelation","chapterID":1,"verseID":3,"passage":"Revelation 1:3","verse":"3Blessed is he that readeth, and they that hear the words of this prophecy, and keep those things which are written therein: for the time is at hand."}
{"index":{"_id":1004}}
{"bookID":66,"book":"Revelation","chapterID":1,"verseID":4,"passage":"Revelation 1:4","verse":"4John to the seven churches which are in Asia: Grace be unto you, and peace, from him which is, and which was, and which is to come; and from the seven Spirits which are before his throne; "}
{"index":{"_id":1005}}
{"bookID":66,"book":"Revelation","chapterID":1,"verseID":5"passage":"Revelation 1:5","verse":"5And from Jesus Christ, who is the faithful witness, and the first begotten of the dead, and the prince of the kings of the earth. Unto him that loved us, and washed us from our sins in his own blood, "}
{"index":{"_id":1006}}
{"bookID":66,"book":"Revelation","chapterID":1,"verseID":6,"passage":"Revelation 1:6","verse":"6And hath made us kings and priests unto God and his Father; to him be glory and dominion for ever and ever. Amen"}
{"index":{"_id":1007}}
{"bookID":66,"book":"Revelation","chapterID":1,"verseID":7,"passage":"Revelation 1:7","verse":"7Behold, he cometh with clouds; and every eye shall see him, and they also which pierced him: and all kindreds of the earth shall wail because of him. Even so, Amen."}
{"index":{"_id":1008}}
{"bookID":66,"book":"Revelation","chapterID":1,"verseID":8,"passage":"Revelation 1:8","verse":"8I am Alpha and Omega, the beginning and the ending, saith the Lord, which is, and which was, and which is to come, the Almighty."}

XML File – Image

revelation_20180804_0808AM.png

Output

addUsingBatch_20180803_0403PM

Review Data

Review Data based on JSON file

Command File

setlocal

set "_binfolder=C:\Downloads\Curl\v7.61.0\Windows\extract\curl-7.61.0-win64-mingw\bin"
set "_binapp=curl.exe"
set "_bin=%_binfolder%\%_binapp%"

set "_url=http://localhost:9200"

set "_esindex=bible"
set "_estype=biblekjv"
set "_esoperation=_search"
set "_espath=/%_esindex%/%_estype%/%_esoperation%"

set "_jsonfile=jsonfile\search_book_Revelation.json"
set "_jsonfileFQDN=%cd%\%_jsonfile%"

set "_contentType=Content-Type: application/json"

%_bin% -i -H "%_contentType%"  %_url%%_espath%?pretty --data-binary @%_jsonfileFQDN%

endlocal

XML File

{
  "query":
  {
    "bool":
    {
      "must":
      [
        { "match":
             { "book": "Revelation" }
        }
      ]
    }
  }
  , "sort" :
  {
      "bookID": {"order": "asc"}
    , "chapterID": {"order": "asc"}
    , "verseID": {"order": "asc"}
  }
} 

Output

fetchUsingQuery_20180803_0425PM

Source Code Control

Git

DanielAdeniji/elasticSearchcURLBatch

Link