Vertica and upgrading Grafana v7 to v9
I've got a new Grafana server running v9, and now installed the grafana plugin. Previously, I was using SQL Server linked servers to hop to Vertica.
Trying to go to Vertica directly is problematic.
1) I have an sql with "time BETWEEN $__timeFrom() and $__timeTo()", but it just doesn't work. I get an error "macro __timeFrom should have no arguments". I've tried all combinations. The only thing that works is to replace with "$__timeFilter(time)", which works in this case, but not others.
2) I also use the following : -
SELECT
CASE
WHEN '$granularity' = 'min' THEN $__timeGroup(time, '1m', NULL)
WHEN '$granularity' = 'hour' THEN $__timeGroup(time, '1h', NULL)
END AS time,
but this also fails with "ERROR 4856: [42601] Syntax error at or near "as""
Any help would be appreciated.
Answers
Can you specify the exact version of Grafana (v7) that you were using earlier? And, did you upgrade to v9.0 or any other? There have been a lot of changes in the Grafana versions so I would try to reproduce it as your environment. Let me know. Thanks
Hi tpise, I'm running grafana versions v7.1.1 and v9.5.3. It wasn't an upgrade. I have 2 boxes.
Did you get anywhere with this?
Hi,
1. If you want to use BETWEEN clause, you can use:
$__timeFilter(column) - Adds time range filter on the specified column. For example, column BETWEEN '2017-04-21T05:01:17Z' AND '2017-04-21T05:01:17Z'.
$__timeGroup() macro can also help in your case.
Whereas $__timeTo() and $__timeFrom() macros would not accept arguments and can only take current timestamps. Attaching the link for supported macros and their definitions for your reference https://github.com/vertica/vertica-grafana-datasource#supported-macros
For the CASE clause in your SQL statement, is that a complete statement?
Also, can you provide the definition for the variable '$granularity' ?
Hi tpise
I'm using $__timeFilter(time) in most cases. Just surprised $__timeFrom() and $__timeTo() don't work. They work for SQL Server.
I was previously making Vertica calls via a SQL Server Linked Server using OPENQUERY, but as the Data Source was SQL Server it was working.
The SQL Server version of $__timeFrom() and $__timeTo() was also lax on allowing $__timeFrom(time).
Also, I'm not using $granularity any more.