Support Bucket Script Aggregation

[rcrezende]

Given the rhe new Bucket Script Aggregation, we can implement formulas from aggregated metric values (e.g. metric1 / metric2 * 100). This would allow calculation of e.g. ratio between two metric results.

https://www.elastic.co/guide/en/elasticsearch/reference/master/search-aggregations-pipeline-bucket-script-aggregation.html

Usage Scenario:
Links to: #3505

[rashidkpc]

Duplicate #4584

[Kazark]

@rashidkpc I don't see how this is a dupe of the pipelines stuff. The pipelines in in 5.4 but I don't see bucket script aggregations there. Can we re-open this?

[darrencruse]

I'm interested in this feature in order to do visualizations of values on documents that represent farmers fields where I need to weight the values greater for large fields and smaller for small fields, after the search query has selected the field documents to be included in the weighted average.

The bucket_script feature seems to be the answer (I have it working in elasticsearch) but I can't seem to reproduce such a query when using a Kibana visualization.

Are there any plans to support this soon?

Or is there any workaround?

(e.g. I was trying to see if I can directly tell Kibana the elasticsearch query I want used for a visualization instead of Kibana creating the query for me based on my UI choices - but that doesn't seem to be possible? Is that correct?)

[alonsomoya]

+1

[smoreaud]

I would be interested in this functionality. In our use case, it would allow to work around with success/failure ratios.

[Spacefish]

+1 would like to see this as well! Should be pretty simple to implement.. Just a "Custom Metric" in the Dropdown which presents a box where you can input your aggregation JSON..

[tbragin]

Starting with Kibana 5.4, bucket script agg is now supported in Time Series Visual Builder, though it's called "Calculation" (cc @simianhacker I wonder if we should make it more explicit that this option relies on "Bucket Script"?)

Could folks on this ticket (e.g. @Kazark) give it a try and let us know if it helps in your use cases?

[alexfrancoeur]

I think @simianhacker has agreed that it makes sense to rename this aggregation. I've opened #13796 for tracking purposes.

[hetzer-fastec]

@tbragin Having the bucket script aggregation in the TSVB is a very nice feature, imo - thanks a lot for implementing it. The things which I tried worked very well.

Nevertheless it would be great if the TSVB supported additional script aggregations (e.g. scripted sum aggregation), as the bucket script aggregation can only fully leverage its potential, if all kinds of sub-aggregations are supported. But since this is not a place to discuss this in more detail, I have opened another issue.

[shaharmor]

Is there a timeline for this feature?

[HardCoreCodin]

The 'Calculate' feature can only do a bucket-script as a top-level, it can't be applied within a parent bucket-aggregation... :(

And that's only in the time-series visualization, there's no bucket-script in the standard visualizations.

Our particular use-cases require both, and it seems like none are supported - not even in 6.x(rc)
Are there any plans for this?

In our use-cases, each document has a start-time and end-time date-type fields, and we need to generate an overall-duration across buckets of those, grouped on some category-field.
This involves first bucketing on that category, then within that doing 2 metrics: min(start-time) and nax(end-time) of the documents in each bucket, then applying a pipeline-script-bucket agg that uses these 2 metric-aggs to compute the duration between them (max_end_time - min_start_time). That gives us a 'duration' metric as the output of each bucket. We then want to apply some metric-pipeline-aggs on them.

An almost identical structure has been detailed here: https://discuss.elastic.co/t/kibana-5-4-bucket-script-visualization/86475

I was able to express that in the query-DSL, and we're using it in production already - but we can't visualize these in Kibana - which REALLY sucks, as our clients have been requesting such visualizations for years, and we've always told them 'we need support from later versions, for pipeline-aggregations'...
Well, now we have those - but actually, don't...
In time-series builder, we have bucket-script pipeline aggregation that can be expressed, but it can't consume/be-applied-on a parent bucket-term-aggregation, and in the other visualization, we have bucket-term-aggregations, and some pipeline-metric-aggregations, but not the needed bucket-script pipeline aggregation needed to express that...

Is there a workaround using some "advanced JSON" thing I can put my query-DSL in there?

[nicoulaj]

Just adding my use case as requested in this question:

My index contains the following document type (which represents test executions):

• id of test (string)
• success (boolean)
• version of software

I want to build the following histogram:

• On X axis: versions
• On Y axis: number of unique tests, stacked on success/failure

The same test id can be executed several times on same version with different results: it should be counted as success if it is successful once.

I really hope this will be implemented, as this is a definitive showstopper for us.

[Spacefish]

Please just add "Bucket Script Aggregation" or "Custom Aggregation" as an option in the normal visualizations.. Should be easy, just like the other aggregation but without adding any content in the aggregation body (just the json supplied by the user)

[ppf2]

One use case for this feature is to workaround Elasticsearch not having a non-negative option for derivative aggregation so that charts can end up showing negative derivative values (upon intentional server restarts): elastic/elasticsearch#15542

[Sagesh]

+1

[fkelbert]

This is a much requested feature from users, similar to #17544.

Are there any plans to implement this?

[glmrenard]

Would be nice !

[AmandaBSobrinho]

+1

[suportecaso1]

+1

[GrahamHannington]

I'm late to this party, so my disappointment is fresh.

A few days ago, I created the Elastic discussion forum topic "Visualize a value calculated from the per-bucket sum of one field divided by the per-bucket sum of another?" (sorry about the verbose title).

Soon after, I found this issue, and belatedly realized why I couldn't figure out how to do that in "standard" Kibana visualizations ☹️.

I replied to my own forum topic, citing this issue, and showing basic examples of using bucket scripts in TSVB and—I guess this is why I'm posting this comment, in case it's helpful to anyone—Vega-Lite.

[fbaligand]

Waiting this wanted feature, an alternative for data table visualization is to use « enhanced table » community plugin, and its « computed columns » feature:
https://github.com/fbaligand/kibana-enhanced-table

[jmottster]

Kibana is useless at the moment because there is no way to do any kind of post processing of an aggregated field value. I've lost 2 days trying to figure out a work-around only to find that any and all ways to do this (bucket_script, scripted_fields, weighted average, etc.) are seemingly blocked by Kibana intentionally. Now we have to find a new tool and move away from Kibana, which most likely means the entire Elk stack.

Is there a technical limitation here? This thread is 6 years old, either the development team doesn't care about those that actually use this tool, or there's some good reason that isn't communicated to its users very well. It seems silly that Kibana can't display what Elasticsearch is capable of. Even just a visualization tool for advanced users where you can just input you query and get a data table result from its results should be provided if the easy to use tools have some kind of limitation in this area.

A sad good-bye to a tool I was really liking but spent way too long defending

[Sagesh]

Kibana is useless at the moment because there is no way to do any kind of post processing of an aggregated field value. I've lost 2 days trying to figure out a work-around only to find that any and all ways to do this (bucket_script, scripted_fields, weighted average, etc.) are seemingly blocked by Kibana intentionally. Now we have to find a new tool and move away from Kibana, which most likely means the entire Elk stack.

Is there a technical limitation here? This thread is 6 years old, either the development team doesn't care about those that actually use this tool, or there's some good reason that isn't communicated to its users very well. It seems silly that Kibana can't display what Elasticsearch is capable of. Even just a visualization tool for advanced users where you can just input you query and get a data table result from its results should be provided if the easy to use tools have some kind of limitation in this area.

A sad good-bye to a tool I was really liking but spent way too long defending

We are also on the same line of thinking. Kibana can't be used as a visualization tool, it's more like a log analyzing tool.

[ypid-geberit]

(bucket_script, scripted_fields, weighted average, etc.) are seemingly blocked by Kibana intentionally.

#4707 (comment) works for me so far. What is your use case in that they don’t work?

[fbaligand]

@jmottster
Well, concerning "processing of an aggregated field value", there are several ways in Kibana:

• scripted fields (in Kibana index patterns) allow to process aggregated field values. Example: doc['field1'].value * 2
• you can use "Bucket Script" aggregation in "Time Series Visual Builder" visualization (TSVB). You can also do "Math" calculations and other parent/sibling pipeline aggregations in this visualization
• in Kibana Canvas, you can define a SQL query as input and so define a "custom query for advanced users", and in advanced edition mode, you can chain filters to transform the data (with pipes)
• in Kibana Timelion visualization, you can also chain functions in "Timelion expression" that transform the data
• finally, in Vega visualization, you can do even more complex and custom visualizations (although that's harder to use)

[jmottster]

@jmottster
Well, concerning "processing of an aggregated field value", there are several ways in Kibana:

* scripted fields (in Kibana index patterns) allow to process aggregated field values. Example: `doc['field1'].value * 2`

* you can use "Bucket Script" aggregation in "Time Series Visual Builder" visualization (TSVB). You can also do "Math" calculations and other parent/sibling pipeline aggregations in this visualization

* in Kibana Canvas, you can define a SQL query as input and so define a "custom query for advanced users", and in advanced edition mode, you can chain filters to transform the data (with pipes)

* in Kibana Timelion visualization, you can also chain functions in "Timelion expression" that transform the data

* finally, in Vega visualization, you can do even more complex and custom visualizations (although that's harder to use)

Yes, thank you for providing this info, however it doesn't meet my particular needs, which is to have these aggregation features available in a data table output format. Graphs are not the problem.

[fbaligand]

If you want a table output, there are several ways:

• in Kibana Canvas, you can display a table visualization from a SQL query (with aggregation functions)
• in TSVB, you can display a table
• finally, you can use "enhanced-table" community plugin that has some enhanced features compared to classic data table, like computed columns and "Split Columns" bucket:
  https://github.com/fbaligand/kibana-enhanced-table/

And yes, I created this community plugin ;)

[jmottster]

If you want a table output, there are several ways:

* in Kibana Canvas, you can display a table visualization from a SQL query (with aggregation functions)

* in TSVB, you can display a table

* finally, you can use "enhanced-table" community plugin that has some enhanced features compared to classic data table, like computed columns and "Split Columns" bucket:
  https://github.com/fbaligand/kibana-enhanced-table/

And yes, I created this community plugin ;)

I've entertained all the options, SQL is not an option, that's why we were using Elasticsearch in the first place, so as not to overload the main databases. I certainly appreciate suggestions, but I've been through all of them. I'm not here for that, I'm here to say this should be a available in all visualizations, we shouldn't have to search for work-arounds to visualize (graph or table) data in Kibana when we're using out-of-the-box Elasticsearch features for our queries. It makes no sense that there are restrictions, and every time someone points them out, the response is just a list of inconvenient work-arounds rather than any information on why these features are missing or if/when they will be implemented. If the reason were shared, perhaps there'd be less frustration -- which I see everywhere I find this subject discussed -- and more patience.

I would have loved to try out your plugin, btw, as I had found it. But our deployment environment was Elastic Cloud, (which isn't cheap, thus my frustrations), and has its own limitations like not being able to install plugins,

[fbaligand]

Sorry that you can’t use enhanced table plugin...
That’s a sad limitation in Elastic Cloud, I agree.

Concerning SQL, I want to say “a SQL query to Elasticsearch”, not to a SQL database. Because yes, you can query Elasticsearch using SQL.

Finally, I fully agree with you that bucket script should be available in all visualizations, I would love that. And so, I share your frustration.

[timductive]

Closing this because it's not planned to be resolved in the foreseeable future. It will be tracked in our Icebox and will be re-opened if our priorities change. Feel free to re-open if you think it should be melted sooner.