Elasticsearch SQL retrieve more than 1000

Question

Implementing SQL query in Elasticsearch, not able to extract records more than 1000 in spite of LIMIT > 1000. Tried "index.mapping.total_fields.limit": 5000, but did not help.

POST _sql?format=txt
{
  "query": "SELECT column1 FROM table LIMIT 5000"
}

Answer 1

TL;DR not possible at the moment

It is possible to run the same queries without a LIMIT however in that case if the maximum size (10000) is passed, an exception will be returned as Elasticsearch SQL is unable to track (and sort) all the results returned.

https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html