Size limitation

[wadhah]

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

[preetish_P (Preetish Pai)]

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.

[flash1293 (Joe Reuter)]

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.

[steamed_buns]

@preetish_P Thanks for the tip with WHERE Costs IS NOT NULL, but I think this is only solving half of the problem @wadhah mentioned in his post.

The real problem shows itself, when the query returns no rows, e.g. after a new index has been created as a result of an ILM policy and no data has been pushed to this index yet or if we extend the example and assume that there is another WHERE clause in the query which results in no rows. In these cases the canvas widget will throw an error and display an ugly warning sign, because the SQL aggregation function tried to aggregate a null value. Specifying WHERE Costs IS NOT NULL is of no use here.

How would one bypass this and instead show a zero, wich would be sane here, as there is nothing to sum/aggregate?

This is also more of a general problem and not specific to size limits.

Cheers

[preetish_P (Preetish Pai)]

Hi @steamed_buns

When no rows are returned by the query the rowCount function comes in handy. In the above example, adding an additional if-condition might help:

essql
query="SELECT SUM(Costs) AS sum 
FROM \"test*\" WHERE Costs IS NOT NULL" 
                | if {rowCount | eq 0} then="0.00" else={math "round(sum,2)"}
                | metric "€"
                | render

However if there is no mapping present for the field(s) used in WHERE clause, the canvas widget will fail with an exclamation mark.

[steamed_buns]

@preetish_P Thank you for your quick reply.

Unfortunately, we already tried your suggestion but to no avail. The rowCount part is actually never reached because SUM(Costs) will already cause an error if there are no rows to add up.

[steamed_buns]

@flash1293 Maybe you have another idea/solution?

Cheers

[flash1293 (Joe Reuter)]

Hey, try this condition instead:

| if {getCell "sum" | gt 0} then={math "round(sum,2)"} else="0.00"

If there are no Costs, then the data table will contain null as value. This if expression will deal with the case correctly.

[wadhah]

It works....Thanks a lot

[steamed_buns]

That is awesome, thank you very much for this @flash1293

Cheers