Vertica integration with Grafana
DieterC
Employee
Hello Forum,
we have received these questions on the integration of V/Grafana. Any help is most welcome.
Thank you
Dieter
---------------------------------------------------------------------------------->
attached the questions regarding the Vertica Plugin for Grafana.
Maybe you can address them internally.
On the official Grafana download page a Vertica Plugin in Version 0.1.0 is available.
On the page https://www.vertica.com/kb/Grafana_CG/Content/Partner/Grafana_CG.htm there is a link to the GitHub with a higher version:
https://github.com/vertica/vertica-grafana-datasource/releases/tag/v0.2.0
For both versions we see following issues:
$__unixEpochFilter(column) does not work
The time replaced in the SQL is for milliseconds: ‘ta_period_epoch >= 1615946051794 AND ta_period_epoch <= 1615967651794’
Not in seconds like for example for Postgres: ‘ta_period_epoch >= 1615946694 AND ta_period_epoch <= 1615968294’Selection of multivalues is not supported SQL IN is not possible:
In Postgres it can be defined as ‘cma1.Node_Name in ($nodeName01)’
This results in following query for Vertica:
SELECT * FROM VIEW WHERE nodeName in ({nodeName1, nodeName2})
- Queries cannot be disabled:
Even if the disabling is activated the SQL is executed anyway.
- Missing a query builder like for other DBs
Thank you for forwarding this internally.
Answers
Dieter,
I'll take a look at the technical issues. Regarding the plugin on Grafana site vs our Github site, the one on the Grafana site was submitted by a 3rd party unrelated to Vertica. We requested Grafan remove it as it was based on an old version, and recently they changed the owner to Micro Focus vs removing it. We are still trying to get the removal done so there is only the Github location to get the plugin.
Dieter,
Below please find my answers based on some research and testing I did today. Note that because this is open source the end user issues and requests should be logged on the vertica-grafana-datasource Github page so they are tracked properly.
$__unixEpochFilter(column) does not work
The time replaced in the SQL is for milliseconds: ‘ta_period_epoch >= 1615946051794 AND ta_period_epoch <= 1615967651794’
Not in seconds like for example for Postgres: ‘ta_period_epoch >= 1615946694 AND ta_period_epoch <= 1615968294’
I think I reproduced this. I'm not sure if this is intentional or not. This should be logged by the user as an issue on the vertica-grafana-datasource plugin Github page.
in Vertica
dbadmin=> select c1,c2,c3 from timetest_epoch;
c1 | c2 | c3
------------+-------+----
1616357256 | user1 | 10
1616443657 | user2 | 7
1616357256 | user1 | 5
in Grafana table panel query
select $__time(c1),c2,c3 from timetest_epoch where $__unixEpochFilter(c1);
Grafana query inspector expand all
Object
request:Object
url:"api/tsdb/query"
method:"POST"
data:Object
from:"1616422860084"
to:"1616444460084"
...
Results:Object
A:Object
refId:"A"
meta:Object
rowCount:0
sql:"select c1 AS time,c2,c3 from timetest_epoch where c1 >= 1616422860084 AND c1 <= 1616444460084;"
Selection of multivalues is not supported SQL IN is not possible:
In Postgres it can be defined as ‘cma1.Node_Name in ($nodeName01)’
This results in following query for Vertica:
SELECT * FROM VIEW WHERE nodeName in ({nodeName1, nodeName2})
You have to use the v .2.0 plugin and leverage the $__expandMultiString macro.
See the query.editor.html in the plugin partials directory:
I have an example where I have an orders table with cols order_date and client
I set up a dashboard variable "myclient" with definition "select client from orders"
Then in my panel query I use the query below noting the expandMultiString and variable vs column name
SELECT order_date, client FROM orders where client in ($__expandMultiString('${myclient}')) order by
order_date,client
This gives me a select pull down in the panel that gives me the ability to select one, multiple, or all clients as a filter
See multiselect.jpg attachment
Queries cannot be disabled:
Even if the disabling is activated the SQL is executed anyway.
Reproduced. Disable/enable toggle seems to be recognized by the panel as seen to the right of the Query letter, but has no effect on display or not of data. I tried a work in progress CSV plugin from the Grafan site and got same results. Of interest there is a Pull request on Grafana Github "Add functionality to show/hide query row results #19794" that is related to this feature and indicates it might have been introduced in Grafana 6.5 and people had used it with Loki and Prometheus, so it may be the plugin has to be coded to support it. This should be logged by the user as an issue on the vertica-grafana-datasource plugin Github page.
Missing a query builder like for other DBs
There was no requirement for a query builder in the original plugin specs. The plugin is open source and users are free to download the code and enhance as desired. And if a user creates an enhancement they think would be of value to the user community they can submit it to the Github page for review and possible merge into the distribution. Alternatively the user can submit a request for future enhancement through the Github page.
Regards,
Thank you Stephen,
these are great answers.
Dieter
Dieter,
Quick question regarding one of these items. We're planning out some enhancements to the plugin once we get the framework updated. That new framework allows for the possibility to create a query builder. Looking at the Grafana docs it appears there is no general QB, instead each plugin author has to code it to their specific database and desired functionality. And looking at some of the existing plugin query builders it's evident they vary greatly. So, I was wondering if you had any specific requirements from any customers who had brought up the lack of a query builder, or if they just generalized. This will help us define what we might do in the future related to implementing a QB. Thanks.
Hi Stephen,
at this point the customer is fine with the answers you gave. We have no specific requirements.
If you are wanting this you can approach the customer for more insight on how the use the V plugin and on planned projects.
Just write me an email for this.
Thank you
Dieter