使用时间序列函数的查询 - Amazon Timestream
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

要获得与亚马逊 Timestream 类似的功能 LiveAnalytics,可以考虑适用于 InfluxDB 的亚马逊 Timestream。它为实时分析提供了简化的数据摄取和个位数毫秒的查询响应时间。点击此处了解更多。

本文属于机器翻译版本。若本译文内容与英语原文存在差异,则一律以英文原文为准。

使用时间序列函数的查询

示例数据集和查询

您可以使用 Timestream LiveAnalytics 来了解和提高您的服务和应用程序的性能和可用性。以下是一个示例表,以及在该表上运行的示例查询。

该表ec2_metrics存储遥测数据,例如 CPU 利用率和来自 EC2 实例的其他指标。您可以查看下表。

Time 区域 az 主机名 measure_name measure_value::double measure_value::bigint

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1a

frontend01

CPU_利用率

35.1

null

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1a

frontend01

memory_utilization

55.3

null

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1a

frontend01

network_bytes_in

null

1500

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1a

frontend01

网络字节数输出

null

6,700

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1b

frontend02

CPU_利用率

38.5

null

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1b

frontend02

memory_utilization

58.4

null

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1b

frontend02

network_bytes_in

null

23,000

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1b

frontend02

网络字节数输出

null

12000

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1c

frontend03

CPU_利用率

45.0

null

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1c

frontend03

memory_utilization

65.8

null

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1c

frontend03

network_bytes_in

null

15000

2019-12-04 19:00:00.000 000 000

us-east-1

us–east–1c

frontend03

网络字节数输出

null

836,000

2019-12-04 19:00:05.000 000 000 000

us-east-1

us–east–1a

frontend01

CPU_利用率

55.2

null

2019-12-04 19:00:05.000 000 000 000

us-east-1

us–east–1a

frontend01

memory_utilization

75.0

null

2019-12-04 19:00:05.000 000 000 000

us-east-1

us–east–1a

frontend01

network_bytes_in

null

1,245

2019-12-04 19:00:05.000 000 000 000

us-east-1

us–east–1a

frontend01

网络字节数输出

null

68,432

2019-12-04 19:00:08.000 000 000 000

us-east-1

us–east–1b

frontend02

CPU_利用率

65.6

null

2019-12-04 19:00:08.000 000 000 000

us-east-1

us–east–1b

frontend02

memory_utilization

85.3

null

2019-12-04 19:00:08.000 000 000 000

us-east-1

us–east–1b

frontend02

network_bytes_in

null

1,245

2019-12-04 19:00:08.000 000 000 000

us-east-1

us–east–1b

frontend02

网络字节数输出

null

68,432

2019-12-04 19:00:20.000 000 000 000

us-east-1

us–east–1c

frontend03

CPU_利用率

12.1

null

2019-12-04 19:00:20.000 000 000 000

us-east-1

us–east–1c

frontend03

memory_utilization

32.0

null

2019-12-04 19:00:20.000 000 000 000

us-east-1

us–east–1c

frontend03

network_bytes_in

null

1,400

2019-12-04 19:00:20.000 000 000 000

us-east-1

us–east–1c

frontend03

网络字节数输出

null

345

2019-12-04 19:00:10.000 000 000

us-east-1

us–east–1a

frontend01

CPU_利用率

15.3

null

2019-12-04 19:00:10.000 000 000

us-east-1

us–east–1a

frontend01

memory_utilization

35.4

null

2019-12-04 19:00:10.000 000 000

us-east-1

us–east–1a

frontend01

network_bytes_in

null

23

2019-12-04 19:00:10.000 000 000

us-east-1

us–east–1a

frontend01

网络字节数输出

null

0

2019-12-04 19:00:16.000 000 000

us-east-1

us–east–1b

frontend02

CPU_利用率

44.0

null

2019-12-04 19:00:16.000 000 000

us-east-1

us–east–1b

frontend02

memory_utilization

64.2

null

2019-12-04 19:00:16.000 000 000

us-east-1

us–east–1b

frontend02

network_bytes_in

null

1,450

2019-12-04 19:00:16.000 000 000

us-east-1

us–east–1b

frontend02

网络字节数输出

null

200

2019-12-04 19:00:40.000 000 000 000

us-east-1

us–east–1c

frontend03

CPU_利用率

66.4

null

2019-12-04 19:00:40.000 000 000 000

us-east-1

us–east–1c

frontend03

memory_utilization

86.3

null

2019-12-04 19:00:40.000 000 000 000

us-east-1

us–east–1c

frontend03

network_bytes_in

null

300

2019-12-04 19:00:40.000 000 000 000

us-east-1

us–east–1c

frontend03

网络字节数输出

null

423

查找过去 2 小时内特定 EC2 主机的 CPU 平均利用率、p90、p95 和 p99:

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

找出与过去 2 小时内整个队列的平均 CPU 利用率相比 CPU 利用率高出 10% 或以上的 EC2 主机:

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

查找过去 2 小时内特定 EC2主机以 30 秒为间隔的平均 CPU 使用率:

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

找出过去 2 小时内特定 EC2主机以 30 秒为间隔分箱的平均 CPU 利用率,使用线性插值填充缺失值:

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)

找出过去 2 小时内特定 EC2主机以 30 秒为间隔分箱的平均 CPU 利用率,并根据上次执行的观测值使用插值填充缺失值:

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)