Make Elasticsearch a database: sort after aggregation

Using Https://github.com/taowen/es-monitor, you can use SQL to make Elasticsearch queries. Sometimes buckets are aggregated to produce a lot of buckets, and we only care about some of them. The simplest way is to sort and then take the first few results.

Elasticsearch database

ORDER by _term


Sql

Elasticsearch create database

$ cat<<Eof|./es_query.py http://127.0.0.1:9200SELECTIpo_year,COUNT(*) fromSymbolGROUP  byIpo_yearORDER  byIpo_year LIMIT2eof{"COUNT(*)":4, "Ipo_year":1972}{"COUNT(*)":1, "Ipo_year":1973}

Elasticsearch vs database






Elasticsearch




{"Aggs": {"ipo_year": {"Terms": {"        field": "Ipo_year",         "order": [          {            "_term": "ASC"          }        ],         "size": 2      },       "Aggs": {}    }  ,   "size": 0}









Because Ipo_year is a GROUP by field, this sort is used with _term.




{"  hits": {    "hits": [],     "Total": 6714,     "Max_score": 0.0  },   "_shards": {    "successful": 1,     "Failed": 0,     "Total": 1  },   "took": 3,   "aggregations": {    "ipo_year": {      "buckets": [        {          "key": 1972,           "Doc_count": 4        },         {          "key": 1973,           "Doc_count": 1        }      ],       "Sum_other_doc_count": 2893,       "Doc_count_error_upper_bound": 0    }  },   "Timed_out": false}

Logstash database to elasticsearch





ORDER by _count


Sql



$ cat<<Eof|./es_query.py http://127.0.0.1:9200SELECTIpo_year,COUNT(*) asIpo_count fromSymbolGROUP  byIpo_yearORDER  byIpo_count LIMIT2eof{"Ipo_count":1, "Ipo_year":1973} {"Ipo_count":2, "Ipo_year":1980}








Elasticsearch




{"Aggs": {"ipo_year": {"Terms": {"        field": "Ipo_year",         "order": [          {            "_count": "ASC"          }        ],         "size": 2      },       "Aggs": {}    }  ,   "size": 0}{  "hits": {    "hits": [] ,     "Total": 6714,     "Max_score": 0.0  },   "_shards": {    "successful": 1,     "failed": 0,     "Total": 1  },   "took": 2,   "aggregations": {    "ipo_year": {      "buckets": [        {          "key": 1973,           "Doc_count": 1        },         {          "key": 1980,           "Doc_count": 2        }      ],       "Sum_other_doc _count ": 2895,       " Doc_count_error_upper_bound ":-1    }  },   " Timed_out ": false}







ORDER by indicator


Sql

Why elasticsearch is not a database

$ cat<<Eof|./es_query.py http://127.0.0.1:9200 SELECTIpo_year,MAX(MARKET_CAP) asMax_market_cap fromsymbolGROUP  byIpo_yearORDER  byMax_market_cap LIMIT2eof{"Max_market_cap":826830000.0, "Ipo_year":1982} {"Max_market_cap":847180000.0, "Ipo_year": .}








Elasticsearch




 {"Aggs": {"ipo_year": {"Terms": {"field": "Ipo_year", "Orde        R ": [{" Max_market_cap ":" ASC "}]," Size ": 2}," Aggs ": {  "Max_market_cap": {"Max": {"field": "Market_cap"}}}}, "Size": 0}{     "hits": {"hits": [], "Total": 6714, "Max_score": 0.0}, "_shards": {"Successful": 1, "failed": 0,  "Total": 1}, "took": "Aggregations": {"ipo_year": {"buckets": [{"Max_market_cap":          {"Value": 826830000.0}, "Key": 1982, "Doc_count": 4}, {       "Max_market_cap": {"value": 847180000.0}, "key":, "Doc_count": 6} ], "Sum_other_doc_count": 2888, "Doc_count_error_upper_bound":-1}}, "Timed_out": false}  /pre>







Histogram and ORDER by


In addition to terms aggregation, other aggregation support order by but are not perfect. For example, histogram aggregation supports sort but does not support size (that is, you can order by but not LIMIT). There are plans to add a generic way to support LIMIT, but it has not yet been implemented: https://github.com/elastic/elasticsearch/issues/14928





Sql



$ cat<<Eof|./es_query.py http://127.0.0.1:9200 SELECTIpo_year_range,MAX(MARKET_CAP) asMax_market_cap fromsymbolGROUP  byHistogram (Ipo_year,Ten) asIpo_year_rangeORDER  byipo_year_range eof{"Ipo_year_range":1970, "Max_market_cap":18370000000.0} {"Ipo_year_range":1980, "Max_market_cap":522690000000.0} {"Ipo_year_range":1990, "Max_market_cap":230940000000.0} {"Ipo_year_range": -, "Max_market_cap":470490000000.0} {"Ipo_year_range": ., "Max_market_cap":287470000000.0}








Elasticsearch



{"Aggs": {"Ipo_year_range": {"Aggs": {"Max_market_cap": {"Max": {"field": "Marke T_cap "}}," histogram ": {" field ":" Ipo_year "," Interval ": Ten," Order ": {" _key ":" ASC "}}}," Size ": 0}{" hits ": {" hits ": []," Total ": 6714," max_s    Core ": 0.0}," _shards ": {" Successful ": 1," failed ": 0," Total ": 1}," took ": 2," aggregations ": {           "Ipo_year_range": {"buckets": [{"Max_market_cap": {"value": 18370000000.0}, "Key": 1970, "Doc_count": 5}, {"Max_market_cap": {"value": 52269            0000000.0}, "key": 1980, "Doc_count": 155}, {"Max_market_cap": {          "Value": 230940000000.0}, "Key": 1990, "Doc_count": 598}, {  "Max_market_cap": {          "Value": 470490000000.0}, "key": $, "Doc_count": 745}, {        "Max_market_cap": {"value": 287470000000.0}, "key":, "Doc_count": 1395 }]}}, "Timed_out": false}








Make Elasticsearch a database: sort after aggregation