TL;DR Summary: ElasticSearch aggregations enable users to group and analyze data similar to SQL's GROUP BY functionality, such as counting web hits from logs. These operations can be complex, allowing for nested queries.
- Aggregations can handle numeric and keyword types for efficient data analysis.
- Common types include bucket aggregations for counting occurrences.
ElasticSearch lets you do the equivalent of a SQL GROUP BY COUNT and AVERAGE functions. They call these aggregations.
In other words, if you are looking at nginx web server logs you could:
- group each web hit record by the city from where the user came
- count them
So this would give you something like:
| City | web hits |
| Paris | 20 |
| London | 30 |
| Berlin | 40 |
In SQL this would be something like:
select city, count(city) from logs
group by city
Here we illustrate this using the simplest use case, web logs. Follow the previous doc to populate your ElasticSearch instance with some nginx web server logs if you want to follow along.
Because ElasticSearch is concerned with performance, there are some rules on what kind of fields you can aggregate. You can group by any numeric field but for text fields that have to be of type keyword or have fielddata=true.
You can think of keyword as being like an index. When we loaded the nginx data, we did not create the index mapping first. We let ElasticSearch build that on-the-fly. So it chose to index all the text fields since it map those of type keyword, like this:
"geoip" : {
"dynamic" : "true",
"properties" : {
"city_name" : {
"type" : "text",
"norms" : false,
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
Now, aggregations is a complicated topic. There are many variations. You can nest them to build up complex queries. Here we look at the simplest, most common use case: bucket aggregation. That is like the select, count SQL statement to produce a count by value.
In the example below, we want to count web hits by the city name.
The general pattern to build up the statement is:
- Use aggs, which is short for aggregations.
- Give the agg a name. Here we use cityName.
- Tell it what field to use. Here we use the dot notation geoip.city_name since city_name is a property of geo_ip. In other words in a deeply nested JSON structure put a dot as you go down the hierarchy. For a JSON array, you would use ElasticSearch scripting, a topic we have not covered yet.
- Add the word keyword, to tell it to use that index.
- Give it the aggregation operation type. here we use terms. You can also use ave (average) and some others.
You can also use filters, which we illustrate further below.
So this query products of a count of web hits by city.
curl -XGET --user $pwd --header 'Content-Type: application/json' https://58571402f5464923883e7be42a037917.eu-central-1.aws.cloud.es.io:9243/logstash/_search?pretty -d '{
"aggs": {
"cityName": {
"terms": {
"field": "geoip.city_name.keyword",
"size": 10
}
Here are the results. We gave it the default size of 10, meaning how far it should go. Since we have 18 cities in our data, "sum_other_doc_count" : 8 means it left off 8 records. Remember that ElasticSearch has many rules to keep performance high.
Notice that under each with these is a doc_count. So we had 6 web hits from the city of Montpellier.
"aggregations" : {
"cityName" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 8,
"buckets" : [
{
"key" : "Montpellier",
"doc_count" : 6
},
{
"key" : "Suzano",
"doc_count" : 3
},
{
"key" : "Boardman",
"doc_count" : 2
},
{
"key" : "Mazatlán",
"doc_count" : 2
},
{
"key" : "New York",
"doc_count" : 2
},
{
"key" : "San Diego",
"doc_count" : 2
},
{
"key" : "Abadan",
"doc_count" : 1
},
{
"key" : "Ashburn",
"doc_count" : 1
},
{
"key" : "Bogotá",
"doc_count" : 1
},
{
"key" : "Cambe",
"doc_count" : 1
}
]
}
}
}
Here we count the same data by response, e.g., 200, 404, etc. Since respond is of type long it's not necessary to add keyword to the end.
curl -XGET --user $pwd --header 'Content-Type: application/json' https://58571402f5464923883e7be42a037917.eu-central-1.aws.cloud.es.io:9243/logstash/_search?pretty -d '{
"aggs": {
"responses": {
"terms": {
"field": "response",
"size": 10
}
}
}
}'
Here are the results. Since there are only 4 types of responses in our data it showed all of them since that is < 10.
"aggregations" : {
"responses" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 200,
"doc_count" : 23
},
{
"key" : 400,
"doc_count" : 13
},
{
"key" : 404,
"doc_count" : 8
},
{
"key" : 304,
"doc_count" : 5
}
]
}
}
Here is an example using a query to filter the results. This is from FDA drug interaction data that we will explore in our upcoming posts on analytics.
This query counts drug interactions by drug type, which is the whole purpose of the FDA database, to track drug interactions and side effects. So this is a bucket type aggregation query and subquery
curl -XGET --user $pwd --header 'Content-Type: application/json' https://58571402f5464923883e7be42a037917.eu-central-1.aws.cloud.es.io:9243/fda/_search?pretty -d '{
"query": {
"term": {
"patient.drug.medicinalproduct.keyword": {
"value": "METRONIDAZOLE"
}
}
},
"aggs" : {
"drug": {
"terms" : {
"field": "patient.drug.medicinalproduct.keyword"
}
},
"adverseaffect" : {
"terms" : {
"field": "patient.reaction.reactionmeddrapt.keyword"
}
}
}
}'
ElasticSearch Guide
- ElasticSearch Tutorial for Beginners: ElasticSearch Basics
- Using Apache Hive with ElasticSearch
- Using ElasticSearch with Apache Spark
- Curl ElasticSearch Commands Cheat Sheet
- Spark ElasticSearch Hadoop Update and Upsert Example and Explanation
- How To Write Apache Spark Data to ElasticSearch Using Python
- ElasticSearch Search Syntax and Boolean and Aggregation Searches
- ElasticSearch Nested Queries: How to Search for Embedded Documents
- ElasticSearch Joins: Has_Child, Has_parent query
- How to Setup up an Elastic Version 7 Cluster
- How to Configure Filebeat for nginx and ElasticSearch
- Using Beats and Logstash to Send Logs to ElasticSearch
- How to Load CSV File into ElasticSearch with Logstash
- ElasticSearch Aggregations Explained
- What is the ELK Stack?
- Logstash 101: Using Logstash in a Data Processing Pipeline
- Using Kibana to Execute Queries in ElasticSearch using Lucene and Kibana Query Language
- ElasticSearch Machine Learning
- How to Install Elastic Enterprise Search
- How To Use Elastic Enterprise Search with GitHub
These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.