CODEWITHSUNDEEP
sqlamazon-web-servicestime-seriesamazon-timestream
Angel Bates
Angel Bates

Reputation: 23

AWS Timestream: Given correlated subquery is not supported

select host, time as currentTime, floor((1 - (
  select sum(measure_value::double)
    from "DendroTimestreamDB"."hostMetrics"
    where cpuMode = 'idle'
    and time <= h.time
    group by host
) / ((
  select sum(measure_value::double)
    from "DendroTimestreamDB"."hostMetrics"
    where cpuMode = 'idle'
    and time <= h.time
    group by host
) + (
  select sum(measure_value::double)
    from "DendroTimestreamDB"."hostMetrics"
    where cpuMode = 'system'
    and time <= h.time
    group by host
) + (
  select sum(measure_value::double)
    from "DendroTimestreamDB"."hostMetrics"
    where cpuMode = 'user'
    and time <= h.time
    group by host
))) * 100) as utilization
from "DendroTimestreamDB"."hostMetrics" h
group by host, time

returns the error line 20:3: Given correlated subquery is not supported but according to Timestream subquery support

The Timestream query language supports correlated and other subqueries.

Columns:

cpuMode, host, device, cpuCode, collector, measure_value::double, measure_name, time,

Sample data:

idle, MacBook-Pro.local, -, 0, cpu, 115950.13, cpu_seconds_total, 2021-04-29 13:46:11.000000000

Desired Output:

host, time, utilization

MacBook-Pro.local 2021-04-29 13:47:56.000000000 15

MacBook-Pro.local 2021-04-29 13:47:41.000000000 16

MacBook-Pro.local 2021-04-29 13:47:26.000000000 19

I'm trying to calculate CPU utilization using the formula (1 - idleTime / totalTime) * 100 but apparently, these correlated subqueries aren't supported. Do I just have to rewrite it in a different way?

In the summation subqueries I'm trying to calculate the sum of the measure values that were received previous to the time from the main query and I'm doing this using the lines and time <= h.time causing the queries to be correlated and thus the issue.

Thanks a ton

Upvotes: 1

Views: 814

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I'm pretty sure you want conditional aggregation. I can't really follow the logic, but the components would appear to be something like this:

select host, time as currentTime,
       sum(sum(case when cpuMode = 'idle' then measure_value::double)) over (partition by host order by time)
       sum(sum(case when cpuMode = 'system' then measure_value::double)) over (partition by host order by time)
       sum(sum(case when cpuMode = 'user' then measure_value::double)) over (partition by host order by time)
from "DendroTimestreamDB"."hostMetrics" h
group by host, time

Upvotes: 1

Related Questions