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?
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.
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.
@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.
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.
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.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.