如需與 Amazon Timestream for LiveAnalytics 類似的功能，請考慮使用 Amazon Timestream for InfluxDB。它提供簡化的資料擷取和單一位數毫秒查詢回應時間，以進行即時分析。[在這裡](https://docs.aws.amazon.com//timestream/latest/developerguide/timestream-for-influxdb.html)進一步了解。

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

# 透過跨儀表板共用排程查詢來最佳化成本
<a name="scheduledqueries-example3"></a>

在此範例中，我們將看到一個案例，其中多個儀表板面板顯示類似資訊的變化 （尋找具有高 CPU 使用率的高 CPU 主機和機群的部分），以及如何使用相同的排程查詢來預先計算結果，然後用於填入多個面板。此重複使用可進一步最佳化您的成本，其中，您僅使用擁有者，而不是使用不同的排程查詢，每個面板各一個。

## 具有原始資料的儀表板面板
<a name="scheduledqueries-example3-dashboard-raw"></a>

**每個微服務每個區域的 CPU 使用率**

第一個面板會運算平均 CPU 使用率低於或高於上述 CPU 使用率的執行個體，以便在區域、儲存格、孤島、可用區域和微服務內進行特定部署。然後，它會排序具有最高使用率主機百分比的區域和微服務。它有助於識別特定部署的伺服器正在執行的熱度，然後向下切入以更好地了解問題。

面板的查詢示範 Timestream for LiveAnalytics 的 SQL 支援彈性，以執行具有常見資料表表達式、視窗函數、聯結等的複雜分析任務。

![\[Table showing CPU utilization data for microservices across different regions.\]](http://docs.aws.amazon.com/zh_tw/timestream/latest/developerguide/images/sched_query_ex3_img1.png)


*查詢*：

```
WITH microservice_cell_avg AS (
    SELECT region, cell, silo, availability_zone, microservice_name, AVG(cpu_user) AS microservice_avg_metric
    FROM "raw_data"."devops"
    WHERE time BETWEEN from_milliseconds(1636526593876) AND from_milliseconds(1636612993876)
        AND measure_name = 'metrics'
    GROUP BY region, cell, silo, availability_zone, microservice_name
), instance_avg AS (
    SELECT region, cell, silo, availability_zone, microservice_name, instance_name,
        AVG(cpu_user) AS instance_avg_metric
    FROM "raw_data"."devops"
    WHERE time BETWEEN from_milliseconds(1636526593876) AND from_milliseconds(1636612993876)
        AND measure_name = 'metrics'
    GROUP BY region, cell, silo, availability_zone, microservice_name, instance_name
), instances_above_threshold AS (
  SELECT i.*,
    CASE WHEN i.instance_avg_metric > (1 + 0.2) * m.microservice_avg_metric THEN 1 ELSE 0 END AS high_utilization,
    CASE WHEN i.instance_avg_metric < (1 - 0.2) * m.microservice_avg_metric THEN 1 ELSE 0 END AS low_utilization
  FROM instance_avg i INNER JOIN microservice_cell_avg m 
    ON i.region = m.region AND i.cell = m.cell AND i.silo = m.silo AND i.availability_zone = m.availability_zone
      AND m.microservice_name = i.microservice_name
), per_deployment_high AS (
SELECT region, microservice_name, COUNT(*) AS num_hosts, SUM(high_utilization) AS high_utilization_hosts, SUM(low_utilization) AS low_utilization_hosts,
    ROUND(SUM(high_utilization) * 100.0 / COUNT(*), 0) AS percent_high_utilization_hosts,
    ROUND(SUM(low_utilization) * 100.0 / COUNT(*), 0) AS percent_low_utilization_hosts
FROM instances_above_threshold
GROUP BY region, microservice_name
), per_region_ranked AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY region ORDER BY percent_high_utilization_hosts DESC, high_utilization_hosts DESC) AS rank
    FROM per_deployment_high
)
SELECT *
FROM per_region_ranked
WHERE rank <= 2
ORDER BY percent_high_utilization_hosts desc, rank asc
```

**深入了解微服務以尋找熱點**

下一個儀表板可讓您深入探索其中一個微服務，以找出該微服務的特定區域、儲存格和孤島正在以更高的 CPU 使用率執行其機群的一小部分。例如，在機群範圍儀表板中，您看到微服務指標出現在前幾個排名的位置，因此在此儀表板中，您想要深入了解該微服務。

此儀表板使用變數來挑選微服務以深入了解，並使用維度的唯一值填入變數的值。選擇微服務後，儀表板的其餘部分會重新整理。

如下方所示，第一個面板會繪製一段時間內部署中的主機百分比 （微服務的區域、儲存格和孤島），以及用來繪製儀表板的對應查詢。此圖本身會識別具有較高 CPU 主機百分比的特定部署。

![\[Dropdown menu showing "microservice", "demeter", "topk", and "2" options.\]](http://docs.aws.amazon.com/zh_tw/timestream/latest/developerguide/images/sched_query_ex3_img2.png)


![\[Graph showing deployments with high CPU utilization over time, with multiple flat lines.\]](http://docs.aws.amazon.com/zh_tw/timestream/latest/developerguide/images/sched_query_ex3_img3.png)


*查詢*：

```
WITH microservice_cell_avg AS (
    SELECT region, cell, silo, availability_zone, microservice_name, bin(time, 1h) as hour, AVG(cpu_user) AS microservice_avg_metric
    FROM "raw_data"."devops"
    WHERE time BETWEEN from_milliseconds(1636526898831) AND from_milliseconds(1636613298831)
        AND measure_name = 'metrics'
        AND microservice_name = 'demeter'
    GROUP BY region, cell, silo, availability_zone, microservice_name, bin(time, 1h)
), instance_avg AS (
    SELECT region, cell, silo, availability_zone, microservice_name, instance_name, bin(time, 1h) as hour,
        AVG(cpu_user) AS instance_avg_metric
    FROM "raw_data"."devops"
    WHERE time BETWEEN from_milliseconds(1636526898831) AND from_milliseconds(1636613298831)
        AND measure_name = 'metrics'
        AND microservice_name = 'demeter'
    GROUP BY region, cell, silo, availability_zone, microservice_name, instance_name, bin(time, 1h)
), instances_above_threshold AS (
  SELECT i.*,
    CASE WHEN i.instance_avg_metric > (1 + 0.2) * m.microservice_avg_metric THEN 1 ELSE 0 END AS high_utilization
  FROM instance_avg i INNER JOIN microservice_cell_avg m 
    ON i.region = m.region AND i.cell = m.cell AND i.silo = m.silo AND i.availability_zone = m.availability_zone
      AND m.microservice_name = i.microservice_name AND m.hour = i.hour
), high_utilization_percent AS (
    SELECT region, cell, silo, microservice_name, hour, COUNT(*) AS num_hosts, SUM(high_utilization) AS high_utilization_hosts,
        ROUND(SUM(high_utilization) * 100.0 / COUNT(*), 0) AS percent_high_utilization_hosts
    FROM instances_above_threshold
    GROUP BY region, cell, silo, microservice_name, hour
), high_utilization_ranked AS (
    SELECT region, cell, silo, microservice_name, 
        DENSE_RANK() OVER (PARTITION BY region ORDER BY AVG(percent_high_utilization_hosts) desc, AVG(high_utilization_hosts) desc) AS rank
    FROM high_utilization_percent 
    GROUP BY region, cell, silo, microservice_name
)
SELECT hup.silo, CREATE_TIME_SERIES(hour, hup.percent_high_utilization_hosts) AS percent_high_utilization_hosts
FROM high_utilization_percent hup INNER JOIN high_utilization_ranked hur
    ON hup.region = hur.region AND hup.cell = hur.cell AND hup.silo = hur.silo AND hup.microservice_name = hur.microservice_name
WHERE rank <= 2
GROUP BY hup.region, hup.cell, hup.silo
ORDER BY hup.silo
```

## 轉換為單一排程查詢以重複使用
<a name="scheduledqueries-example3-query-reuse"></a>

請務必注意，在兩個儀表板的不同面板中也會進行類似的運算。您可以為每個面板定義個別的排程查詢。在這裡，您將看到如何透過定義一個排程查詢來進一步最佳化成本，這些查詢可用於轉譯所有三個面板的結果。

下列查詢會擷取計算並用於所有不同面板的彙總。您將在此排程查詢的定義中觀察幾個重要層面。
+ 排程查詢支援的 SQL 表面積的彈性和功能，您可以在其中使用常見的資料表表達式、聯結、案例陳述式等。
+ 您可以使用一個排程查詢，以比特定儀表板可能需要更精細的精細程度來計算統計資料，以及儀表板可能用於不同變數的所有值。例如，您會看到彙總會跨區域、儲存格、孤島和微服務進行計算。因此，您可以結合這些項目來建立區域層級或區域，以及微服務層級彙總。同樣地，相同的查詢會運算所有區域、儲存格、孤島和微服務的彙總。它可讓您在這些資料欄上套用篩選條件，以取得一部分值的彙總。例如，您可以計算任何一個區域的彙總，例如 us-east-1，或任何一個微服務，例如 demeter 或深入探索區域、儲存格、孤立和微服務中的特定部署。此方法可進一步最佳化維護預先計算彙總的成本。

```
WITH microservice_cell_avg AS (
    SELECT region, cell, silo, availability_zone, microservice_name, bin(time, 1h) as hour, AVG(cpu_user) AS microservice_avg_metric    
    FROM raw_data.devops    
    WHERE time BETWEEN bin(@scheduled_runtime, 1h) - 1h AND bin(@scheduled_runtime, 1h) + 1h
        AND measure_name = 'metrics'    
    GROUP BY region, cell, silo, availability_zone, microservice_name, bin(time, 1h)    
), instance_avg AS (
    SELECT region, cell, silo, availability_zone, microservice_name, instance_name, bin(time, 1h) as hour,
        AVG(cpu_user) AS instance_avg_metric    
   FROM raw_data.devops    
   WHERE time BETWEEN bin(@scheduled_runtime, 1h) - 1h AND bin(@scheduled_runtime, 1h) + 1h
       AND measure_name = 'metrics'    
   GROUP BY region, cell, silo, availability_zone, microservice_name, instance_name, bin(time, 1h)    
), instances_above_threshold AS (
    SELECT i.*,
        CASE WHEN i.instance_avg_metric > (1 + 0.2) * m.microservice_avg_metric THEN 1 ELSE 0 END AS high_utilization,    
        CASE WHEN i.instance_avg_metric < (1 - 0.2) * m.microservice_avg_metric THEN 1 ELSE 0 END AS low_utilization    
   FROM instance_avg i INNER JOIN microservice_cell_avg m
       ON i.region = m.region AND i.cell = m.cell AND i.silo = m.silo AND i.availability_zone = m.availability_zone
           AND m.microservice_name = i.microservice_name AND m.hour = i.hour    
)     
SELECT region, cell, silo, microservice_name, hour,
     COUNT(*) AS num_hosts, SUM(high_utilization) AS high_utilization_hosts, SUM(low_utilization) AS low_utilization_hosts    
FROM instances_above_threshold GROUP BY region, cell, silo, microservice_name, hour
```

以下是上一個查詢的排程查詢定義。排程表達式設定為每 30 分鐘重新整理一次，並使用 bin(@scheduled\$1runtime， 1h) 建構再次重新整理資料長達一小時，以取得完整小時的事件。視應用程式的新鮮度需求而定，您可以將其設定為更頻繁或更不頻繁地重新整理。透過使用 WHERE time BETWEEN bin(@scheduled\$1runtime， 1h) - 1h and bin(@scheduled\$1runtime， 1h) \$1 1h，我們可以確保即使您每 15 分鐘重新整理一次，仍會取得目前小時和前一小時的完整小時資料。

稍後，您將看到三個面板如何使用寫入資料表 deployment\$1cpu\$1stats\$1per\$1hr 的這些彙總來視覺化與面板相關的指標。

```
{
    "Name": "MultiPT30mHighCpuDeploymentsPerHr",
    "QueryString": "WITH microservice_cell_avg AS (    SELECT region, cell, silo, availability_zone, microservice_name, bin(time, 1h) as hour, AVG(cpu_user) AS microservice_avg_metric    FROM raw_data.devops    WHERE time BETWEEN bin(@scheduled_runtime, 1h) - 1h AND bin(@scheduled_runtime, 1h) + 1h    AND measure_name = 'metrics'    GROUP BY region, cell, silo, availability_zone, microservice_name, bin(time, 1h)    ), instance_avg AS (    SELECT region, cell, silo, availability_zone, microservice_name, instance_name, bin(time, 1h) as hour,    AVG(cpu_user) AS instance_avg_metric    FROM raw_data.devops    WHERE time BETWEEN bin(@scheduled_runtime, 1h) - 1h AND bin(@scheduled_runtime, 1h) + 1h    AND measure_name = 'metrics'    GROUP BY region, cell, silo, availability_zone, microservice_name, instance_name, bin(time, 1h)    ), instances_above_threshold AS (    SELECT i.*,    CASE WHEN i.instance_avg_metric > (1 + 0.2) * m.microservice_avg_metric THEN 1 ELSE 0 END AS high_utilization,    CASE WHEN i.instance_avg_metric < (1 - 0.2) * m.microservice_avg_metric THEN 1 ELSE 0 END AS low_utilization    FROM instance_avg i INNER JOIN microservice_cell_avg m    ON i.region = m.region AND i.cell = m.cell AND i.silo = m.silo AND i.availability_zone = m.availability_zone    AND m.microservice_name = i.microservice_name AND m.hour = i.hour    )     SELECT region, cell, silo, microservice_name, hour,         COUNT(*) AS num_hosts, SUM(high_utilization) AS high_utilization_hosts, SUM(low_utilization) AS low_utilization_hosts    FROM instances_above_threshold GROUP BY region, cell, silo, microservice_name, hour",
    "ScheduleConfiguration": {
        "ScheduleExpression": "cron(0/30 * * * ? *)"
    },
    "NotificationConfiguration": {
        "SnsConfiguration": {
            "TopicArn": "******"
        }
    },
    "TargetConfiguration": {
        "TimestreamConfiguration": {
            "DatabaseName": "derived",
            "TableName": "deployment_cpu_stats_per_hr",
            "TimeColumn": "hour",
            "DimensionMappings": [
                {
                    "Name": "region",
                    "DimensionValueType": "VARCHAR"
                },
                {
                    "Name": "cell",
                    "DimensionValueType": "VARCHAR"
                },
                {
                    "Name": "silo",
                    "DimensionValueType": "VARCHAR"
                },
                {
                    "Name": "microservice_name",
                    "DimensionValueType": "VARCHAR"
                }
            ],
            "MultiMeasureMappings": {
                "TargetMultiMeasureName": "cpu_user",
                "MultiMeasureAttributeMappings": [
                    {
                        "SourceColumn": "num_hosts",
                        "MeasureValueType": "BIGINT"
                    },
                    {
                        "SourceColumn": "high_utilization_hosts",
                        "MeasureValueType": "BIGINT"
                    },
                    {
                        "SourceColumn": "low_utilization_hosts",
                        "MeasureValueType": "BIGINT"
                    }
                ]
            }
        }
    },
    "ErrorReportConfiguration": {
        "S3Configuration" : {
            "BucketName" : "******",
            "ObjectKeyPrefix": "errors",
            "EncryptionOption": "SSE_S3"
        }
    },
    "ScheduledQueryExecutionRoleArn": "******"
}
```

## 預先計算結果的儀表板
<a name="scheduledqueries-example3-dashboard-precompute"></a>

**高 CPU 使用率主機**

對於高使用率主機，您會看到不同的面板如何使用來自 deployment\$1cpu\$1stats\$1per\$1hr 的資料來計算面板所需的不同彙總。例如，此面板提供區域層級資訊，因此會報告依區域和微服務分組的彙總，而不會篩選任何區域或微服務。

![\[Table showing microservice utilization stats across regions, with high and low host percentages.\]](http://docs.aws.amazon.com/zh_tw/timestream/latest/developerguide/images/sched_query_ex3_img4.png)


```
WITH per_deployment_hosts AS (
    SELECT region, cell, silo, microservice_name, 
        AVG(num_hosts) AS num_hosts, 
        AVG(high_utilization_hosts) AS high_utilization_hosts, 
        AVG(low_utilization_hosts) AS low_utilization_hosts
    FROM "derived"."deployment_cpu_stats_per_hr"
    WHERE time BETWEEN from_milliseconds(1636567785437) AND from_milliseconds(1636654185437)
        AND measure_name = 'cpu_user'
    GROUP BY region, cell, silo, microservice_name
), per_deployment_high AS (
    SELECT region, microservice_name,
        SUM(num_hosts) AS num_hosts, 
        ROUND(SUM(high_utilization_hosts), 0) AS high_utilization_hosts,
        ROUND(SUM(low_utilization_hosts),0) AS low_utilization_hosts,
        ROUND(SUM(high_utilization_hosts) * 100.0 / SUM(num_hosts)) AS percent_high_utilization_hosts,
        ROUND(SUM(low_utilization_hosts) * 100.0 / SUM(num_hosts)) AS percent_low_utilization_hosts
    FROM per_deployment_hosts
    GROUP BY region, microservice_name
), 
per_region_ranked AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY region ORDER BY percent_high_utilization_hosts DESC, high_utilization_hosts DESC) AS rank
    FROM per_deployment_high
)
SELECT *
FROM per_region_ranked
WHERE rank <= 2
ORDER BY percent_high_utilization_hosts desc, rank asc
```

**深入了解微服務以尋找高 CPU 用量部署**

下一個範例再次使用 deployment\$1cpu\$1stats\$1per\$1hr 衍生的資料表，但現在會套用特定微服務的篩選條件 （此範例中的指標，因為它在彙總儀表板中報告了高使用率主機）。此面板會追蹤一段時間內高 CPU 使用率主機的百分比。

![\[Graph showing consistent high CPU utilization percentages for multiple deployments over 24 hours.\]](http://docs.aws.amazon.com/zh_tw/timestream/latest/developerguide/images/sched_query_ex3_img5.png)


```
WITH high_utilization_percent AS (
    SELECT region, cell, silo, microservice_name, bin(time, 1h) AS hour, MAX(num_hosts) AS num_hosts, 
        MAX(high_utilization_hosts) AS high_utilization_hosts,
        ROUND(MAX(high_utilization_hosts) * 100.0 / MAX(num_hosts)) AS percent_high_utilization_hosts
    FROM "derived"."deployment_cpu_stats_per_hr"
    WHERE time BETWEEN from_milliseconds(1636525800000) AND from_milliseconds(1636612200000)
        AND measure_name = 'cpu_user'
        AND microservice_name = 'demeter'
    GROUP BY region, cell, silo, microservice_name, bin(time, 1h)
), high_utilization_ranked AS (
    SELECT region, cell, silo, microservice_name, 
        DENSE_RANK() OVER (PARTITION BY region ORDER BY AVG(percent_high_utilization_hosts) desc, AVG(high_utilization_hosts) desc) AS rank
    FROM high_utilization_percent 
    GROUP BY region, cell, silo, microservice_name
)
SELECT hup.silo, CREATE_TIME_SERIES(hour, hup.percent_high_utilization_hosts) AS percent_high_utilization_hosts
FROM high_utilization_percent hup INNER JOIN high_utilization_ranked hur
    ON hup.region = hur.region AND hup.cell = hur.cell AND hup.silo = hur.silo AND hup.microservice_name = hur.microservice_name
WHERE rank <= 2
GROUP BY hup.region, hup.cell, hup.silo
ORDER BY hup.silo
```