Elasticsearch: Reintroduce raw query editor

[yehosef]

This is copied from elastic/kibana#5282 - But if it could be done in grafana - all the better!

==begin copy==

I would like to suggest an advanced query option where a user can put in a raw query and use kibana for visualizing it.

When you build a query, the data comes back in aggregations and sometimes nested aggregations depending on the complexity. But eventually all of that can be represented in a table which is shown with the request/response.

If I could take the columns of that table and define which I want to be the X-axis, which should be the Y axis, which for split chart, which for split bars, etc. I could build my own visualization and even change it (there are sooo many times that I have build a visualization using split chart with multiple filters and then I decide I want instead a split bars and I have to cut and paste the fields, which it's really a small internal change to tell the rendering what to do with the data)

The advantage of this approach is that it would allow people to do more sophisticated queries and aggregations without them needing to be implemented in the query builder. EG, we could also start using pipeline aggregations now, without needed to wait for them to be implemented in Kibana.

As I see it, the visualize tool in Kibana has 3 parts: the query builder, making/receiving/parsing the response, rendering the response. If we can just take out the query builder for certain queries, I think the data will be pretty easy to drop into the pipeline. What'll happen is that the aggregation names instead of being "1" or "2" will be "date" or "num_visits" and the user will need to specify which fields are the X/Y axis, split chart/split bar

== end copy ==

Elasticsearch can do many amazing things that are hard to build a query builder to represent. There are the pipeline aggregations (which may or not be hard - it just new). Another that we use are parent/children documents. We are running analytics for user retention with a "user" doc and children "user_day" or "user_week" docs which represent the aggregated activities for the user. We want to run reporting similar to http://jwegan.com/growth-hacking/27-metrics-pinterests-internal-growth-dashboard/ and sometimes I want to find a person who was active in a certain week that had been inactive x weeks before. With parent/children documents we ask for a child that has a certain parent that has a certain child. It works great and is performant, the problem is the visualization tools don't support such a complicated query. If we could write the query ourselves, we could use grafana to visualize it.

[yehosef]

I just realized that you can can already do this! You just choose the datastore as elasticsearch - you'll see the start of a query with count and date histogram. You then click on "editor mode" to see the raw query. Then you can put in whatever query you want.

Amazing! - the only thing that might be nice to add is you could lock in this mode because if I click on "switch editor mode" again to see what happens, it erases the query.

I'm closing this because it can do what I need - though something to lock the mode would be nice.

[martingoldstone]

I'm trying to use this with the new derivative pipeline aggregation in elasticsearch. Basically I'm trying to visualise the rate of change of a particular metric (in this case, I've got network rx and tx bytes, I want to graph the throughput). Unfortunately, I'm running into an issue where it looks like a) the number of metrics listed in the main query editor needs to equal the number of values being returned in the document (no data points found is the message if it doesn't match), and b) I'm getting a "TypeError: Cannot read property 'value' of undefined at c.processMetrics" error once I've got the right number of metrics. With Kibana's current inability to utilise these new aggregations, I was hoping to be able to get something with Grafana. Am I doing something wrong?

[bangert]

i am also trying to get derivative pipeline aggregation to work. the query appears to work but i cant seem to get grafana to graph the derivative instead of the avg.

@yehosef: coudl you give some insight? thanks

      "aggs": {
        "1": {
          "avg": {
            "field": "value"
          }
        },

        "3": {
          "derivative": {
            "buckets_path": "1"
          }
        }

      }

"3" is the pipeline aggregation that i added

[yehosef]

@bangert - I assume the structure of the pipeline aggregations is different. I haven't used them yet so I don't have any more ideas. If so, I don't think this will work without some other processing script.

[yehosef]

this is broken in the 2.6 release. It seems that the elasticsearch query editor use to use https://github.com/grafana/grafana/blob/v2.6.0/public/app/plugins/datasource/influxdb/partials/query.editor.html
and now it uses
https://github.com/grafana/grafana/blob/v2.6.0/public/app/plugins/datasource/elasticsearch/partials/query.editor.html
which is missing the toggleQueryMode().

was this taken out on purpose?

[bangert]

yes - it's in the changelog.

https://github.com/grafana/grafana/blob/master/CHANGELOG.md#breaking-changes-1
elasticsearch: Manual json edited queries are not supported any more (They very barely worked in 2.5)

2.6 has support for basic pipeline aggregations though.
Elasticsearch: Support for pipeline aggregations Moving average and derivative, closes #2715
Elasticsearch: Support for inline script and missing options for metrics, closes #3500

[yehosef]

thanks for the reference - I don't understand why? what does "very barely worked" mean?

Why can't it be left as an advanced option? We are using children queries which are not supported. While I could open a ticket for those, it's probably a rare use case that I don't know if anyone wants to implement. But for us, the raw query worked fine and we have dashboard built on them.

[skundrik]

👍 for bringing the edited queries back. Maybe providing more documentation on how they interface with grafana will help users to write them correctly. We used it for percentile_ranks that is not available out of the box but quite useful.

[bangert]

i dont know all ways in which advanced queries where broken. but when i tried using them with derivative pipeline aggregations it would not work, because the first bucket would not have a derivative value.

This has been fixed, when official support for moving_avg og derivative pipeline aggregations were added.

[yehosef]

So - it could be that this raw query wouldn't work here because the grafana was expecting results in a certain format and the pipeline aggregations were not in that format.

But there are other "advanced" queries that are basically simple queries, just with more complex conditions that the current (basic) query builder does not support (ie children/parent) . You shouldn't "throw out the baby with the bath water" - the raw query editor work for many situations and should still be an option. There are many places in the system I can put in something invalid and not get results - as long as it's not dangerous (ie crash the server, result in data loss), the user should be be allowed to do it.

You could put a warning about being unsupported if you want - but I don't see a reason it should be disabled.

[Ruben-E]

I totally agree to this. I needed the custom queries as well... An unsupported warning would be the best of both world in my opinion.

[sparippil]

Yes. This would be a good to have feature.

[RolphH]

I agree with @yehosef here. The customer queries are needed here too, and basically we won't upgrade to 2.6 at this moment.
Also, "script" : "inline" ....does not seem to work on my ES (1.7.3). If I remove the "inline" the query works.

[moravski]

Please add the custom queries edit mode back! It's really needed...

[teliatko]

👍 Agree with @yehosef. Mark it as experimental if you have to, but bring it back.
You shouldn't protect the user from shooting in to the leg if she will.

[brenuart]

+1 for any way to build the aggregation part of the query "myself" - should it be experimental or not. This would allow us to use Grafana for scenarios for which the standard query editor doesn't (yet) provide support.

[jimmykirk]

+1 from this guy. I would like to be able to bring in parent/child and have my parent filters applied via this method.

[mdan1eli]

Support for percentile_ranks or a free query editor would be goos. Make addition of percentile_rank a separate issue?

[ztec]

👍 could save when stuck with standard query

[jisaitua]

+1 this is the only way to use grafana and at the same time support all the powerful and complex queries that ES has to offer. We were using graphite and we migrate to ES because of their query capabilities, but sadly grafana 3.x is restricting that functionality. So please, bring edited queries back. Put a big warning that it's an untested functionality, only for power users, that may eat your cat, but please...we really need it.

[louzadod]

+1 crutial for our ops BI strategy.

[faxm0dem]

I agree: please bring back the advanced query editor

[shuoli84]

several months passed, and the query editor still not back.

So we either not using ES as data source, or have to write some script to do the complex query and bump data back...

[RedStalker]

+1
Need some analog of CONCAT function to merge result fields

[aurimasplu]

It seem that Grafana developers mostly ignores Elasticsearch and only supports basic functionally even though Elastic provides one of the richest query capabilities of time series databases.

[nicholas-fwang]

+1

[vimitris]

+1 There are multiple reasons why would someone choose Grafana over Kibana for visualizations(memory, cpu, image size etc.), but if used with ElasticSearch as a Data Store, this feature is a must to close the gap between the two and permit any kind of query/aggregation. In my mind, this feature should be the first to be implemented for any data store.

[nikhilgupta-repos]

+1 I was recently exploring to visualize some advance query results either in Kibana/Grafana. I was able to do so in Kibana using their "experimental" Vega visualizations. However I still want to do the same in Grafana. This is a much-needed feature.

[Willshield]

+1

[trifidkod]

+1

[hjmwijnen]

+1 - Just found out that the feature I was looking for already existed before and got removed :(

[alanbuxton]

+1

[Lumotheninja]

+1

[palvaroni]

+1

[Skyler-Arnold]

+1

[cameronkerrnz]

Considering how flexible it would need to be, the best way forward would likely be creating a JSON backend which would implement the query logic and data-wrangling involved. Possibly you might also have some other value-adds (eg. translating fields from identifiers to something more business-friendly --- eg. building code to a building name)

https://grafana.com/grafana/plugins/simpod-json-datasource

[FireByTrial]

same here, could have really used this. will have to script out creating the chart now (report is needed in a few days). so a +1 here as well

[aocenas]

If I understand this issue we had raw query mode before and it was working with some caveats but then we removed it at some point so now some advanced queries are unavailable in Grafana.

[astronights]

Please bring back raw query mode. Was really helpful.

[dk-data]

This is a very valuable feature, please bring it back!

[jessover9000]

Hi everyone, thank you so much for your interest in this feature! The Grafana UX team is working on improving query editors in general, and we've been collecting user feedback about various data sources. We want to dedicate the upcoming UX community call to discuss this issue and learn more about your needs in a good Elastic code editor. The call will happen next Monday at 5pm UTC (find the agenda and zoom link here). Hopefully some of you can make it, we'd love to hear about your experience as we work on improvements.

[dk-data]

@jessover9000 Great to see engagement with the community on this issue 👍

[MrPowerGamerBR]

+1 Grafana's ElasticSearch support is so poor, that if you never used ElasticSearch directly via its API, you would think that ElasticSearch sucks and can't do any complex queries like aggregation because Grafana doesn't support it.