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

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

# 將彙總儀表板轉換為排程查詢
<a name="scheduledqueries-example1"></a>

假設您正在計算整個機群的統計資料，例如透過五個微服務以及部署服務的六個區域在機群中的主機計數。從下面的快照中，您可以看到有 500K部伺服器發出指標，而一些較大的區域 （例如 us-east-1) 有 >20 萬部伺服器。

運算這些彙總，您在其中運算數百 GB 資料的不同執行個體名稱，除了掃描資料的成本之外，還可能導致查詢延遲數十秒。

![\[Instance counts for microservices: apollo and zeus 150k, hercules 100k, athena and demeter 50k each.\]](http://docs.aws.amazon.com/zh_tw/timestream/latest/developerguide/images/sched_query_ex1_img1.png)


**原始儀表板查詢**

dasboard 面板中顯示的彙總會使用下列查詢，從原始資料計算。查詢使用多個 SQL 建構，例如不同的計數和多個彙總函數。

```
SELECT CASE WHEN microservice_name = 'apollo' THEN num_instances ELSE NULL END AS apollo,
    CASE WHEN microservice_name = 'athena' THEN num_instances ELSE NULL END AS athena,
    CASE WHEN microservice_name = 'demeter' THEN num_instances ELSE NULL END AS demeter,
    CASE WHEN microservice_name = 'hercules' THEN num_instances ELSE NULL END AS hercules,
    CASE WHEN microservice_name = 'zeus' THEN num_instances ELSE NULL END AS zeus
FROM (
    SELECT microservice_name, SUM(num_instances) AS num_instances
    FROM (
        SELECT microservice_name, COUNT(DISTINCT instance_name) as num_instances
        FROM "raw_data"."devops"
        WHERE time BETWEEN from_milliseconds(1636526171043) AND from_milliseconds(1636612571043)
            AND measure_name = 'metrics'
        GROUP BY region, cell, silo, availability_zone, microservice_name
    )
    GROUP BY microservice_name
)
```

**轉換為排程查詢**

先前的查詢可以轉換成排定的查詢，如下所示。您會先在區域、儲存格、孤島、可用區域和微服務的特定部署中計算不同的主機名稱。然後，您加總主機以計算每個微服務主機計數每小時的 。透過使用排程查詢支援的 `@scheduled_runtime` 參數，您可以在叫用查詢時重新計算過去一小時的參數。內部查詢的 `WHERE`子句`bin(@scheduled_runtime, 1h)`中的 可確保即使查詢排程在一個小時中間的時間，您仍然會取得整小時的資料。

即使查詢會運算每小時彙總，如同您在排程的運算組態中所見，它也會設定為每半小時重新整理一次，以便您更快在衍生的資料表中取得更新。您可以根據您的新鮮度需求進行調整，例如每 15 分鐘重新計算彙總，或在小時界限重新計算彙總。

```
SELECT microservice_name, hour, SUM(num_instances) AS num_instances    
FROM (
        SELECT microservice_name, bin(time, 1h) AS hour, 
            COUNT(DISTINCT instance_name) as num_instances
       FROM raw_data.devops        
       WHERE time BETWEEN bin(@scheduled_runtime, 1h) - 1h AND @scheduled_runtime        
           AND measure_name = 'metrics'        
       GROUP BY region, cell, silo, availability_zone, microservice_name, bin(time, 1h)    
     )    
GROUP BY microservice_name, hour
```

```
{
    "Name": "MultiPT30mHostCountMicroservicePerHr",
    "QueryString": "SELECT microservice_name, hour, SUM(num_instances) AS num_instances    FROM (        SELECT microservice_name, bin(time, 1h) AS hour, COUNT(DISTINCT instance_name) as num_instances        FROM raw_data.devops        WHERE time BETWEEN bin(@scheduled_runtime, 1h) - 1h AND @scheduled_runtime            AND measure_name = 'metrics'        GROUP BY region, cell, silo, availability_zone, microservice_name, bin(time, 1h)    )    GROUP BY microservice_name, hour",
    "ScheduleConfiguration": {
        "ScheduleExpression": "cron(0/30 * * * ? *)"
    },
    "NotificationConfiguration": {
        "SnsConfiguration": {
            "TopicArn": "******"
        }
    },
    "TargetConfiguration": {
        "TimestreamConfiguration": {
            "DatabaseName": "derived",
            "TableName": "host_count_pt1h",
            "TimeColumn": "hour",
            "DimensionMappings": [
                {
                    "Name": "microservice_name",
                    "DimensionValueType": "VARCHAR"
                }
            ],
            "MultiMeasureMappings": {
                "TargetMultiMeasureName": "num_instances",
                "MultiMeasureAttributeMappings": [
                    {
                        "SourceColumn": "num_instances",
                        "MeasureValueType": "BIGINT"
                    }
                ]
            }
        }
    },
    "ErrorReportConfiguration": {
        "S3Configuration" : {
            "BucketName" : "******",
            "ObjectKeyPrefix": "errors",
            "EncryptionOption": "SSE_S3"
        }
    },
    "ScheduledQueryExecutionRoleArn": "******"
}
```

**在新的儀表板中使用預先計算的結果**

您現在將了解如何使用您建立的排程查詢衍生的資料表來建立彙總檢視儀表板。從儀表板快照中，您也可以驗證從衍生資料表和基礎資料表計算的彙總是否也相符。使用衍生資料表建立儀表板後，相較於從原始資料運算這些彙總，您會注意到使用衍生資料表的載入時間明顯更快，且成本更低。以下是使用預先運算資料的儀表板快照，以及使用儲存在資料表「衍生」中的預先運算資料轉譯此面板的查詢。」host\$1count\$1pt1h」。請注意，查詢的結構與原始資料儀表板中使用的查詢非常相似，但它使用已計算此查詢彙總之不同計數的衍生資料表。

![\[Instance count by microservice showing values for apollo, athena, demeter, hercules, and zeus.\]](http://docs.aws.amazon.com/zh_tw/timestream/latest/developerguide/images/sched_query_ex1_img2.png)


```
SELECT CASE WHEN microservice_name = 'apollo' THEN num_instances ELSE NULL END AS apollo,
    CASE WHEN microservice_name = 'athena' THEN num_instances ELSE NULL END AS athena,
    CASE WHEN microservice_name = 'demeter' THEN num_instances ELSE NULL END AS demeter,
    CASE WHEN microservice_name = 'hercules' THEN num_instances ELSE NULL END AS hercules,
    CASE WHEN microservice_name = 'zeus' THEN num_instances ELSE NULL END AS zeus
FROM (
    SELECT microservice_name, AVG(num_instances) AS num_instances
    FROM (
        SELECT microservice_name, bin(time, 1h), SUM(num_instances) as num_instances
        FROM "derived"."host_count_pt1h"
        WHERE time BETWEEN from_milliseconds(1636567785421) AND from_milliseconds(1636654185421)
            AND measure_name = 'num_instances'
        GROUP BY microservice_name, bin(time, 1h)
    )
    GROUP BY microservice_name
)
```