how can I get more than 10000 in SQL queries?
When use something like this “LIMIT 10000, 2000” I get an error.
I am not requesting more than 10000 records at once, but I can’t seem to paginate.
Thanks for reporting the issue. I tried the query from my side. Basically it reached the
index.max_result_window limit though the size of resulting record is small.
ES doc explains: “The maximum value of
from + size for searches to this index. Defaults to
10000 . Search requests take heap memory and time proportional to
from + size and this limits that memory.”
So I think our LIMIT clause can only work within this window. Alternatively please check out our pagination doc: https://opendistro.github.io/for-elasticsearch-docs/docs/sql/endpoints/#cursor. Thanks!
Hello, thank you for your response. I am using the JDBC driver inside DBeaver. Isn’t that incorporated into the driver or it depends on the application itself?
Yes, it is. I think it’s because our pagination feature is disabled by default for now. So you need to enabled it by changing your ES cluster setting: https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/admin/settings.rst#opendistro-sql-cursor-enabled
And then you can change page size (“fetch_size”) in your JDBC driver by passing fetchSize parameter in connection url or by setFetchSize() method. Please see more details in our README: https://github.com/opendistro-for-elasticsearch/sql/tree/master/sql-jdbc