如何优化adx上的重复查询和摘要查询

Question

我们正在定期摄取简单的设备读数到adx中。然而，0.28%的摄取量可能会随新的阅读值而更新。这可以通过简单的物化视图和arg_max来纠正。然而，我们也希望优化每天、每月和每年的阅读摘要。问题是，我不能在物化视图之上创建物化视图。也不可能用两个摘要子句来表示物化的视图。

示例：

.create table Readings (Timestamp:datetime, DeviceName:string, IngestTime:datetime, Reading:decimal)

.ingest inline into table Readings <|
    "2022-10-31 23:00:00.0000000", "EX", "2022-11-06 11:02:29.5690000",0.733 
    "2022-10-31 22:00:00.0000000", "EX", "2022-11-05 11:05:36.4230000",0.763
    "2022-10-31 22:00:00.0000000", "EX", "2022-11-01 07:08:55.9580000",0.5
    "2022-10-31 22:00:00.0000000", "EX", "2022-11-02 11:04:42.7050000",0.5
    "2022-10-31 21:00:00.0000000", "EX", "2022-11-05 11:05:36.4230000",0.856
    "2022-10-31 20:00:00.0000000", "EX", "2022-11-05 11:05:36.4230000",0.827
    "2022-10-31 20:00:00.0000000", "EX", "2022-11-02 11:04:42.7050000",0
    "2022-10-31 20:00:00.0000000", "EX", "2022-11-01 07:08:55.9580000",0
    "2022-10-31 19:00:00.0000000", "EX", "2022-11-05 11:05:36.4230000",0.935

.create materialized-view with (backfill=true, DocString="Only latest measures by arg_max", 
        effectiveDateTime=datetime(2019-01-01), 
        MaxSourceRecordsForSingleIngest=10000000, 
        Concurrency=5 
) ReadingsLatest on table Readings { Readings 
| summarize arg_max(IngestTime, *) by DeviceName, Reading } 

给予输出

2022-10-31T23:00:00Z     "EX"  2022-11-06T11:02:29.569Z   0.733
2022-10-31T22:00:00Z    "EX"  2022-11-05T11:05:36.423Z   0.763
2022-10-31T21:00:00Z    "EX"  2022-11-05T11:05:36.423Z   0.856
2022-10-31T20:00:00Z    "EX"  2022-11-05T11:05:36.423Z   0.827
2022-10-31T19:00:00Z    "EX"  2022-11-05T11:05:36.423Z   0.935

当我们执行聚合查询时，问题在于性能：

ReadingsLatest
| summarize Reading_Day = sum(Reading) by Day = startofday(datetime_utc_to_local(Timestamp, 'Europe/Oslo')), DeviceName

因此，我们希望在这方面有一个物化的观点，但我们不知道如何实现：

.create materialized-view with (backfill=true, DocString="Only latest measures by arg_max", 
        effectiveDateTime=datetime(2019-01-01), 
        MaxSourceRecordsForSingleIngest=10000000, 
        Concurrency=5 
) ReadingsLatestDay on materialized-view ReadingsLatest { ReadingsLatest 
| summarize Reading_Day = sum(Reading) by Day = startofday(datetime_utc_to_local(Timestamp, 'Europe/Oslo')), DeviceName }

//fails with:
Cannot create materialized view 'ReadingsLatestDay': Materialized view can only be created on top of another materialized view which includes a single any()/anyif()/take_any()/take_anyif() aggregation.

同时尝试：

.create materialized-view with (backfill=true, DocString="Only latest measures by arg_max", 
        effectiveDateTime=datetime(2019-01-01), 
        MaxSourceRecordsForSingleIngest=10000000, 
        Concurrency=5 
) ReadingsLatestDay on table Readings { Readings 
| summarize arg_max(IngestTime, *) by DeviceName, Reading  
| summarize Reading_Day = sum(Reading) by Day = startofday(datetime_utc_to_local(Timestamp, 'Europe/Oslo')), DeviceName }

//fails with:
Cannot create materialized view 'ReadingsLatestDay': Materialized views query can only include a single summarize operator over the source table.

我们考虑过的另一个选择是删除后来更新的读数，但这似乎也很困难。我们找不出语法。

Answer 1

您所得到的错误是预期的--只有当第一个视图是take_any(*)类型时才支持物化视图--参见医生来了。不支持同一物化视图中的单个聚合。在ADX中，没有预先计算这两个聚合的构建。您可以创建第一个物化视图(arg_max())，然后使用任意一个编制工具编排自己的管道，该管道定期查询物化视图并使用从查询命令中摄取保存每日聚合。