HI!
I am currently working on a metrics visualization in a Kibana Canvas Element.
My goal is to calculate the current throughput of the processing process of items in an array and render the returned value into a Canvas-Metrics-Element. Concrete: In every new Elasticsearch document the size of the array field can decrease or increase in every subsequent document.
Because the size of the items list can also increase in size I want to define a query that ignores already processed items.
My Problem: Is it possible to calculate it with using only out-of-the-box query functions like combining multiple queries that can be used as one query to render the calculated value in a Kibana Canvas Element?
In this context I have 5 issues:
- Usage of array functions like COUNT in ESSQL
- Usage of functions like INTERSECTION of SQL in ESSQL
- Reuse of Sub-Queries in ESSQL
- Usage multiple indices in ESSQL
- Realization of this calculation using Canvas Expression Language
---------------------------------------------------------------------------------------------------------
- First I am going to get the counted items of the document of the earliest time of the considered periode (e.g. LAST 1 HOUR):
SELECT items FROM index_name WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR ORDER BY timestamp ASC LIMIT 1
- Here I also want to use an ESSQL array function on the field 'items', that counts all items in that array. Which ESSQL function can realize this?
-
Same for the counted items of the document of the latest time of the considered periode (1 HOUR):
SELECT items FROM index_name WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR ORDER BY timestamp DESC LIMIT 1 -
Next Subquery: Get the count of items of the intersected Set of items between the set of the earliest and latest set of items.
SELECT items FROM index_name WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR ORDER BY timestamp ASC LIMIT 1 INTERSECT SELECT items FROM index_name WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR ORDER BY timestamp DESC LIMIT 1
- Are there any similar functions in ESSQL like INTERSECTION I can use to get the intersected set of two array fields?
- Get the difference value of the counted items of the earliest document and the counted items of the latest document of the considered periode. (Query 1) - (Query 2)
- How can I reuse subqueries in ESSQL?
- Reuse all defined subqueries (Query 1 - 4) in one ESSQ Query or other query methods to calculate e.g. like the throughput via subqueries and render the output in an Kibana Canvas.
I am happy to hear any other methods to make these kind of calculations work using subqueries. Most important: It has to work in a Kibana Canvas Environment.
- Is it possible to realize this calculation with this given index (see index mapping) with using the Canvas Expression Language?
Also I thought about using something like this:
SELECT A.size - B.size AS Throughput_per_Second
FROM (
SELECT size FROM index_name WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR ORDER BY timestamp DESC LIMIT 1
) A ,
(
SELECT size FROM index_name WHERE WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR AND ORDER BY timestamp ASC LIMIT 1
) B
WHERE A.size >= B.size
- But unfortunately multiple indices aren't supported in ESSQL.
- Are there any other similar ways to use multiple indices/tables in an ESSQL query?
The mapping of the only index:
{
"mappings": {
"_doc": {
"properties": {
"items": {
"type": "long"
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"size": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"timestamp": {
"type": "date"
}
}
}
}
}
Kind Regards,
Burak