从ReplicatedAggregationMergeTree实体化视图中选择countMerge

Question

最近我开始使用clickhouse，遇到了一些麻烦。我使用的集群有3个碎片，每个碎片都有一个额外的复制，因此总共有6个服务器。

我在包含完整数据的本地表上创建本地MV(称为tbl(，并基于本地MV创建分布式MV。包含完整数据的本地表，或者tbl使用ReplicatedMergeTree作为引擎。本地MV使用ReplicatedAggregationMergeTree作为引擎。此外，我用POPULATE重新创建了测试数据库中的每个MV。这样，就不可能重复插入。

问题在于，当我从分布式MV中选择countMerge时，我得到了正确答案的两倍(即，如果正确答案是50，我得到100。(，而从分布式MV中选择uniqExactMerge将给出正确的结果。

这是我的脚本：

本地MV脚本：

CREATE DATABASE IF NOT EXISTS test ON CLUSTER cc_cluster;
CREATE MATERIALIZED VIEW IF NOT EXISTS
test.user_event_stat_scene_mv_local_test_v2 ON CLUSTER cc_cluster
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/{layer}-{shard}/test.user_event_stat_scene_mv_local_test_v2', '{replica}')
PARTITION BY (dt)
ORDER BY (dt, scene)
POPULATE
AS select
countState(1) as expos,
countState(if(click>0, 1, null)) as clicks,
sumState(if(click=1, watch, 0)) as dr,
countState(if(click=1 and tbl.cost_cnt>0, 1, null)) as cost_cnt,
sumState(if(click=1, tbl.cost_total, 0)) as cost_total,
countState(if(click=1 and tbl.chat_cnt>0, 1, null)) as chat_cnt,
uniqExactState(recom_token) as item_expos,
uniqExactState(if(click=1, recom_token, null)) as item_clicks,
uniqExactState(uid) as user_expos,
uniqExactState(if(click=1, uid, null)) as user_clicks,
uniqExactState(if(click=1 and tbl.cost_cnt>0, uid, null)) as user_costs,
uniqExactState(if(click=1 and tbl.chat_cnt>0, uid, null)) as user_chats,
scene,
toFixedString(dt, 8) as dt
FROM recom_stats_dws.user_event_log_day_local as tbl
GROUP BY dt, scene;

分布式MV脚本如下：

CREATE TABLE IF NOT EXISTS
test.user_event_stat_scene_mv_all_test_v2
ON CLUSTER cc_cluster
AS test.user_event_stat_scene_mv_local_test_v2
ENGINE = Distributed(cc_cluster, test, user_event_stat_scene_mv_local_test_v2, rand());

查询脚本为：

select
countMerge(expos) as expos,
countMerge(clicks) as clicks,
sumMerge(dr) as dr,
countMerge(cost_cnt) as cost_cnt,
sumMerge(cost_total) as cost_total,
countMerge(chat_cnt) as chat_cnt, 
uniqExactMerge(item_expos) as item_expos, 
uniqExactMerge(item_clicks) as item_clicks, 
uniqExactMerge(user_expos) as user_expos,
uniqExactMerge(user_clicks) as user_clicks,
uniqExactMerge(user_costs) as user_costs,
uniqExactMerge(user_chats) as user_chats,
scene,
dt
FROM test.user_event_stat_scene_mv_all_test_v2 as tbl
GROUP BY dt, scene
order by dt, scene;

PS:

• 本地MV=test.user_event_stat_scene_MV_local_test_v2
• 分布式MV=test.user_event_stat_scene_MV_all_test_v2
• 包含完整数据的本地表=recom_stats_dws.user_event_log_day_local

我可能在哪一部分做错了什么？希望得到您的帮助XD

Answer 1

https://github.com/ClickHouse/ClickHouse/issues/16208

你已经执行了两次POPULATE。在每个复制副本上。数据翻了一番。

您只需要在一个复制副本上运行POPULATE。