How to query Timestream for several different measure_name and their values

Question

Hi,  
  
Timestream is great in many ways, but seems to be hard/different to work with if you want to query several different measurements in the same query. This is because of the "flat" data model that Timestream uses.  
  
If we use the example dataset found in the documentation at   
https://docs.aws.amazon.com/timestream/latest/developerguide/sample-queries.devops-scenarios.html  
  
How can I write a query that returns the four columns   
time,   
hostname,   
(measure_value::double for) cpu_utilization and   
(measure_value::double for) memory_utilization?  
  
The CREATE_TIME_SERIES function only seems to work with one measure_name?  
  
Thanks.

Answer

To answer my own question:  
  
One way to do this is with a following type of query:  
  
-- Get data points for both CO2 and humidity  
SELECT   
    time,   
    MAX(if(measure_name = 'CO2', measure_value::double)) AS CO2_ppm,  
    MAX(if(measure_name = 'humidity', measure_value::double)) AS humidity_percent  
FROM "MyTimestreamDB"."myTable"   
WHERE   
    time >= ago(10m)  
	AND measure_name IN ('CO2', 'humidity')  
    AND deviceId='6a087171-400c-4851-a82b-ceab383b421c'  
GROUP BY time  
ORDER BY time DESC

How to query Timestream for several different measure_name and their values

Contenido relevante