计划查询的数据模型映射 - Amazon Timestream
示例：多度量记录的目标度量名称示例：在多度量记录中使用计划查询中的度量名称示例：将结果映射到具有不同属性的不同多指标记录示例：使用查询结果中的度量名称将结果映射到单度量记录示例：将结果映射到以查询结果列作为度量名称的单度量记录
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

从2025年6月20日起，亚马逊Timestream版 LiveAnalytics 将不再向新客户开放。如果您想使用亚马逊 Timestream LiveAnalytics，请在该日期之前注册。现有客户可以继续照常使用该服务。有关更多信息，请参阅 Amazon Timestream 以了解 LiveAnalytics 可用性变更。

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

计划查询的数据模型映射

Timestream for LiveAnalytics 支持对其表中的数据进行灵活建模，同样的灵活性也适用于在表格的另一个 Timestream 中实现的定时查询的结果。 LiveAnalytics 通过计划查询，您可以查询任何表，无论该表包含多度量记录还是单度量记录中的数据，并使用多度量记录或单度量记录写入查询结果。

您可以在定时查询的规范 TargetConfiguration 中使用将查询结果映射到目标派生表中的相应列。以下各节描述了指定此值 TargetConfiguration 以在派生表中实现不同数据模型的不同方法。具体而言，您将看到：

  • 当查询结果没有度量名称并且您在中指定了目标度量名称时，如何写入多度量记录。 TargetConfiguration

  • 如何在查询结果中使用度量名称来写入多度量记录。

  • 如何定义一个模型来写入具有不同多度量属性的多条记录。

  • 如何定义模型以写入派生表中的单度量记录。

  • 如何在计划查询中查询单度量记录和/或多度量记录并将结果具体化为单度量记录或多度量记录，这使您可以灵活选择数据模型的灵活性。

示例：多度量记录的目标度量名称

在此示例中，您将看到查询正在从包含多度量数据的表中读取数据，并使用多度量记录将结果写入另一个表。计划查询结果没有自然度量名称列。在这里，您可以使用中的 TargetMultiMeasureName 属性在派生表中指定度量名称 TargetConfiguration。 TimestreamConfiguration。

{
    "Name" : "CustomMultiMeasureName",
    "QueryString" : "SELECT region, bin(time, 1h) as hour, AVG(memory_cached) as avg_mem_cached_1h, MIN(memory_free) as min_mem_free_1h, MAX(memory_used) as max_mem_used_1h, SUM(disk_io_writes) as sum_1h, AVG(disk_used) as avg_disk_used_1h, AVG(disk_free) as avg_disk_free_1h, MAX(cpu_user) as max_cpu_user_1h, MIN(cpu_idle) as min_cpu_idle_1h, MAX(cpu_system) as max_cpu_system_1h FROM raw_data.devops_multi WHERE time BETWEEN bin(@scheduled_runtime, 1h) - 14h AND bin(@scheduled_runtime, 1h) - 2h AND measure_name = 'metrics' GROUP BY region, bin(time, 1h)",
    "ScheduleConfiguration" : {
        "ScheduleExpression" : "cron(0 0/1 * * ? *)"
    },
    "NotificationConfiguration" : {
        "SnsConfiguration" : {
            "TopicArn" : "******"
        }
    },
    "ScheduledQueryExecutionRoleArn": "******",
    "TargetConfiguration": {
        "TimestreamConfiguration": {
            "DatabaseName" : "derived",
            "TableName" : "dashboard_metrics_1h_agg_1",
            "TimeColumn" : "hour",
            "DimensionMappings" : [
                {
                    "Name": "region",
                    "DimensionValueType" : "VARCHAR"
                }
            ],
            "MultiMeasureMappings" : {
                "TargetMultiMeasureName": "dashboard-metrics",
                "MultiMeasureAttributeMappings" : [
                    {
                        "SourceColumn" : "avg_mem_cached_1h",
                        "MeasureValueType" : "DOUBLE",
                        "TargetMultiMeasureAttributeName" : "avgMemCached"
                    },
                    {
                        "SourceColumn" : "min_mem_free_1h",
                        "MeasureValueType" : "DOUBLE"
                    },
                    {
                        "SourceColumn" : "max_mem_used_1h",
                        "MeasureValueType" : "DOUBLE"
                    },
                    {
                        "SourceColumn" : "sum_1h",
                        "MeasureValueType" : "DOUBLE",
                        "TargetMultiMeasureAttributeName" : "totalDiskWrites"
                    },
                    {
                        "SourceColumn" : "avg_disk_used_1h",
                        "MeasureValueType" : "DOUBLE"
                    },
                    {
                        "SourceColumn" : "avg_disk_free_1h",
                        "MeasureValueType" : "DOUBLE"
                    },
                    {
                        "SourceColumn" : "max_cpu_user_1h",
                        "MeasureValueType" : "DOUBLE",
                        "TargetMultiMeasureAttributeName" : "CpuUserP100"
                    },
                    {
                        "SourceColumn" : "min_cpu_idle_1h",
                        "MeasureValueType" : "DOUBLE"
                    },
                    {
                        "SourceColumn" : "max_cpu_system_1h",
                        "MeasureValueType" : "DOUBLE",
                        "TargetMultiMeasureAttributeName" : "CpuSystemP100"
                    }  
                ]
            }
        }
    },
    "ErrorReportConfiguration": {
        "S3Configuration" : {
            "BucketName" : "******",
            "ObjectKeyPrefix": "errors",
            "EncryptionOption": "SSE_S3"
        }
    }
}

本示例中的映射创建了一条包含度量名称仪表板指标和属性名称的多度量记录，min_mem_free_1h、max_mem_used_1h、、avg_disk_used_1h avgMemCached、avg_disk_free_1h、P100、min_cpu_idle_1h、P100。 totalDiskWrites CpuUser CpuSystem请注意，可以选择使用 TargetMultiMeasureAttributeName 将查询输出列重命名为用于结果实现的不同属性名称。

以下是实现此定时查询后目标表的架构。如以下结果中 LiveAnalytics 属性类型的 Timestream 所示，结果将具体化为具有单度量名称的多度量记录dashboard-metrics，如度量架构所示。

列 类型 LiveAnalytics 属性类型的时间流

区域

varchar

维度

measure_name

varchar

MEASURE_NAME

时间

timestamp

TIMESTAMP

CpuSystemP100

double

多

avgMemCached

double

多

min_cpu_idle_1h

double

多

avg_disk_free_1h

double

多

avg_disk_used_1h

double

多

totalDiskWrites

double

多

max_mem_used_1h

double

多

min_mem_free_1h

double

多

CpuUserP100

double

多

以下是通过 SHOW MEASURES 查询获得的相应度量。

measure_name data_type Dimensions

仪表板指标

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

示例：在多度量记录中使用计划查询中的度量名称

在此示例中，您将看到一个查询从包含单度量记录的表中读取数据，并将结果具体化为多度量记录。在这种情况下，调度查询结果中有一列，该列的值可用作目标表中的度量名称，而定时查询的结果将在该表中实现定时查询的结果。然后，您可以使用中的 MeasureNameColumn 属性为派生表中的多度量记录指定度量名称。 TargetConfiguration TimestreamConfiguration。

{
    "Name" : "UsingMeasureNameFromQueryResult",
    "QueryString" : "SELECT region, bin(time, 1h) as hour, measure_name, AVG(CASE WHEN measure_name IN ('memory_cached', 'disk_used', 'disk_free') THEN measure_value::double ELSE NULL END) as avg_1h, MIN(CASE WHEN measure_name IN ('memory_free', 'cpu_idle') THEN measure_value::double ELSE NULL END) as min_1h, SUM(CASE WHEN measure_name IN ('disk_io_writes') THEN measure_value::double ELSE NULL END) as sum_1h, MAX(CASE WHEN measure_name IN ('memory_used', 'cpu_user', 'cpu_system') THEN measure_value::double ELSE NULL END) as max_1h FROM raw_data.devops WHERE time BETWEEN bin(@scheduled_runtime, 1h) - 14h AND bin(@scheduled_runtime, 1h) - 2h AND measure_name IN ('memory_free', 'memory_used', 'memory_cached', 'disk_io_writes', 'disk_used', 'disk_free', 'cpu_user', 'cpu_system', 'cpu_idle') GROUP BY region, measure_name, bin(time, 1h)",
    "ScheduleConfiguration" : {
        "ScheduleExpression" : "cron(0 0/1 * * ? *)"
    },
    "NotificationConfiguration" : {
        "SnsConfiguration" : {
            "TopicArn" : "******"
        }
    },
    "ScheduledQueryExecutionRoleArn": "******",
    "TargetConfiguration": {
        "TimestreamConfiguration": {
            "DatabaseName" : "derived",
            "TableName" : "dashboard_metrics_1h_agg_2",
            "TimeColumn" : "hour",
            "DimensionMappings" : [
                {
                    "Name": "region",
                    "DimensionValueType" : "VARCHAR"
                }
            ],
            "MeasureNameColumn" : "measure_name",
            "MultiMeasureMappings" : {
                "MultiMeasureAttributeMappings" : [
                    {
                        "SourceColumn" : "avg_1h",
                        "MeasureValueType" : "DOUBLE"
                    },
                    {
                        "SourceColumn" : "min_1h",
                        "MeasureValueType" : "DOUBLE",
                        "TargetMultiMeasureAttributeName": "p0_1h"
                    },
                    {
                        "SourceColumn" : "sum_1h",
                        "MeasureValueType" : "DOUBLE"
                    },
                    {
                        "SourceColumn" : "max_1h",
                        "MeasureValueType" : "DOUBLE",
                        "TargetMultiMeasureAttributeName": "p100_1h"
                    } 
                ]
            }
        }
    },
    "ErrorReportConfiguration": {
        "S3Configuration" : {
            "BucketName" : "******",
            "ObjectKeyPrefix": "errors",
            "EncryptionOption": "SSE_S3"
        }
    }
}

本示例中的映射将创建具有属性 avg_1h、p0_1h、sum_1h、p100_1h 的多度量记录，并将使用查询结果中 measure_name 列的值作为目标表中多度量记录的度量名称。此外，请注意，前面的示例可以选择使用 TargetMultiMeasureAttributeName 带有映射子集的来重命名属性。例如，min_1h 被重命名为 p0_1h，max_1h 重命名为 p100_1h。

以下是实现此定时查询后目标表的架构。正如您在以下结果中从 LiveAnalytics 属性类型的时间流中看到的那样，结果被具体化为多度量记录。如果您查看度量架构，则提取了九个不同的度量名称，它们与查询结果中看到的值相对应。

列 类型 LiveAnalytics 属性类型的时间流

区域

varchar

维度

measure_name

varchar

MEASURE_NAME

时间

timestamp

TIMESTAMP

sum_1h

double

多

p100_1h

double

多

p0_1h

double

多

avg_1h

double

多

以下是通过 SHOW MEASURES 查询获得的相应度量。

measure_name data_type Dimensions

cpu_idle

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

cpu_system

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

cpu_user

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

disk_free

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

disk_io_writes

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

disk_used

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

内存_cached

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

内存_免费

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

内存_免费

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

示例：将结果映射到具有不同属性的不同多指标记录

以下示例说明如何将查询结果中的不同列映射到具有不同度量名称的不同多度量记录中。如果你看到以下定时查询定义，则查询结果包含以下各列：区域、小时、avg_mem_cached_1h、min_mem_free_1h、max_mem_used_1h、total_disk_io_writes_1h、avg_disk_free_1h、max_cpu_user_1h、max_cpu_user_1h、max_cpu_user_1h、max_cpu_user_1h、max_cpu_user_1h、max_cpu_user_1h、max_cpu_user_1h、max_cpu_us_cpu_system_1h，min_cpu_system_1h。 region映射到维度hour，并映射到时间列。

中的 MixedMeasureMappings 房产 TargetConfiguration。 TimestreamConfiguration指定如何将度量映射到派生表中的多度量记录。

在这个具体的示例中，在一条多度量记录中使用 avg_mem_cached_1h、min_mem_free_1h、max_mem_used_1h，度量名称为 mem_aggregates，total_disk_writes_1h，avg_disk_free_1h 用于另一条度量名称为 disk_aggregates 的多度量记录中，最后 max_cpu_user_1h、max_cpu_system_1h、min_cpu_system_1h 被用于另一条度量名称为 cpu_aggregates 的多度量记录中。

在这些映射中，您还可以选择使用 TargetMultiMeasureAttributeName 重命名查询结果列，使其在目标表中使用不同的属性名称。例如，结果列 avg_mem_cached_1h 被重命名为，total_disk_io_writes_1h 被重命名为 total， avgMemCached等等。IOWrites

在为多度量记录定义映射时， LiveAnalytics Timestream for 会检查查询结果中的每一行，并自动忽略具有 NULL 值的列值。因此，对于具有多个度量名称的映射，如果给定行的映射中该组的所有列值均为 NULL，则不会为该行提取该度量名称的值。

例如，在以下映射中，avg_mem_cached_1h、min_mem_free_1h 和 max_mem_used_1h 映射到度量名称 mem_aggrates。如果对于查询结果的给定行，所有这些列值均为 NULL，则 Timestream for 将 LiveAnalytics 不会提取该行的度量 mem_aggregates。如果给定行的九列全部为 NULL，则会在错误报告中看到用户错误报告。

{
    "Name" : "AggsInDifferentMultiMeasureRecords",
    "QueryString" : "SELECT region, bin(time, 1h) as hour, AVG(CASE WHEN measure_name = 'memory_cached' THEN measure_value::double ELSE NULL END) as avg_mem_cached_1h, MIN(CASE WHEN measure_name = 'memory_free' THEN measure_value::double ELSE NULL END) as min_mem_free_1h, MAX(CASE WHEN measure_name = 'memory_used' THEN measure_value::double ELSE NULL END) as max_mem_used_1h, SUM(CASE WHEN measure_name = 'disk_io_writes' THEN measure_value::double ELSE NULL END) as total_disk_io_writes_1h, AVG(CASE WHEN measure_name = 'disk_used' THEN measure_value::double ELSE NULL END) as avg_disk_used_1h, AVG(CASE WHEN measure_name = 'disk_free' THEN measure_value::double ELSE NULL END) as avg_disk_free_1h, MAX(CASE WHEN measure_name = 'cpu_user' THEN measure_value::double ELSE NULL END) as max_cpu_user_1h, MAX(CASE WHEN measure_name = 'cpu_system' THEN measure_value::double ELSE NULL END) as max_cpu_system_1h, MIN(CASE WHEN measure_name = 'cpu_idle' THEN measure_value::double ELSE NULL END) as min_cpu_system_1h FROM raw_data.devops WHERE time BETWEEN bin(@scheduled_runtime, 1h) - 14h AND bin(@scheduled_runtime, 1h) - 2h AND measure_name IN ('memory_cached', 'memory_free', 'memory_used', 'disk_io_writes', 'disk_used', 'disk_free', 'cpu_user', 'cpu_system', 'cpu_idle') GROUP BY region, bin(time, 1h)",
    "ScheduleConfiguration" : {
        "ScheduleExpression" : "cron(0 0/1 * * ? *)"
    },
    "NotificationConfiguration" : {
        "SnsConfiguration" : {
            "TopicArn" : "******"
        }
    },
    "ScheduledQueryExecutionRoleArn": "******",
    "TargetConfiguration": {
        "TimestreamConfiguration": {
            "DatabaseName" : "derived",
            "TableName" : "dashboard_metrics_1h_agg_3",
            "TimeColumn" : "hour",
            "DimensionMappings" : [
                {
                    "Name": "region",
                    "DimensionValueType" : "VARCHAR"
                }
            ],
            "MixedMeasureMappings" : [
                {
                    "MeasureValueType" : "MULTI",
                    "TargetMeasureName" : "mem_aggregates",
                    "MultiMeasureAttributeMappings" : [
                        {
                            "SourceColumn" : "avg_mem_cached_1h",
                            "MeasureValueType" : "DOUBLE",
                            "TargetMultiMeasureAttributeName": "avgMemCached"
                        },
                        {
                            "SourceColumn" : "min_mem_free_1h",
                            "MeasureValueType" : "DOUBLE"
                        },
                        {
                            "SourceColumn" : "max_mem_used_1h",
                            "MeasureValueType" : "DOUBLE",
                            "TargetMultiMeasureAttributeName": "maxMemUsed"
                        }
                    ]
                },
                {
                    "MeasureValueType" : "MULTI",
                    "TargetMeasureName" : "disk_aggregates",
                    "MultiMeasureAttributeMappings" : [
                        {
                            "SourceColumn" : "total_disk_io_writes_1h",
                            "MeasureValueType" : "DOUBLE",
                            "TargetMultiMeasureAttributeName": "totalIOWrites"
                        },
                        {
                            "SourceColumn" : "avg_disk_used_1h",
                            "MeasureValueType" : "DOUBLE"
                        },
                        {
                            "SourceColumn" : "avg_disk_free_1h",
                            "MeasureValueType" : "DOUBLE"
                        }
                    ]
                },
                {
                    "MeasureValueType" : "MULTI",
                    "TargetMeasureName" : "cpu_aggregates",
                    "MultiMeasureAttributeMappings" : [
                        {
                            "SourceColumn" : "max_cpu_user_1h",
                            "MeasureValueType" : "DOUBLE"
                        },
                        {
                            "SourceColumn" : "max_cpu_system_1h",
                            "MeasureValueType" : "DOUBLE"
                        },
                        {
                            "SourceColumn" : "min_cpu_idle_1h",
                            "MeasureValueType" : "DOUBLE",
                            "TargetMultiMeasureAttributeName": "minCpuIdle"
                        }
                    ]
                }
            ]
        }
    },
    "ErrorReportConfiguration": {
        "S3Configuration" : {
            "BucketName" : "******",
            "ObjectKeyPrefix": "errors",
            "EncryptionOption": "SSE_S3"
        }
    }
}

以下是实现此定时查询后目标表的架构。

列 类型 LiveAnalytics 属性类型的时间流

区域

varchar

维度

measure_name

varchar

MEASURE_NAME

时间

timestamp

TIMESTAMP

minCpuIdle

double

多

max_cpu_system_1h

double

多

max_cpu_user_1h

double

多

avgMemCached

double

多

maxMemUsed

double

多

min_mem_free_1h

double

多

avg_disk_free_1h

double

多

avg_disk_used_1h

double

多

总计 IOWrites

double

多

以下是通过 SHOW MEASURES 查询获得的相应度量。

measure_name data_type Dimensions

cpu_Aggregates

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

磁盘聚合

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

mem_aggregates

multi

[{'dimension_name': 'region'，'data_type'：'varchar'}]

示例：使用查询结果中的度量名称将结果映射到单度量记录

以下是一个定时查询的示例，其结果将具体化为单一测量记录。在此示例中，查询结果具有 measure_name 列，其值将用作目标表中的度量名称。您可以在中使用该 MixedMeasureMappings 属性 TargetConfiguration。 TimestreamConfiguration 指定查询结果列与目标表中标量度量的映射。

在以下示例定义中，查询结果应为九个不同的 measure_name 值。您可以在映射中列出所有这些度量名称，并指定要使用哪一列作为该度量名称的单度量值。例如，在此映射中，如果看到给定结果行的 memory_cached 的度量名称，则在将数据写入目标表时，avg_1h 列中的值将用作度量的值。您可以选择使用 TargetMeasureName 为该值提供新的度量名称。

{
    "Name" : "UsingMeasureNameColumnForSingleMeasureMapping",
    "QueryString" : "SELECT region, bin(time, 1h) as hour, measure_name, AVG(CASE WHEN measure_name IN ('memory_cached', 'disk_used', 'disk_free') THEN measure_value::double ELSE NULL END) as avg_1h, MIN(CASE WHEN measure_name IN ('memory_free', 'cpu_idle') THEN measure_value::double ELSE NULL END) as min_1h, SUM(CASE WHEN measure_name IN ('disk_io_writes') THEN measure_value::double ELSE NULL END) as sum_1h, MAX(CASE WHEN measure_name IN ('memory_used', 'cpu_user', 'cpu_system') THEN measure_value::double ELSE NULL END) as max_1h FROM raw_data.devops WHERE time BETWEEN bin(@scheduled_runtime, 1h) - 14h AND bin(@scheduled_runtime, 1h) - 2h AND measure_name IN ('memory_free', 'memory_used', 'memory_cached', 'disk_io_writes', 'disk_used', 'disk_free', 'cpu_user', 'cpu_system', 'cpu_idle') GROUP BY region, bin(time, 1h), measure_name",
    "ScheduleConfiguration" : {
        "ScheduleExpression" : "cron(0 0/1 * * ? *)"
    },
    "NotificationConfiguration" : {
        "SnsConfiguration" : {
            "TopicArn" : "******"
        }
    },
    "ScheduledQueryExecutionRoleArn": "******",
    "TargetConfiguration": {
        "TimestreamConfiguration": {
            "DatabaseName" : "derived",
            "TableName" : "dashboard_metrics_1h_agg_4",
            "TimeColumn" : "hour",
            "DimensionMappings" : [
                {
                    "Name": "region",
                    "DimensionValueType" : "VARCHAR"
                }
            ],
            "MeasureNameColumn" : "measure_name",
            "MixedMeasureMappings" : [
                {
                    "MeasureName" : "memory_cached",
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "avg_1h",
                    "TargetMeasureName" : "AvgMemCached"
                },
                {
                    "MeasureName" : "disk_used",
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "avg_1h"
                },
                {
                    "MeasureName" : "disk_free",
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "avg_1h"
                },
                {
                    "MeasureName" : "memory_free",
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "min_1h",
                    "TargetMeasureName" : "MinMemFree"
                },
                {
                    "MeasureName" : "cpu_idle",
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "min_1h"
                },
                {
                    "MeasureName" : "disk_io_writes",
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "sum_1h",
                    "TargetMeasureName" : "total-disk-io-writes"
                },
                {
                    "MeasureName" : "memory_used",
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "max_1h",
                    "TargetMeasureName" : "maxMemUsed"
                },
                {
                    "MeasureName" : "cpu_user",
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "max_1h"
                },
                {
                    "MeasureName" : "cpu_system",
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "max_1h"
                }
            ]
        }
    },
    "ErrorReportConfiguration": {
        "S3Configuration" : {
            "BucketName" : "******",
            "ObjectKeyPrefix": "errors",
            "EncryptionOption": "SSE_S3"
        }
    }
}

以下是实现此定时查询后目标表的架构。从架构中可以看出，该表使用的是单度量记录。如果您列出表的度量架构，您将看到根据规范中提供的映射写入的九个度量。

列 类型 LiveAnalytics 属性类型的时间流

区域

varchar

维度

measure_name

varchar

MEASURE_NAME

时间

timestamp

TIMESTAMP

measure_value::double

double

MEASURE_VALUE

以下是通过 SHOW MEASURES 查询获得的相应度量。

measure_name data_type Dimensions

AvgMemCached

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

MinMemFree

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

cpu_idle

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

cpu_system

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

cpu_user

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

disk_free

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

disk_used

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

maxMemUsed

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

total-disk-io-writes

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

示例：将结果映射到以查询结果列作为度量名称的单度量记录

在此示例中，您的查询的结果没有度量名称列。相反，在将输出映射到单度量记录时，您希望将查询结果列名称作为度量名称。前面有一个例子，将类似的结果写入多度量记录中。在此示例中，如果符合您的应用场景，您将看到如何将其映射到单度量记录。

同样，您可以使用中的 MixedMeasureMappings 属性来指定此映射 TargetConfiguration。 TimestreamConfiguration。在以下示例中，您会看到查询结果有九列。您可以使用结果列作为度量名称，将值用作单度量值。

例如，对于查询结果中的给定行，列名 avg_mem_cached_1h 用作与列关联的列名和值，avg_mem_cached_1h 用作单度量记录的度量值。您也可以使用 TargetMeasureName 在目标表中使用不同的度量名称。例如，对于 sum_1h 列中的值，映射指定使用 total_disk_io_writes_1h 作为目标表中的度量名称。如果任何列的值为 NULL，则会忽略相应的度量。

{
    "Name" : "SingleMeasureMappingWithoutMeasureNameColumnInQueryResult",
    "QueryString" : "SELECT region, bin(time, 1h) as hour, AVG(CASE WHEN measure_name = 'memory_cached' THEN measure_value::double ELSE NULL END) as avg_mem_cached_1h, AVG(CASE WHEN measure_name = 'disk_used' THEN measure_value::double ELSE NULL END) as avg_disk_used_1h, AVG(CASE WHEN measure_name = 'disk_free' THEN measure_value::double ELSE NULL END) as avg_disk_free_1h, MIN(CASE WHEN measure_name = 'memory_free' THEN measure_value::double ELSE NULL END) as min_mem_free_1h, MIN(CASE WHEN measure_name = 'cpu_idle' THEN measure_value::double ELSE NULL END) as min_cpu_idle_1h, SUM(CASE WHEN measure_name = 'disk_io_writes' THEN measure_value::double ELSE NULL END) as sum_1h, MAX(CASE WHEN measure_name = 'memory_used' THEN measure_value::double ELSE NULL END) as max_mem_used_1h, MAX(CASE WHEN measure_name = 'cpu_user' THEN measure_value::double ELSE NULL END) as max_cpu_user_1h, MAX(CASE WHEN measure_name = 'cpu_system' THEN measure_value::double ELSE NULL END) as max_cpu_system_1h FROM raw_data.devops WHERE time BETWEEN bin(@scheduled_runtime, 1h) - 14h AND bin(@scheduled_runtime, 1h) - 2h AND measure_name IN ('memory_free', 'memory_used', 'memory_cached', 'disk_io_writes', 'disk_used', 'disk_free', 'cpu_user', 'cpu_system', 'cpu_idle') GROUP BY region, bin(time, 1h)",
    "ScheduleConfiguration" : {
        "ScheduleExpression" : "cron(0 0/1 * * ? *)"
    },
    "NotificationConfiguration" : {
        "SnsConfiguration" : {
            "TopicArn" : "******"
        }
    },
    "ScheduledQueryExecutionRoleArn": "******",
    "TargetConfiguration": {
        "TimestreamConfiguration": {
            "DatabaseName" : "derived",
            "TableName" : "dashboard_metrics_1h_agg_5",
            "TimeColumn" : "hour",
            "DimensionMappings" : [
                {
                    "Name": "region",
                    "DimensionValueType" : "VARCHAR"
                }
            ],
            "MixedMeasureMappings" : [
                {
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "avg_mem_cached_1h"
                },
                {
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "avg_disk_used_1h"
                },
                {
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "avg_disk_free_1h"
                },
                {
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "min_mem_free_1h"
                },
                {
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "min_cpu_idle_1h"
                },
                {
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "sum_1h",
                    "TargetMeasureName" : "total_disk_io_writes_1h"
                },
                {
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "max_mem_used_1h"
                },
                {
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "max_cpu_user_1h"
                },
                {
                    "MeasureValueType" : "DOUBLE",
                    "SourceColumn" : "max_cpu_system_1h"
                }
            ]
        }
    },
    "ErrorReportConfiguration": {
        "S3Configuration" : {
            "BucketName" : "******",
            "ObjectKeyPrefix": "errors",
            "EncryptionOption": "SSE_S3"
        }
    }
}

以下是实现此定时查询后目标表的架构。如您所见，目标表正在存储双精度量值为双精度的记录。同样，该表的度量架构显示了九个度量名称。另请注意，由于映射将 sum_1h 重命名为 total_disk_io_writes_1h，因此存在度量名称 total_disk_io_writes_1h。

列 类型 LiveAnalytics 属性类型的时间流

区域

varchar

维度

measure_name

varchar

MEASURE_NAME

时间

timestamp

TIMESTAMP

measure_value::double

double

MEASURE_VALUE

以下是通过 SHOW MEASURES 查询获得的相应度量。

measure_name data_type Dimensions

avg_disk_free_1h

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

avg_disk_used_1h

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

avg_mem_cached_1h

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

max_cpu_system_1h

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

max_cpu_user_1h

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

max_mem_used_1h

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

min_cpu_idle_1h

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

min_mem_free_1h

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]

total-disk-io-writes

double

[{'dimension_name': 'region'，'data_type'：'varchar'}]