athena-datasource

athena-datasource copied to clipboard

Is your feature request related to a problem? Please describe. It might be not the place for this but on grafana community forums I was softly sent to athena datasource, so feel free to redirect if needed. So as a part of my query I want to count whole minutes between $__rawTimeFrom and $__rawTimeTo.

Describe the solution you'd like I saw some solutions like using unix_timestamp or other tricks, but wasn't able to succeed.

Describe alternatives you've considered None

Additional context I select the truncated by minute distinct entries between From and To and then I want to divide them to a whole number of minutes in this interval, this will give me percentage I want

Mar 14 '24 14:03 olekspickle

hi @alekspickle I believe this should do it: SELECT date_diff('minute', parse_datetime($__rawTimeFrom, 'yyyy-MM-dd HH:mm:ss'), parse_datetime($__rawTimeTo, 'yyyy-MM-dd HH:mm:ss')) Let us know how that works for ya

Mar 14 '24 20:03 sarahzinger

this works, but for some reason does not get through in final query. This is what I ended up:

with truncated as (
  SELECT DISTINCT DATE_TRUNC('minute', "timestamp") as timestamp
  FROM "table"
  WHERE id = '${id}' AND
  date BETWEEN $__rawTimeFrom('yyyyMMdd') AND $__rawTimeTo('yyyyMMdd')
  ORDER BY timestamp ASC
),diff as (
  SELECT date_diff('minute', parse_datetime($__rawTimeFrom, 'yyyy-MM-dd HH:mm:ss'), parse_datetime($__rawTimeTo, 'yyyy-MM-dd HH:mm:ss')) as total_minutes
)
-- this works
SELECT count(timestamp) as total_actual from truncated,diff;
-- as well as this
SELECT total_minutes from truncated,diff;

-- but bringing it together is not, the following triggers an error
SELECT count(timestamp) as total_actual/total_minutes * 100 from truncated,diff;

error querying the database: error executing query: InvalidRequestException: Only one sql statement is allowed. Got: with truncated as ( SELECT DISTINCT DATE_TRUNC('minute', "timestamp") as timestamp FROM "table_name" WHERE device_id = '0' AND date BETWEEN '20240314' AND '20240315' ORDER BY timestamp ASC ),diff as ( SELECT date_diff('minute', parse_datetime('2024-03-14 11:11:48', 'yyyy-MM-dd HH:mm:ss'), parse_datetime('2024-03-15 11:11:48', 'yyyy-MM-dd HH:mm:ss')) as total_minutes )  SELECT count(timestamp) as total_actual/total_minutes * 100 from truncated,diff; { RespMetadata: { StatusCode: 400, RequestID: "93328d62-de37-4e66-ad53-b67e38d3ddd5" }, AthenaErrorCode: "MALFORMED_QUERY", Message_: "Only one sql statement is allowed. Got: \nwith truncated as (\n SELECT DISTINCT DATE_TRUNC('minute', \"timestamp\") as timestamp\n FROM \"table_name\"\n WHERE device_id = '0' AND\n date BETWEEN '20240314' AND '20240315'\n ORDER BY timestamp ASC\n),diff as (\n SELECT date_diff('minute', parse_datetime('2024-03-14 11:11:48', 'yyyy-MM-dd HH:mm:ss'), parse_datetime('2024-03-15 11:11:48', 'yyyy-MM-dd HH:mm:ss')) as total_minutes\n)\nSELECT count(timestamp) as total_actual/total_minutes * 100 from truncated,diff;" }

Mar 15 '24 11:03 olekspickle

The error you're getting is just what is getting proxied from Athena, not grafana. In this case it seems to think you're making more than one sql statement at a time.

I wonder if it would be best to reach out to the AWS Athena to see if they can help you write the query you need? It might make sense to first write the query you want with hard coded strings for times and then replace those with the macros. This way you can solve these 2 separate problems one at a time (writing a sql query, and then integrating that query with dynamic values from grafana's macros)

You may wish to the the Query inspector and click "data" to help debug your queries. For example, if I wanted to know what format rawTimeFrom is returning I could do this and then when I hover over the column I can see that the format for this value is a string (also the A icon means string)

So now I can copy and paste that value as a string in the query when I'm trying to write the query without grafana macros to ensure I have a valid sql query first.

Mar 15 '24 12:03 sarahzinger

ok, I'll keep playing with it, thx for tips!

Mar 15 '24 13:03 olekspickle

I see, the line with date_diff returns a row for each minute and it always the same(duh). I'm trying to slap distinct somewhere but it keeps failing. Also I have a feeling that Error tab in query explorer is cached somehow: it keeps showing me some much older query, who's fault must that be? is it plugin, grafana or athena itself?

Mar 15 '24 14:03 olekspickle

Hi, @alekspickle - sorry for the slow response on this one. Is this something you still need help with?

Aug 06 '24 08:08 njvrzm

So basically I wanted to calculate the ratio of the minutes I have datapoints and the whole monutes calculated between $__rawTimeFrom and $__rawTimeTo

but I moved from an idea to do it with grafana and ES lucene does not make it easier, so I guess we are done here

Aug 06 '24 09:08 olekspickle