clickhousematerialized-views

ClickHouse materialized view not populating AggregatingMergeTree table correctly


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

  1. Is there something wrong with my materialized view definition or aggregation logic?
  2. Are there additional steps required to ensure the materialized view populates the destination table correctly?
  3. Could this be related to how ClickHouse handles AggregateFunction types in materialized views?

Solution

  • 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;