Amazon Timestream for LiveAnalytics와 유사한 기능을 원하는 경우 Amazon Timestream for InfluxDB를 고려해 보세요. 간소화된 데이터 수집과 실시간 분석을 위한 10밀리초 미만의 쿼리 응답 시간을 제공합니다. [여기](https://docs.aws.amazon.com//timestream/latest/developerguide/timestream-for-influxdb.html)에서 자세히 알아보세요.

기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다.

# 시계열 함수를 사용한 쿼리
<a name="sample-queries.devops-scenarios"></a>

**Topics**
+ [예제 데이터세트 및 쿼리](#sample-queries.devops-scenarios.example)

## 예제 데이터세트 및 쿼리
<a name="sample-queries.devops-scenarios.example"></a>

Timestream for LiveAnalytics를 사용하여 서비스 및 애플리케이션의 성능과 가용성을 이해하고 개선할 수 있습니다. 다음은 해당 테이블에서 실행되는 예제 테이블 및 샘플 쿼리입니다.

테이블 `ec2_metrics`에는 EC2 인스턴스의 CPU 사용률과 기타 지표 등의 원격 분석 데이터가 저장됩니다. 아래 표를 볼 수 있습니다.


| Time | 리전 | az | 호스트 이름 | measure\_name | measure\_value::double | measure\_value::bigint | 
| --- | --- | --- | --- | --- | --- | --- | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | cpu\_utilization | 35.1 | null | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | memory\_utilization | 55.3 | null | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | network\_bytes\_in | null | 1,500 | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | network\_bytes\_out | null | 6,700 | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | cpu\_utilization | 38.5 | null | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | memory\_utilization | 58.4 | null | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | network\_bytes\_in | null | 23,000 | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | network\_bytes\_out | null | 12,000 | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | cpu\_utilization | 45.0 | null | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | memory\_utilization | 65.8 | null | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | network\_bytes\_in | null | 15,000 | 
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | network\_bytes\_out | null | 836,000 | 
| 2019-12-04 19:00:05.000000000 | us-east-1 | us-east-1a | frontend01 | cpu\_utilization | 55.2 | null | 
| 2019-12-04 19:00:05.000000000 | us-east-1 | us-east-1a | frontend01 | memory\_utilization | 75.0 | null | 
| 2019-12-04 19:00:05.000000000 | us-east-1 | us-east-1a | frontend01 | network\_bytes\_in | null | 1,245 | 
| 2019-12-04 19:00:05.000000000 | us-east-1 | us-east-1a | frontend01 | network\_bytes\_out | null | 68,432 | 
| 2019-12-04 19:00:08.000000000 | us-east-1 | us-east-1b | frontend02 | cpu\_utilization | 65.6 | null | 
| 2019-12-04 19:00:08.000000000 | us-east-1 | us-east-1b | frontend02 | memory\_utilization | 85.3 | null | 
| 2019-12-04 19:00:08.000000000 | us-east-1 | us-east-1b | frontend02 | network\_bytes\_in | null | 1,245 | 
| 2019-12-04 19:00:08.000000000 | us-east-1 | us-east-1b | frontend02 | network\_bytes\_out | null | 68,432 | 
| 2019-12-04 19:00:20.000000000 | us-east-1 | us-east-1c | frontend03 | cpu\_utilization | 12.1 | null | 
| 2019-12-04 19:00:20.000000000 | us-east-1 | us-east-1c | frontend03 | memory\_utilization | 32.0 | null | 
| 2019-12-04 19:00:20.000000000 | us-east-1 | us-east-1c | frontend03 | network\_bytes\_in | null | 1,400 | 
| 2019-12-04 19:00:20.000000000 | us-east-1 | us-east-1c | frontend03 | network\_bytes\_out | null | 345 | 
| 2019-12-04 19:00:10.000000000 | us-east-1 | us-east-1a | frontend01 | cpu\_utilization | 15.3 | null | 
| 2019-12-04 19:00:10.000000000 | us-east-1 | us-east-1a | frontend01 | memory\_utilization | 35.4 | null | 
| 2019-12-04 19:00:10.000000000 | us-east-1 | us-east-1a | frontend01 | network\_bytes\_in | null | 23 | 
| 2019-12-04 19:00:10.000000000 | us-east-1 | us-east-1a | frontend01 | network\_bytes\_out | null | 0 | 
| 2019-12-04 19:00:16.000000000 | us-east-1 | us-east-1b | frontend02 | cpu\_utilization | 44.0 | null | 
| 2019-12-04 19:00:16.000000000 | us-east-1 | us-east-1b | frontend02 | memory\_utilization | 64.2 | null | 
| 2019-12-04 19:00:16.000000000 | us-east-1 | us-east-1b | frontend02 | network\_bytes\_in | null | 1,450 | 
| 2019-12-04 19:00:16.000000000 | us-east-1 | us-east-1b | frontend02 | network\_bytes\_out | null | 200 | 
| 2019-12-04 19:00:40.000000000 | us-east-1 | us-east-1c | frontend03 | cpu\_utilization | 66.4 | null | 
| 2019-12-04 19:00:40.000000000 | us-east-1 | us-east-1c | frontend03 | memory\_utilization | 86.3 | null | 
| 2019-12-04 19:00:40.000000000 | us-east-1 | us-east-1c | frontend03 | network\_bytes\_in | null | 300 | 
| 2019-12-04 19:00:40.000000000 | us-east-1 | us-east-1c | frontend03 | network\_bytes\_out | null | 423 | 

지난 2시간 동안 특정 EC2 호스트의 평균, p90, p95 및 p99 CPU 사용률을 찾습니다.

```
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)
```