I am working with ClickHouse and trying to set up a materialized view to populate an AggregatingMergeTree
table. However, the destination table is not being populated correctly, and queries on the aggregated data are not returning the expected results.
Here is the setup:
Source table
CREATE TABLE crypto_trade_mtm (
trade_id String,
counterparty String,
mtm Array(Float64)
)
Engine = MergeTree
ORDER BY trade_id;
Destination table
CREATE TABLE crypto_trade_mtm_aggs_dest (
counterparty String,
mtm_sum AggregateFunction(sumForEach, Array(Float64))
)
Engine = AggregatingMergeTree
ORDER BY counterparty;
Materialized view
CREATE MATERIALIZED VIEW crypto_trade_mtm_aggs_view
TO crypto_trade_mtm_aggs_dest
AS SELECT counterparty, sumForEachState(mtm)
FROM crypto_trade_mtm
GROUP BY counterparty;
Test data insert
INSERT INTO crypto_trade_mtm VALUES ('001', 'Company A', [1.0, 2.0]);
Querying aggregated data
SELECT counterparty, sumForEachMerge(mtm_sum)
FROM crypto_trade_mtm_aggs_dest
GROUP BY counterparty;
Despite following the documentation, the destination table does not seem to reflect the inserted data correctly. I have tried removing the ORDER BY
clause in the materialized view definition and ensured that the GROUP BY
clause matches the ORDER BY
clause of the destination table, but the issue persists.
Questions
AggregateFunction
types in materialized views?When defining your materialized view, the columns in your SELECT
are not matching the columns in the destination table. You are simply missing an AS mtm_sum
clause in your view:
CREATE MATERIALIZED VIEW crypto_trade_mtm_aggs_view
TO crypto_trade_mtm_aggs_dest
AS SELECT
counterparty,
sumForEachState(mtm) AS mtm_sum
FROM crypto_trade_mtm
GROUP BY counterparty;