How do I retrieve more than 10000 results/events in Elasticsearch?

Question

Example query:

GET hostname:port /myIndex/_search { 
    "size": 10000,
    "query": {
        "term": { "field": "myField" }
    }
}

I have been using the size option knowing that:

index.max_result_window = 100000

But if my query has the size of 650,000 Documents for example or even more, how can I retrieve all of the results in one GET?

I have been reading about the SCROLL, FROM-TO, and the PAGINATION API, but all of them never deliver more than 10K.

This is the example from Elasticsearch Forum, that I have been using:

GET /_search?scroll=1m

Can anybody provide an example where you can retrieve all the documents for a GET search query?

Michaelamichaele

Answer 1

Scroll is the way to go if you want to retrieve a high number of documents, high in the sense that it's way over the 10000 default limit, which can be raised.

The first request needs to specify the query you want to make and the scroll parameter with duration before the search context times out (1 minute in the example below)

POST /index/type/_search?scroll=1m
{
    "size": 1000,
    "query": {
        "match" : {
            "title" : "elasticsearch"
        }
    }
}

In the response to that first call, you get a _scroll_id that you need to use to make the second call:

POST /_search/scroll 
{
    "scroll" : "1m", 
    "scroll_id" : "DXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAD4WYm9laVYtZndUQlNsdDcwakFMNjU1QQ==" 
}

In each subsequent response, you'll get a new _scroll_id that you need to use for the next call until you've retrieved the amount of documents you need.

So in pseudo code it looks somewhat like this:

# first request
response = request('POST /index/type/_search?scroll=1m')
docs = [ response.hits ]
scroll_id = response._scroll_id

# subsequent requests
while (true) {
   response = request('POST /_search/scroll', scroll_id)
   docs.push(response.hits)
   scroll_id = response._scroll_id
}

UPDATE:

Please refer to the following answer which is more accurate regarding the best solution for deep pagination: Elastic Search - Scroll behavior

Be

Thanks val. I'm not sure I can get this working with curl within php. Unless I can parametrise the get scroll Id and knowing in advance how many docs I will have to retrieve. You see I'm not using sense or either kibana. I have to use google chart to do advance aggregations and I have to query elastic to get two large set of data. Regex them and store result in arrays. Elastic api can be very exotic. Do you think there i a simpler way to retrieve all data? Can index max value be increased ? Or is there any simpler way to use scroll Id s? – Michaelamichaele

You can definitely increase the index.max_result_window value but you'll run the risk of bringing down your cluster if you want to get your 650000 documents in one shot. – Be

Another possibility is to query ES from within a Google Script so it's easier to integrate the results with Google Charts – Be

Otherwise you can stay with curl and use existing solutions to scroll over your index. – Be

Hey @Val; i will test this asap and give you feedback. I apology for the delay. I promise i will do this in the next 3-4 days max. – Michaelamichaele

Answer 2

Note that from + size can not be more than the index.max_result_window index setting which defaults to 10,000.

https://www.elastic.co/guide/en/elasticsearch/reference/6.8/search-request-from-size.html

So You'll have TWO approches here:

1.add the your query the "track_total_hits": true variable.

GET index/_search
{
    "size":1,
    "track_total_hits": true
}

2.Use the Scroll API, but then you can't do the from,size in the ordinary way and you'll have to use the Scroll API.

https://www.elastic.co/guide/en/elasticsearch/reference/6.8/search-request-scroll.html

for example:

 POST /twitter/_search?scroll=1m
{
"size": 100,
"query": {
    "match" : {
        "title" : "elasticsearch"
    }
}
}

Koph

While this code may resolve the OP's issue, it is best to include an explanation as to how your code addresses the OP's issue. In this way, future visitors can learn from your post, and apply it to their own code. SO is not a coding service, but a resource for knowledge. Also, high quality, complete answers are more likely to be upvoted. These features, along with the requirement that all posts are self-contained, are some of the strengths of SO as a platform, that differentiates it from forums. You can edit to add additional info &/or to supplement your explanations with source documentation. – Viburnum

track_total_hits was the ticket for me. I don't want to get a large result window, but I did want to know how many hits there were. – Kroon

"track_total_hits" worked for me. Thanks! – Mischief

I don't get how "track_total_hits" can work or whats the point there? In the documentation linked to this approach it states that "Note that from + size can not be more than the index.max_result_window index setting which defaults to 10,000". This means, that the first approach cant be used to display more than 10k entries? – Unhallow

After researching this for a while and almost going down the scroll API solution, I'm happy I found this answer. The "track_total_hits" parameter is perfect if all you're after is the total number of hits and you do not need the meta data for each document (for post processing, etc.). – Haruspicy

Answer 3

nodeJS scroll example using elascticsearch:

const elasticsearch = require('elasticsearch');
const elasticSearchClient = new elasticsearch.Client({ host: 'esURL' });

async function getAllData(query) {
  const result = await elasticSearchClient.search({
    index: '*',
    scroll: '10m',
    size: 10000,
    body: query,
  });

  const retriever = async ({
    data,
    total,
    scrollId,
  }) => {
    if (data.length >= total) {
      return data;
    }

    const result = await elasticSearchClient.scroll({
      scroll: '10m',
      scroll_id: scrollId,
    });

    data = [...data, ...result.hits.hits];

    return retriever({
      total,
      scrollId: result._scroll_id,
      data,
    });
  };

  return retriever({
    total: result.hits.total,
    scrollId: result._scroll_id,
    data: result.hits.hits,
  });
}

Toothbrush

There's an updated example here using generator function – Runyan

Answer 4

Another option is the search_after Tag. Joined with a sorting mechanism, you can save your last element in the first return and then ask for results coming after that last element.

GET twitter/_search
{
    "size": 10,
    "query": {
        "match" : {
            "title" : "elasticsearch"
        }
    },
    "search_after": [1463538857, "654323"],
    "sort": [
        {"date": "asc"},
        {"_id": "desc"}
    ]
}

Worked for me. But until now getting more than 10.000 documents is really not easy.

Overdo

what is 1463538857 and "654323" – Reagent

same here: ' "search_after": [1463538857, "654323"]' how to get those array values? Any java example would really help a lot. thanks – Narbonne

This is the only correct answer that lets you scroll without limitations. You can increase your scroll window, but besides the fact that ES recommends against it (and that there's cost to the scrolling), it's always limited by the size of your scroll window. search_after does not come with that limitation, though one disadvantage to it is that you don't have positional data (so you can't 'calculate' what page you're on) – Distort

Object []lastSortValues; for (SearchHit documentFields : response.getHits()) { lastSortValues = documentFields.getSortValues(); } – Histoid

Answer 5

You can use scroll to retrieve more than 10000 records. Below is the Python function example to achieve scroll.

self._elkUrl = "http://Hostname:9200/logstash-*/_search?scroll=1m"
self._scrollUrl="http://Hostname:9200/_search/scroll"

"""
Function to get the data from ELK through scrolling mechanism
"""
import logging
import pandas as pd
import requests
import sys


def GetDataFromELK(self):
    # implementing scroll and retrieving data from elk to get more than 100000 records at one search
    # ref :https://www.elastic.co/guide/en/elasticsearch/reference/6.8/search-request-scroll.html
    try:
        dataFrame = pd.DataFrame()
        if self._elkUrl is None:
            raise ValueError("_elkUrl is missing")
        if self._username is None:
            raise ValueError("_userNmae for elk is missing")
        if self._password is None:
            raise ValueError("_password for elk is missing")
        response = requests.post(self._elkUrl, json=self.body,
                                 auth=(self._username, self._password))
        response = response.json()
        if response is None:
            raise ValueError("response is missing")
        sid = response['_scroll_id']
        hits = response['hits']
        total = hits["total"]
        if total is None:
            raise ValueError("total hits from ELK is none")
        total_val = int(total['value'])
        url = self._scrollUrl
        if url is None:
            raise ValueError("scroll url is missing")
        # start scrolling 
        while (total_val > 0):
            # keep search context alive for 2m
            scroll = '2m'
            scroll_query = {"scroll": scroll, "scroll_id": sid}
            response1 = requests.post(url, json=scroll_query,
                                      auth=(self._username, self._password))
            response1 = response1.json()
            # The result from the above request includes a scroll_id, which should be passed to the scroll API in order to retrieve the next batch of results
            sid = response1['_scroll_id']
            hits = response1['hits']
            data = response1['hits']['hits']
            if len(data) > 0:
                cleanDataFrame = self.DataClean(data)
                dataFrame = dataFrame.append(cleanDataFrame)
            total_val = len(response1['hits']['hits'])
            num = len(dataFrame)
        print('Total records recieved from ELK=', num)
        return dataFrame
    except Exception as e:
        logging.error('Error while getting the data from elk', exc_info=e)
        sys.exit()

Seasick

This answer worked for me and was extremely useful. Thanks! – Concatenation

Answer 6

Upgrade the 10000 limit

PUT _settings
{
  "index.max_result_window": 500000
}

Fraya

Answer 7

By default only a lower bound of results is shown, since that is sufficient for many searches. To explicitly tell ElasticSearch to accurately count (and therefore visit) every entry you can add "track_total_hits": true to your query. Keep in mind that this is more expensive than the default behavior.

Link to documentation

Your query would than look like this:

GET hostname:port /myIndex/_search { 
    "size": 10000,
    "track_total_hits": true,
    "query": {
        "term": { "field": "myField" }
    }
}

Bozen

Answer 8

I can suggest a better way to do this. I guess you're trying to get more than 10,000 records. Try the below way and you will get millions of records as well.

1. Define your client.

   client = Elasticsearch(['http://localhost:9200'])
   

2. search = Search(using=client)
   
   

3. Check total number of hits.

   results = search.execute()
   results.hits.total
   

4. s = Search(using=client)
   
   

5. Write down your query.

   s = s.query(..write your query here...)
   

6. Dump the data into a data frame with scan. Scan will dump all the data into your data frame even if it's in billions, so be careful.

   results_df = pd.DataFrame((d.to_dict() for d in s.scan()))
   

7. Have a look at your data frame.

   results_df
   

8. If you're getting an error with search function, then do below:

   from elasticsearch_dsl import Search
   

Credulous

@mairan Its working fine for me. Don't try to get all the data and I guess that is why it's crashing. You must be getting lots of data. First, check how many hits you are getting. Go through my medium blog for a better understanding :- medium.com/@abhimanyusingh_16119/… .please accept the answer if it works. – Credulous

Answer 9

When there are more than 10000 results, the only way to get the rest is to split your query to multiple, more refined queries with more strict filters, such that each query returns less than 10000 results. And then combine the query results to obtain your complete target result set.

This limitation to 10000 results applies to web services that are backed by ElasticSearch index, and there’s just no way around it, the web service would have to be reimplemented without using ElasticSearch.

Irairacund

Hi @Tenusha.I am trying to get 1 lakh records from elastic search through RestClient using Search query.I am sending one query now.Can you tell how can i split queries into multiple and get records so that it increases the performance as well – Countersign

@SachinHR There is a way to retrieve more than 10000 records. But be aware of the consequences (ie memory). Refer to this (discuss.elastic.co/t/…) Also refer quora.com/… – Irairacund

Answer 10

Scroll API has its own limitation. Recently elastic introduce a new functionality (Point in Time).

Point in time

Basically it take a snapshot of index at that time and then you can use search_after to retrieve result beyond 10000.

Edaphic

Answer 11

1. On "Dev Tools" in Elasticsearch set a new max_result_window per index:

PUT indexname/_settings
{
      "index.max_result_window": 30000 # example of 30000 documents
}

2. For the search command: Use with from and size:

res = elastic_client.search(index=index_bu, request_timeout=10, 
body={
  "from": 0, # get from number of document  
  "size": 15000, # how much documents
  "query": {"match_all": {}}
})

3. The next request will be "from": 15000, "size": 15000

Shillyshally

Answer 12

For people still asking this question. You need to increase the max_result_window in your index setting, which has by default 10k.

curl -X PUT "http://localhost:9200/index_name/_settings" -H "Content-Type: application/json" -d '{
  "index" : {
    "max_result_window" : 50000
  }
}'

Just make sure you have enough memory to handle results when you get them.

Understood

Answer 13

Look at search_after documentation

Example query as hash in Ruby:

query = {
  size: query_size,
  query: {
    multi_match: {
      query: "black",
      fields: [ "description", "title", "information", "params" ]
    }
  },
  search_after: [after],
  sort: [ {id: "asc"} ]

}

Bortman

is there any java implementation as example for search-after ? Also, in search_after field, what should be the value here? how to get that? – Narbonne

Answer 14

here you go:

GET /_search
{
  "size": "10000",
    "query": {
        "match_all": {"boost" : "1.0" }
    }
}

But we should mostly avoid this approach to retrieve huge amount of docs at once as it can increase data usage and overhead.

Epimenides

Answer 15

For Node.js, starting in ElasticSeach v7.7.0, there is now a scroll helper!

Documentation here: https://www.elastic.co/guide/en/elasticsearch/client/javascript-api/7.x/client-helpers.html#_scroll_documents_helper

Otherwise, the main docs for the Scroll API have a good example to work off of: https://www.elastic.co/guide/en/elasticsearch/client/javascript-api/current/scroll_examples.html

Edeline