Scroll

Elasticsearch-sql scroll

In elasticsearch-sql you can use the scan & scroll feature to retrieve large numbers of documents from Elasticsearch efficiently. Read about Scan and scroll on elasticsearch official guide

How to use?

1. If you want to use scroll on your queries simply add this hint
   

SELECT /*! USE_SCROLL*/ firstname , balance FROM accounts

This will cause a scroll with default values 50,60k to start
You can specify the values like this:

SELECT /*! USE_SCROLL(100,30000)*/ firstname , balance FROM accounts

The first value is number of documents per shard in each scroll (so in default if you have 3 shards you'll get 150 per fetch)
The second value is the how long keep the search context open (in milliseconds - so default is 1minute) 2. If you are using the Web UI you can simply check the always scroll and change the size of scroll and it will add the hint to your query automatically
On first fetch you'll see the number of results your going to get, and the scroll buttons will be enabled. Clicking on this button will get you the next scroll.

Clicking on this button will scroll you till the end (one scroll at a time)

Need to know

• The combination of limit and use_scroll hint is not supported
• On scan & scroll the first result only returns a scrollId and the total number of hits matching the query.