I am using Grafana version 6.5.0 and I have installed “Presto plugin” to connect to Trino (Note that since the Grafana version I am using is 6, I cannot use the Trino plugin because it requires Grafana version 8 and above.)
presto plugin link: https://github.com/sabarishs/presto-grafana
I have installed the plugin from the above link and when I want to add a data source as shown in the image below, I encounter an error:
HTTP Error Bad Gateway
When I put “Access” on the browser, I don’t encounter any errors, but when I use this data source in the dashboard and query Trino, no data is displayed in the output.
(Our trino version is 377, and I also use basic auth. for security reasons, I can’t add a picture of my exact datasource settings.)
hi @mikhailvolkov, Can you help me as always?
@zahrazare313 Presto data source is 3 years old, based on Angular framework and no one is supporting it. I would not recommend using it.
I would upgrade Grafana to v8 at least to use Trino.
hi @mikhailvolkov, I did the same thing and installed the Trino plugin on Grafana 9, but the problem is that the performance of my dashboard that directly queries Trino is deficient, for example, it takes at least 40 minutes for a time range of 7 days. is there a solution that improves the performance of this particular dashboard
How long does the same query take if you ran it on trino itself via web client?
Less than thirty seconds!
very worrisome. So a query with time range of 7 days takes 40 minutes plus in grafana but 30 seconds in trino itself?
I need to correct the information I gave you:
When I run a query in Trino without a where clause, it takes a lot of time, with a where clause it takes a few seconds. Similarly, when I run a query without any where clause in Grafana, it seems that regardless of the time range, it loads all the data from the trino first, then in the panel, it shows the data of the desired time range, that’s why it takes a long time, but when I use the clause “where DATE_KEY between 20230829 and 20230904” in the query panel, the loading time of the panel is greatly reduced. But the problem is that this expression is fixed and I want the user to decide what data to see in the panel by selecting the time in the time range.
So grafana is not taking longer than trino. they are pretty comparable.
Now on to your where clause issue. Your
DATE_KEY column is numeric or integer data type column? If so in order for the query to be dynamic you will need to convert the
_to date ranges to be same format as
DATE_KEY using one of the following date functions.
declare @from = date_parse(_from, '%Y/%m/%d');
declare @to= date_parse(_to, '%Y/%m/%d');
select * from kimchi
where DATE_KEY between @from and @to
kind of things, not sure about the date parsers in trino, you will have to fill in the blanks
Yes, the type of the DATE_KEY column is numeric, and in order to use the time series, I use the “Convert Field Type” transformation and first change the type to string, then to time. Honestly, I didn’t understand your advice, can you explain more?
you want to use the selected time in your
WHERE clause right? And the
DATE_KEY column is numeric.
You cant use the time ranges because they are not numeric. you need to convert them to numeric
I’m just looking for a solution to load my dashboard My dashboard is impacting everything…if I convert the time range to a numeric, I can’t use the time series visualization anymore, I want to display the trend of the data on the dashboard, so is it not possible I use special macros?
I used $__dateFilter(DATE_KEY) but I get an error
All the recommendations offered should solve your issue.
What is rexommended is for you to change to numeric in the where clause only, not on the select.
You can use macros but read up the documentation on them. They cant use numeric values which is what your DATE_KEY is
hi @yosiasz , I hope you are well, I apologize for the delay in replying, can you tell me what exactly to do?
Sorry but cannot add anything more than what has already been said?
To repeat what has already been said a few rimes before
Use a function in trino to convert the grafana filter dates to numeric value and use them in the where clause of your query with DATE_KEY
I tried to use your suggested solution but I am facing the following error.
declare @from = CAST(date_format(TIMESTAMP __from, ‘%Y-%m-%d’) as integer);
declare @to = CAST(date_format(TIMESTAMP __to, ‘%Y-%m-%d’) AS integer);
SELECT * FROM kimchi
WHERE DATE_KEY between @from and @to
GROUP BY DATE_KEY
error querying the database: trino: query failed (200 OK): “io.trino.sql.parser.ParsingException: line 1:1: mismatched input ‘declare’. Expecting: ‘ALTER’, ‘ANALYZE’, ‘CALL’, ‘COMMENT’, ‘COMMIT’, ‘CREATE’, ‘DEALLOCATE’, ‘DELETE’, ‘DENY’, ‘DESC’, ‘DESCRIBE’, ‘DROP’, ‘EXECUTE’, ‘EXPLAIN’, ‘GRANT’, ‘INSERT’, ‘MERGE’, ‘PREPARE’, ‘REFRESH’, ‘RESET’, ‘REVOKE’, ‘ROLLBACK’, ‘SET’, ‘SHOW’, ‘START’, ‘TRUNCATE’, ‘UPDATE’, ‘USE’, ”
Notice what I said here. Kind of things. Try that same query in trino web sql query and sort it out .
Are you familiar with trino query language?
I haven’t worked with it before, it’s the first time I’m making a dashboard with Trino data source:(
Then I would encourage you to read up the documentation and try out you trino queries on trino first then apply it in grafana.
Also if at all possible use latest grafana v6 is very old
I am currently using Grafana 9.