ADX中基于materialized view创建新视图报错的解决求助

这是Stack Overflow帖子「materialized view || Low memory condition || ADX || KQL」的后续咨询。尝试在Azure Data Explorer（ADX）中基于已有的物化视图创建新的物化视图时遇到错误，参考官方文档后仍未解决，寻求协助。

操作流程 #

• 创建源表并插入测试数据：

.create table MySourceTable(col1:int, col2:int, Value:int, EventUTCDateTime:datetime);

.set-or-append  MySourceTable <|
let MySourceTable = datatable(col1:int, col2:int, Value:int, EventUTCDateTime:datetime)
[
1,2,10,'2022-01-01 01:00:01',
1,2,10,'2022-01-01 01:00:01',
1,2,30,'2022-01-01 01:05:01',
4,5,40,'2022-01-01 01:30:01'
];
MySourceTable;

• 创建用于去重的物化视图DeduplicatedTable：

.create async materialized-view with ( backfill=true, effectiveDateTime=datetime(2022-01-01))
DeduplicatedTable  on table MySourceTable
{
MySourceTable
| project col1, col2, Value, EventUTCDateTime
| distinct *
| extend EventUTCDate = bin(EventUTCDateTime, 30m)
| project col1, col2, Value, EventUTCDate
| summarize take_any(*) by col1, col2,Value, EventUTCDate
};

• 尝试基于DeduplicatedTable创建聚合用的物化视图DailyUsage时触发错误：

.create materialized-view DailyUsage on materialized-view DeduplicatedTable
{
    DeduplicatedTable
    | summarize count(), dcount(col1) by Day=bin(EventUTCDate, 1d)
};

错误信息 #

Error

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

问题原因与解决方案 #

原因 #

ADX对嵌套物化视图有严格限制：仅允许在包含单个any()/anyif()/take_any()/take_anyif()聚合的物化视图上创建新的物化视图。当前的DeduplicatedTable虽然使用了take_any(*)，但前序包含distinct *操作，且分组键涉及多列，不符合嵌套创建的要求。

解决方案 #

方案1：直接基于源表创建聚合物化视图（推荐）

将去重与聚合逻辑合并到同一个物化视图中，绕过嵌套限制：

.create async materialized-view with (backfill=true, effectiveDateTime=datetime(2022-01-01))
DailyUsage on table MySourceTable
{
    MySourceTable
    // 先去重
    | distinct col1, col2, Value, EventUTCDateTime
    | extend EventUTCDate = bin(EventUTCDateTime, 30m)
    | summarize take_any(*) by col1, col2, Value, EventUTCDate
    // 再做日度聚合
    | summarize count(), dcount(col1) by Day=bin(EventUTCDate, 1d)
}

方案2：调整现有物化视图以符合嵌套要求

修改DeduplicatedTable的定义，使其仅包含单个take_any()类聚合，结构满足嵌套条件：

.create async materialized-view with (backfill=true, effectiveDateTime=datetime(2022-01-01))
DeduplicatedTable on table MySourceTable
{
    MySourceTable
    | extend EventUTCDate = bin(EventUTCDateTime, 30m)
    // 仅保留单个take_any聚合，分组键明确
    | summarize take_any(EventUTCDateTime, Value) by col1, col2, EventUTCDate
};

之后即可基于修改后的视图创建DailyUsage：

.create materialized-view DailyUsage on materialized-view DeduplicatedTable
{
    DeduplicatedTable
    | summarize count(), dcount(col1) by Day=bin(EventUTCDate, 1d)
};

内容的提问来源于stack exchange，提问作者Brahmaiah Takkellapati