# Size limitation

**URL:** <https://discuss.elastic.co/t/size-limitation/224696>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [March 23, 2020, 5:06pm UTC](https://discuss.elastic.co/t/size-limitation/224696 "2020-03-23T17:06:41Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![wadhah](https://avatars.discourse-cdn.com/v4/letter/w/bc8723/32.png) [@wadhah](https://discuss.elastic.co/u/wadhah)\
**Post date:** [March 23, 2020, 5:06pm UTC](https://discuss.elastic.co/t/size-limitation/224696/1 "2020-03-23T17:06:42Z")

</div>

Hello,

```auto
    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 :

```auto
    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

```auto
    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

---

<div class="post-metadata">

**Author:** ![preetish\_P](https://avatars.discourse-cdn.com/v4/letter/p/77aa72/32.png) [@preetish\_P](https://discuss.elastic.co/u/preetish_P)\
**Post date:** [March 27, 2020, 1:55pm UTC](https://discuss.elastic.co/t/size-limitation/224696/2 "2020-03-27T13:55:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [March 27, 2020, 2:00pm UTC](https://discuss.elastic.co/t/size-limitation/224696/3 "2020-03-27T14:00:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![steamed\_buns](https://avatars.discourse-cdn.com/v4/letter/s/bbe5ce/32.png) [@steamed\_buns](https://discuss.elastic.co/u/steamed_buns)\
**Post date:** [April 7, 2020, 11:41am UTC](https://discuss.elastic.co/t/size-limitation/224696/4 "2020-04-07T11:41:25Z")

</div>

@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

---

<div class="post-metadata">

**Author:** ![preetish\_P](https://avatars.discourse-cdn.com/v4/letter/p/77aa72/32.png) [@preetish\_P](https://discuss.elastic.co/u/preetish_P)\
**Post date:** [April 7, 2020, 1:25pm UTC](https://discuss.elastic.co/t/size-limitation/224696/5 "2020-04-07T13:25:14Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![steamed\_buns](https://avatars.discourse-cdn.com/v4/letter/s/bbe5ce/32.png) [@steamed\_buns](https://discuss.elastic.co/u/steamed_buns)\
**Post date:** [April 7, 2020, 1:43pm UTC](https://discuss.elastic.co/t/size-limitation/224696/6 "2020-04-07T13:43:13Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![steamed\_buns](https://avatars.discourse-cdn.com/v4/letter/s/bbe5ce/32.png) [@steamed\_buns](https://discuss.elastic.co/u/steamed_buns)\
**Post date:** [April 21, 2020, 8:08am UTC](https://discuss.elastic.co/t/size-limitation/224696/7 "2020-04-21T08:08:49Z")

</div>

@flash1293 Maybe you have another idea/solution?

Cheers

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [April 23, 2020, 11:42am UTC](https://discuss.elastic.co/t/size-limitation/224696/8 "2020-04-23T11:42:39Z")

</div>

Hey, try this condition instead:

```auto
| 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.

---

<div class="post-metadata">

**Author:** ![wadhah](https://avatars.discourse-cdn.com/v4/letter/w/bc8723/32.png) [@wadhah](https://discuss.elastic.co/u/wadhah)\
**Post date:** [April 23, 2020, 11:56am UTC](https://discuss.elastic.co/t/size-limitation/224696/9 "2020-04-23T11:56:30Z")

</div>

It works....Thanks a lot

---

<div class="post-metadata">

**Author:** ![steamed\_buns](https://avatars.discourse-cdn.com/v4/letter/s/bbe5ce/32.png) [@steamed\_buns](https://discuss.elastic.co/u/steamed_buns)\
**Post date:** [April 23, 2020, 12:00pm UTC](https://discuss.elastic.co/t/size-limitation/224696/10 "2020-04-23T12:00:46Z")

</div>

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

Cheers

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 21, 2020, 12:01pm UTC](https://discuss.elastic.co/t/size-limitation/224696/11 "2020-05-21T12:01:31Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.