Interrogazioni con funzioni di serie temporali

Set di dati e interrogazioni di esempio

È possibile utilizzare Timestream per LiveAnalytics comprendere e migliorare le prestazioni e la disponibilità dei servizi e delle applicazioni. Di seguito è riportato un esempio di tabella e delle query di esempio eseguite su tale tabella.

La tabella ec2_metrics memorizza i dati di telemetria, come l'utilizzo della CPU e altre metriche delle istanze. EC2 È possibile visualizzare la tabella riportata di seguito.

Orario	Regione	az	Hostname (Nome host)	measure_name	measure_value::double	measure_value::bigint
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1a	front-end 01	cpu_utilization	35.1	null
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1a	front-end 01	memory_utilization	5.3	null
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1a	front-end 01	network_bytes_in	null	1.500
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1a	front-end 01	network_bytes_out	null	6.700
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1b	front-end 02	cpu_utilization	38,5	null
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1b	front-end 02	memory_utilization	58,4	null
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1b	front-end 02	network_bytes_in	null	23.000
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1b	front-end 02	network_bytes_out	null	12.000
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1c	front-end 03	cpu_utilization	45.0	null
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1c	front-end 03	memory_utilization	65,8	null
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1c	front-end 03	network_bytes_in	null	15.000
2019-12-04 19:00:00.000 000000	us-east-1	us-east-1c	front-end 03	network_bytes_out	null	836.000
2019-12-04 19:00:05.000 000000	us-east-1	us-east-1a	front-end 01	cpu_utilization	55.2	null
2019-12-04 19:00:05.000 000000	us-east-1	us-east-1a	front-end 01	memory_utilization	75.0	null
2019-12-04 19:00:05.000 000000	us-east-1	us-east-1a	front-end 01	network_bytes_in	null	1.245
2019-12-04 19:00:05.000 000000	us-east-1	us-east-1a	front-end 01	network_bytes_out	null	68.432
2019-12-04 19:00:08000 000000	us-east-1	us-east-1b	front-end 02	cpu_utilization	65,6	null
2019-12-04 19:00:08000 000000	us-east-1	us-east-1b	front-end 02	memory_utilization	85,3	null
2019-12-04 19:00:08000 000000	us-east-1	us-east-1b	front-end 02	network_bytes_in	null	1.245
2019-12-04 19:00:08000 000000	us-east-1	us-east-1b	front-end 02	network_bytes_out	null	68.432
2019-12-04 19:00:20000 000000	us-east-1	us-east-1c	front-end 03	cpu_utilization	12.1	null
2019-12-04 19:00:20000 000000	us-east-1	us-east-1c	front-end 03	memory_utilization	32,0	null
2019-12-04 19:00:20000 000000	us-east-1	us-east-1c	front-end 03	network_bytes_in	null	1.400
2019-12-04 19:00:20000 000000	us-east-1	us-east-1c	front-end 03	network_bytes_out	null	345
2019-12-04 19:00:10.000 000000	us-east-1	us-east-1a	front-end 01	cpu_utilization	15.3	null
2019-12-04 19:00:10.000 000000	us-east-1	us-east-1a	front-end 01	memory_utilization	35,4	null
2019-12-04 19:00:10.000 000000	us-east-1	us-east-1a	front-end 01	network_bytes_in	null	23
2019-12-04 19:00:10.000 000000	us-east-1	us-east-1a	front-end 01	network_bytes_out	null	0
2019-12-04 19:00:16.000 000000	us-east-1	us-east-1b	front-end 02	cpu_utilization	44.0	null
2019-12-04 19:00:16.000 000000	us-east-1	us-east-1b	front-end 02	memory_utilization	64.2	null
2019-12-04 19:00:16000 000000	us-east-1	us-east-1b	front-end 02	network_bytes_in	null	1.450
2019-12-04 19:00:16000 000000	us-east-1	us-east-1b	front-end 02	network_bytes_out	null	200
2019-12-04 19:00:40.000 000000	us-east-1	us-east-1c	front-end 03	cpu_utilization	6.4	null
2019-12-04 19:00:40,000 000000	us-east-1	us-east-1c	front-end 03	memory_utilization	86,3	null
2019-12-04 19:00:40,000 000000	us-east-1	us-east-1c	front-end 03	network_bytes_in	null	300
2019-12-04 19:00:40.000 000000	us-east-1	us-east-1c	front-end 03	network_bytes_out	null	423

Calcola l'utilizzo medio della CPU p90, p95 e p99 per un EC2 host specifico nelle ultime 2 ore:

SELECT region, az, hostname, BIN(time, 15s) AS binned_timestamp,
    ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization,
    ROUND(APPROX_PERCENTILE(measure_value::double, 0.9), 2) AS p90_cpu_utilization,
    ROUND(APPROX_PERCENTILE(measure_value::double, 0.95), 2) AS p95_cpu_utilization,
    ROUND(APPROX_PERCENTILE(measure_value::double, 0.99), 2) AS p99_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
    AND hostname = 'host-Hovjv'
    AND time > ago(2h)
GROUP BY region, hostname, az, BIN(time, 15s)
ORDER BY binned_timestamp ASC

Identifica EC2 gli host con un utilizzo della CPU superiore del 10% o più rispetto all'utilizzo medio della CPU dell'intero parco macchine nelle ultime 2 ore:

WITH avg_fleet_utilization AS (
    SELECT COUNT(DISTINCT hostname) AS total_host_count, AVG(measure_value::double) AS fleet_avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND time > ago(2h)
), avg_per_host_cpu AS (
    SELECT region, az, hostname, AVG(measure_value::double) AS avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND time > ago(2h)
    GROUP BY region, az, hostname
)
SELECT region, az, hostname, avg_cpu_utilization, fleet_avg_cpu_utilization
FROM avg_fleet_utilization, avg_per_host_cpu
WHERE avg_cpu_utilization > 1.1 * fleet_avg_cpu_utilization
ORDER BY avg_cpu_utilization DESC

Trova l'utilizzo medio della CPU suddiviso a intervalli di 30 secondi per un EC2 host specifico nelle ultime 2 ore:

SELECT BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
    AND hostname = 'host-Hovjv'
    AND time > ago(2h)
GROUP BY hostname, BIN(time, 30s)
ORDER BY binned_timestamp ASC

Calcola l'utilizzo medio della CPU a intervalli di 30 secondi per un EC2 host specifico nelle ultime 2 ore, inserendo i valori mancanti utilizzando l'interpolazione lineare:

WITH binned_timeseries AS (
    SELECT hostname, BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND hostname = 'host-Hovjv'
        AND time > ago(2h)
    GROUP BY hostname, BIN(time, 30s)
), interpolated_timeseries AS (
    SELECT hostname,
        INTERPOLATE_LINEAR(
            CREATE_TIME_SERIES(binned_timestamp, avg_cpu_utilization),
                SEQUENCE(min(binned_timestamp), max(binned_timestamp), 15s)) AS interpolated_avg_cpu_utilization
    FROM binned_timeseries
    GROUP BY hostname
)
SELECT time, ROUND(value, 2) AS interpolated_cpu
FROM interpolated_timeseries
CROSS JOIN UNNEST(interpolated_avg_cpu_utilization)

Calcola l'utilizzo medio della CPU eseguito a intervalli di 30 secondi per un EC2 host specifico nelle ultime 2 ore, inserendo i valori mancanti utilizzando l'interpolazione basata sull'ultima osservazione effettuata:

WITH binned_timeseries AS (
    SELECT hostname, BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND hostname = 'host-Hovjv'
        AND time > ago(2h)
    GROUP BY hostname, BIN(time, 30s)
), interpolated_timeseries AS (
    SELECT hostname,
        INTERPOLATE_LOCF(
            CREATE_TIME_SERIES(binned_timestamp, avg_cpu_utilization),
                SEQUENCE(min(binned_timestamp), max(binned_timestamp), 15s)) AS interpolated_avg_cpu_utilization
    FROM binned_timeseries
    GROUP BY hostname
)
SELECT time, ROUND(value, 2) AS interpolated_cpu
FROM interpolated_timeseries
CROSS JOIN UNNEST(interpolated_avg_cpu_utilization)