Aggregating Counts in AWS Timestream Error Causes Errors

Question

I have telemetry being pushed to AWS timestream:

measure_value::varchar	IP	time	measure_name
test.html	192.168.1.100	2021-05-25 14:27:45	hits
blah.html	192.168.1.101	2021-05-25 14:27:45	hits
test.html	192.168.1.102	2021-05-25 14:27:46	hits

I want to have aggregates of the data displayed in timestream showing me how many hits for each uri we had for each hour.

measure_value::varchar	Count	time
test.html	2	2021-05-25 14:00
blah.html	1	2021-05-25 14:00

I am trying to use:

SELECT measure_value::varchar as URIs, CREATE_TIME_SERIES(time, measure_value::varchar) AS served FROM $__database.$__table WHERE $__timeFilter group by measure_value::varchar

but I'm getting the error:

ValidationException: Duplicate timestamps are not allowed in a timeseries.

Am I using the wrong function or is my data wrong?

===================

Trying @berto99's solution... I get:

SELECT measure_value::varchar AS URIs, date_trunc('hour', time) AS hour, count(measure_value::varchar) as queries
FROM $__database.$__table
WHERE $__timeFilter
GROUP BY measure_value::varchar, date_trunc('hour', time)

=====================

Update #2:

Getting there, still not 100% there.

SELECT measure_value::varchar AS URIs, bin(time, 15m) AS hour, count(measure_value::varchar) as queries
FROM $__database.$__table
WHERE $__timeFilter
GROUP BY measure_value::varchar, bin(time, 15m) order by hour

Hi, I've just deleted a comment, because I had the same issue weeks ago, and the problem was that the result set was not ordered by time, so you should order that by the hour (check my answer, I've updated it) — Alberto Sinigaglia, May 27 '21 at 14:54
However, I think you should set the entity to `measure_value::varchar` in the graph options, so that It plots a different line for each "web page" — Alberto Sinigaglia, May 27 '21 at 14:55

score 3 · Accepted Answer · answered May 27 '21 at 16:26

Finally got this all working with a combo of @Berto99's suggestions and more digging on stackoverflow - TimeStream + Grafana: not recognizing series in data.

You have to put Berto99's suggestion into a subquery then run it through CREATE_TIME_SERIES. The final query ended up being:

WITH binned_query AS (
SELECT measure_value::varchar AS URIs, bin(time, 15m) AS bin_time, count(measure_value::varchar) as queries
FROM $__database.$__table
WHERE $__timeFilter
GROUP BY measure_value::varchar, bin(time, 15m) order by bin_time
)
SELECT URIs, CREATE_TIME_SERIES(bin_time,queries) as Endpoint
FROM binned_query 
GROUP BY URIs

Changed from using date_trunc to bin as it gives you more flexibility to do 15 minute intervals.

Beautiful graph:

Alberto Sinigaglia · Answer 2 · 2021-05-27T14:52:20.743

1

I know that there are probably better ways to do this, but something like this:

SELECT measure_value::varchar AS URIs, date_trunc('hour', time) AS time
FROM $__database.$__table
WHERE $__timeFilter
GROUP BY measure_value::varchar, date_trunc('hour', time)
ORDER BY date_trunc('hour', time)

And maybe you will have to adjust also the timezone with date_trunc('hour', time at time zone '-X') where X is your timezone

edited May 27 '21 at 14:52

answered May 26 '21 at 23:13

Alberto Sinigaglia

12,097
2
20
48

This works but grafana is not able to pick up each "uri" and produce a stacked graph. I believe it is just showing data from a single URI. Any ideas? – PressingOnAlways May 27 '21 at 16:00

Aggregating Counts in AWS Timestream Error Causes Errors

2 Answers2