Desire add support for using the $_interval variable to group by Interval selections in dashboard

[verticacrossman]

Customer reported: Trying to use the Grafana Interval in graph to dynamically group by the data shown by the intervals specified using the global interval variable in the dashboard. They use this with Elastic Search and want to use with Vertica as well. It appears from grafana docs and tests that this has to be specifically implemented as there are only specific databases that currently support it.

=========

$interval You can use the $interval variable as a parameter to group by time (for InfluxDB, MySQL, Postgres, MSSQL), Date histogram interval (for Elasticsearch), or as a summarize function parameter (for Graphite).

Grafana automatically calculates an interval that can be used to group by time in queries. When there are more data points than can be shown on a graph then queries can be made more efficient by grouping by a larger interval. It is more efficient to group by 1 day than by 10s when looking at 3 months of data and the graph will look the same and the query will be faster. The $__interval is calculated using the time range and the width of the graph (the number of pixels).

Approximate Calculation: (from - to) / resolution

For example, when the time range is 1 hour and the graph is full screen, then the interval might be calculated to 2m - points are grouped in 2 minute intervals. If the time range is 6 months and the graph is full screen, then the interval might be 1d (1 day) - points are grouped by day.

In the InfluxDB data source, the legacy variable $interval is the same variable. $__interval should be used instead.

The InfluxDB and Elasticsearch data sources have Group by time interval fields that are used to hard code the interval or to set the minimum limit for the $__interval variable (by using the > syntax -> >10m)

=========

I found the following whihc seems like it might be related, but not currently implemented in the Vertica Grafana plugin. $__timeGroup macro not implemented but might be what's missing.

• $timeGroup(column,'5m'[, fillvalue]) -> (extract(epoch from column)/300)::bigint*300 (NOT YET IMPLEMENTED) by setting fillvalue grafana will fill in missing values according to the interval fillvalue can be either a literal value, NULL or previous; previous will fill in the previous seen value or NULL if none has been seen yet Example of group by and order by with $timeGroup: SELECT $__timeGroup(date_time_col, '1h'), sum(value) as value FROM yourtable GROUP BY time ORDER BY time

[rajsameer]

@verticacrossman , i recently created a PR to migrate this plugin to support grafana 7.4. In that we can use $_invertval_ms to acive this. Following is an example. SELECT time_slice(end_time, $__interval_ms, 'ms', 'end') as time , node_name, avg(average_cpu_usage_percent) FROM v_monitor.cpu_usage WHERE end_time > TO_TIMESTAMP($__from/1000) and end_time < TO_TIMESTAMP($__to/1000) GROUP BY 1, 2 ORDER BY 1 asc

I closed the PR since I did not get a response. So i created a new vertica data source and submitted to Grafna. Following is the link to repo. https://github.com/rajsameer/vertica-datasource I you guys wish i can recreate the PR and add the new change.

[verticacrossman]

Sameer. Thanks. I'll take a look at your version. I assume your changes were to upgrade it to use the new framework? Did you also add dataframe support or just get the basic framework updates?

[rajsameer]

@verticacrossman , yes my changes where to upgrade to new frame work. Yes, it uses the data frame as advised by grafana. I have tried to use most of the recommendation given by grafana. My main goal is to add UI for query builder, so it becomes easy for non sql guys to create simple queries, something similar to influx. I my repo you have two release v1.0.0 and unsinged-v1.0.0 , please use https://github.com/rajsameer/vertica-datasource/releases/download/unsinged-v1.0.0/rajsameer-vertica-datasource-1.0.0.zip. Because the v1.0.0 is singed with a private signature.

[verticacrossman]

I was able to pull down and get running. Looks like you based this on the 0.1.0 release? There were some fixes and enhancements added in release 0.2.0 that do not show up in you version. For instance, "Use client load balancing" environment option missing and "Use Prepared Statements" is a slider vs checkbox. A dash I have that uses variables and multi-select to allow pull down selections and single panel per selected option is not working and that was a bug fix to handling variables.

[rajsameer]

client load balancing is used, when you configure the data source the load balancing parameter is passed to the vertica client. the new variable implementation was done to support use value and display value in the variable.