TimeStream + Grafana: not recognizing series in data

Question

Jumping on AWS Timestream, I've some trouble with the grafana integration:

I've build a query that returns a count of events grouped by day and "thing" - and would like to show that in a graph. Doesn't even matter which one.

In a table, the data is displayed fine, and it can also be graph'd - but there is no recognition of the series - so all data is shown in one series.

Here is the SQL query:

SELECT BIN(time,1d) AS "time",dimension_name AS "series",count(measure_name) AS "metric" 
FROM "event"."data" 
WHERE "measure_name" = 'code' AND $__timeFilter 
GROUP BY BIN(time,1d),dimension_name 
ORDER BY 1

Here is an excerpt of the data:

What can I do so that grafana recognizeses the dimension_name as the denominator for a series?

Answer 1

Okay, after some more digging, I think I found the right thing on the (sparse) documentation. Subqueries are the answer!

WITH binned_query AS (
SELECT BIN(time,1d) AS "day_time",
dimension_name AS "series",count(measure_name) AS "metric" 
FROM "event"."data" WHERE "measure_name" = 'code' 
AND $__timeFilter 
GROUP BY BIN(time,1d),dimension_name order by 1
)
SELECT series, CREATE_TIME_SERIES(day_time,metric) 
FROM binned_query 
GROUP BY series

Displays beautifully in grafana:

Answer 2

This solution works only if you don't have a lot of data points. If I filter for the last days i'm fine, but if I would like to get the last 30 days I'm getting an error from the AWS TimeStream: "Query aborted as max page response size has been exceeded by the output result row". The fact is that I'm trying to group 40247 data points.

Any help will be much appreciated. I have to say that I first used MySQL and it worked easily, when I moved to AWS TimeStream I facing some issues.

Answer 3

In modern version of Grafana, you can use the Transform Prepare Time Series with Multi-frame time series selected to resolve the issue you're experiencing.

This transform will identify the numeric columns and string columns returned from the datasource, and use the string columns as labels.

You'll be able to use your Timestream query as initially written once this transform is applied.