ODBC driver to retrieve the aggregate records beyond 200

[CuriousQA]

Referencing to https://github.com/opendistro-for-elasticsearch/sql/issues/1013

My Elastic search instance holds the details of the transaction timestamp and the service that a customer makes. As there are many customers and the data is available from 2017, the transaction numbers are way higher than 10k. So can you please suggest a way to query aggregate of all the records in that index. Basically on a high level to get the total number of transactions made by a particular customer in a given time period or overall. Or like number of transactions made on a given date.

I'm working this in PowerBI, I can only see the number of transactions made are around 200 only(default number). Kindly provide us your valuable suggestion. I am using Opendistro version 1.11.0 with ES version 7.9.1

[dai-chen]

Hi @CuriousQA , thanks for reporting the issue! will have a look

[CuriousQA]

Thanks @dai-chen for acknowledging this.

[abelykh0]

Additional information: 200 is a default limit, maximum limit is 9999. Still, JDBC should do the needed paging if required.

[CuriousQA]

@dai-chen , I updated the query from JDBC to ODBC. Can you please share your comments on this to retrieve the aggregate results for records beyond 200.

[CuriousQA]

@dai-chen , Can you please provide any update on this. TIA.

[sanketrs]

Hi, I resolved this issue with following command that you will have to execute on Dev Tools of Kibana

1. This will update the maximum number of records that your can retrieve from your elastic index. Here for example, I have set up limit to 2,50,000

PUT /your_index_name/_settings
{
  "index": {
    "max_result_window":"250000"
  }
}

2. This will update the number of records you can retrieve with a SELECT sql query on an elastic index Here for example, I have set up limit to 1,40,000

PUT _cluster/settings
{
  "transient" : {
    "opendistro.query.size_limit" : 140000
  }
}