Size limitation

Hello,

    essql 
          query="
          SELECT Costs 
          FROM \"test*\"
        "
        | math "round(sum(costs),2)"
        | metric "€"
        | render

I am trying to run the above query, however it is only limited to a 1000 documents.
There's an option to add "count" after the query like the example below :

    essql 
              query="
              SELECT Costs 
              FROM \"test*\"
            " count=10000
            | math "round(sum(costs),2)"
            | metric "€"
            | render

-> However, it is limited to 10000. It is true that I can use

    essql 
                  query="
                  SELECT SUM(Costs) AS sum 
                  FROM \"test*\"
                " 
                | math "round(sum,2)"
                | metric "€"
                | render

But, in this case, if there are no rows/null values in the "Costs" column, we will get an error.

So, is there any possible workaround for such case?

Thanks in advance

Hi @wadhah ,

The 10000 records limit in es-sql causes hindrance, if you are pulling the entire record set in SQL and aggregating on the 'Display' tab . Instead you can use aggregate functions within the SQL.

In your case you can use the below SQL:
SELECT SUM(Costs) AS sum FROM \"test*\" WHERE Costs IS NOT NULL

This is is equivalent to using the 'exists' filter on discover tab.

1 Like

Thanks for your great answer @preetish_P, you are completely right.

Just wanted to add that you should always try to solve things as far upstream as possible - if Elasticsearch can do a calculation for you, you should definitely let Elasticsearch do it for you, because for Canvas the alternative is to stream all of the documents from the Elasticsearch cluster through the Kibana server into the browser and do all of the calculation there. As you can imagine this approach doesn't scale well at all because there are a lot of bottlenecks on the way that could slow down your workpad to a crawl once you are hitting large amounts of production data.

That's why the 10000 records limit is in place here - if you need more than 10.000 individual document in the browser client, you are very likely doing something in the browser which should happen in the Elasticsearch cluster instead.