Hello,
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.
Thank you
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: Endpoint - Open Distro Documentation. 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?
Thank you.
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: sql/settings.rst at main · opendistro-for-elasticsearch/sql · GitHub
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: sql/sql-jdbc at main · opendistro-for-elasticsearch/sql · GitHub