ES course

Elastic search

#!bash
# Course content

# creating the index
curl -H "Content-Type: application/json" -XPUT 127.0.0.1:9200/movies -d '
{
    "mappings": {
        "movie": {
            "properties": {
                "year": {"type": "date"}
            }
        }
    }
}'

# get the mappings & index
curl -H "Content-Type: application/json" -XGET 127.0.0.1:9200/movies/_mapping/movie

# adding the document into the index
./curl -XPUT 127.0.0.1:9200/movies/movie/109487 -d '
{
    "genre": ["IMAX","Sci-Fi"],
    "title": "Interstellar",
    "year": 2014
}'

# search from the file
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty


# importing data from the file
../bin/curl -XPUT 127.0.0.1:9200/_bulk?pretty --data-binary @movies.json

# delete the index1
../bin/curl -XDELETE 127.0.0.1:9200/movies

# define a new index
../bin/curl -XPUT 127.0.0.1:9200/movies -d '
{
    "mappings": {
        "movie": {
            "properties": {
                "id": {"type": "integer"},
                "year": {"type": "date"},
                "genre": {"type": "keyword"},
                "title": {"type": "text", "analyzer": "english"}
            }
        }
    }
}'

# importing again data from the file
../bin/curl -XPUT 127.0.0.1:9200/_bulk?pretty --data-binary @movies.json


# parent child relations
../bin/curl -XPUT '127.0.0.1:9200/series' -d '
{
	"mappings": {
		"movie": {
			"properties": {
				"film_to_franchise": {
          "type": "join",
					"relations": {
						"franchise" : "film"
					}
				}
			}
		}
	}
}'

# feed data
../bin/curl -XPUT 127.0.0.1:9200/_bulk?pretty --data-binary @series.json

# find the children of parent using join field
../bin/curl -XGET 127.0.0.1:9200/series/movie/_search?pretty -d '
{
	"query": {
		"has_parent": {
			"parent_type": "franchise",
			"query": {
				"match": {
					"title": "Star Wars"
				}
			}
		}
	}
}'  


# find the parent of child using join field
../bin/curl -XGET 127.0.0.1:9200/series/movie/_search?pretty -d '
{
	"query": {
		"has_child": {
			"type": "film",
			"query": {
				"match": {
					"title": "force"
				}
			}
		}
	}
}'  

# search queries
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
	"query": {
		"bool": {
			"must": {
				"term": {
					"title": "trek"
				}
			},
			"filter": {
				"range": {
					"year": {
						"gte": 2015
					}
				}
			}
		}
	}
}' 


# Proximities queries
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
	"query": {
		"bool": {
			"must": {
				"term": {
					"title": "trek"
				}
			},
			"filter": {
				"range": {
					"year": {
						"gte": 2015
					}
				}
			}
		}
	}
}'

# Phrase query
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
	"query": {
		"match_phrase": {
			"title": "star wars"
		}
	}
}'  

# Slop query
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
	"query": {
		"match_phrase": {
			"title": {"query": "star beyond", "slop": 100}
		}
	}
}'

# Star wars movie released after 1980
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
	"query": {
		"bool": {
			"must": {
				"match_phrase": {
					"title": "star wars"
				}
			},
			"filter": {
				"range": {
					"year": {
						"gte": 1980
					}
				}
			}
		}
	}
}'

# Pagination query
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
	"from": 0,
	"size": 2,
	"query": {
		"bool": {
			"must": {
				"match_phrase": {
					"title": "star wars"
				}
			}
		}
	}
}'


# can't sort on analyzed field
# sort by having an another copy of that data
# Sorting
# create a new index by having raw title so that we can sort on it
../bin/curl -XPUT 127.0.0.1:9200/movies -d '
{
    "mappings": {
        "movie": {
            "properties": {
                "title": {
                    "type": "text",
										"fields": {
											"raw": {
												"type": "keyword"
											}
										}
                }
            }
        }
    }
}'

# Now we can sort

../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?sort=title.raw



### Filters
#year-  > 2010 &&  year < 2015
# genre -> sci-fi
# not having in title -> trek
../bin/curl -XGET 127.0.0.1:9200/movies/_search?pretty -d '
{
    "query": {
        "bool": {
            "must": {
                "match": {
                    "genre": "Sci-Fi"
                }
            },
            "must_not": {
                "match": {
                    "title": "trek"
                }
            },
            "filter": {
                "range": {
                    "year": {
                        "gte": 2010,
                        "lt": 2015
                    }
                }
            }
        }
    }
}
'

# Sci-fi movies
# gt > 1960 year
# sorted by title
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?sort=title.raw&pretty -d '
{
    "query": {
        "bool": {
            "must": {
                "match": {
                    "genre": "Sci-Fi"
                }
            },
            "filter": {
                "range": {
                    "year": {
                        "gt": 1960
                    }
                }
            }
        }
    }
}
'

#levenshtein edit distance accounts for the tolerance of
# substitution, misspeled, addition of characters
# this accounts for fuzziness parameter

# Non fuzzy query
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
    "query": {
			"match": {
				"title" : "intersteller"
			}
    }
}
'


# No response from above query then we can convert this to fuzzy query.
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
    "query": {
			"fuzzy": {
				"title" : {
					"value": "intersteller",
					"fuzziness": 1
					}
			}
    }
}
'

# fuziness can be set to auto instead of manual set
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
    "query": {
        "fuzzy": {
            "title": {
                "value": "intepsteller",
                "fuzziness": "auto"
            }
        }
    }
}'

# Partial queries
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
    "query": {
        "prefix": {
					"title": "sta"
				}
    }
}'


# wildcard
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
    "query": {
        "wildcard": {
					"title": "sta*"
				}
    }
}'

# N-Grams, and search as You Type

../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?pretty -d '
{
    "query": {
        "match_phrase_prefix": {
					"title": {
						"query": "star trek",
							"slop": 10
					}
				}
    }
}'


# indexing n-grams again to setup analyzer
../bin/curl -XPUT 127.0.0.1:9200/movies?pretty -d '
{
    "settings": {
        "analysis": {
            "filter": {
                "autocomplete_filter": {
                    "type": "edge_ngram",
                    "min_gram": 1,
                    "max_gram": 20
                }
            },
            "analyzer": {
                "autocomplete": {
                    "type": "custom",
                    "tokenizer": "standard",
                    "filter": [
                        "lowercase",
                        "autocomplete_filter"
                    ]
                }
            }
        }
    }
}'

# analyzer
../bin/curl -XGET 127.0.0.1:9200/movies/_analyze?pretty -d '
{
	"analyzer": "autocomplete",
	"text": "Sta"
}
'

# mapping with the field
../bin/curl -XPUT 127.0.0.1:9200/movies/_mapping/movie?pretty -d '
{
	"movie": {
		"properties": {
			"title" : {
				"type": "text",
				"analyzer": "autocomplete"
			}
		}
	}
}'

# Search
../bin/curl -XPUT 127.0.0.1:9200/movies/_search/movie?pretty -d '
{
    "query": {
      "match": {
      	"title": {
      		"query": "sea",
      		"analyzer": "standard"
      	}
      }
    }
}'

# search 
../bin/curl -XPUT 127.0.0.1:9200/movies/_search/movie?pretty -d '
{
    "query": {
      "match": {
      	"title": {
      		"query": "sea",
      		"analyzer": "autocomplete"
      	}
      }
    }
}'

# Download the ml-latest-small


# check the rating
../bin/curl -XGET 127.0.0.1:9200/ratings/_search


# Running logstash

cd /usr/share/logstash
sudo bin/logstash -f /etc/logstash/conf.d/logstash.conf 



# install mysql server
# create database movielens
# create table moEY vies

create table movies (
	movieId INT PRIMARY KEY NOT NULL,
	title TEXT,
	releaseDate DATE
);

LOAD DATA LOCAL INFILE 'ml-100k/u.item' INTO TABLE movielens.movies FIELDS TERMINATED BY '|'
(movieId, title, @var3)
set releaseDate = STR_TO_DATE(@var3, '%d-%M-%Y');


# aggregations
# if we pass size=0 then unrequired data will not come
../bin/curl -XGET 127.0.0.1:9200/ratings/rating/_search?size=0?pretty -d '
{
	"aggs": {
		"ratings": {
			"terms": {
				"field": "rating"
			}
		}
	}
}
'

../bin/curl -XGET 127.0.0.1:9200/ratings/rating/_search?size=0?pretty -d '
{
	"query": {
		"match": {
			"rating": 5.0
		}
	},
	"aggs": {
		"ratings": {
			"terms": {
				"field": "rating"
			}
		}
	}
}
'

../bin/curl -XGET 127.0.0.1:9200/ratings/rating/_search?size=0?pretty -d '
{
	"query": {
		"match_phrase": {
			"title": "Star Wars Episode  IV"
		}
	},
	"aggs": {
		"avg_rating": {
			"avg": {
				"field": "rating"
			}
		}
	}
}'

# Histogram
../bin/curl -XGET 127.0.0.1:9200/ratings/rating/_search?size=0?pretty -d '
{
	"aggs": {
		"whole_ratings": {
			"histogram": {
				"field": "rating",
				"interval": 1.0
			}
		}
	}
}
'


# movie in range of 10 years
../bin/curl -XGET 127.0.0.1:9200/movies/movie/_search?size=0?pretty -d '
{
	"aggs": {
		"movie_release": {
			"histogram": {
				"field": "year",
				"interval": 10
			}
		}
	}
}'

# time series data
# website hits by hour
../bin/curl -XGET 127.0.0.1:9200/logstash-2017.04.30/_search?size=0?pretty -d '
{
	"aggs": {
		"timestamp": {
			"date_histogram": {
				"field": "@timestamp",
				"interval": "hour"
			}
		}
	}
}
'

# Googlebot traffic by hour
../bin/curl -XGET 127.0.0.1:9200/logstash-2017.04.30/_search?size=0?pretty -d '
{
	"query": {
		"match": {
			"agent": "Googlebot"
		}
	},
	"aggs": {
		"timestamp": {
			"date_histogram": {
				"field": "@timestamp",
				"interval": "hour"
			}
		}
	}
}
'

# to checkout when site was down
../bin/curl -XGET 127.0.0.1:9200/logstash-2017.04.30/_search?size=0?pretty -d '
{
	"query": {
		"match": {
			"response": "500"
		}
	},
	"aggs": {
		"timestamp": {
			"date_histogram": {
				"field": "@timestamp",
				"interval": "hour"
			}
		}
	}
}'


# average rating of all star wars movie
# reindex with the raw field
../bin/curl -XGET 127.0.0.1:9200/ratings/rating/_search?size=0?pretty -d '
{
	"query": {
		"match_phrase": {
			"title": "Star Wars"
		}
	},
	"aggs": {
		"titles": {
			"terms": {
				"field": "title:raw"
			},
			"aggs": {
				"avg_rating": {
					"avg": {
						"field": "rating"
					}
				}
			}
		}
	}
}
'


# remapping the index 
../bin/curl -XPUT 127.0.0.1:9200/ratings/mapping/rating?pretty -d '
{
	"properties": {
		"title": {
			"type": "text",
			"fielddata": true
		}
	}
}
'