Materialized view has no data

[Václav Brůžek]

Hi, 
I've tried to create a materialized view which would aggregate accesses to a given website for each day and which would be aggregated by id of a client. Source data for this view are inserted to table traffic which has following structure:

CREATE TABLE traffic
(
`id_client` UInt16,    
`domain_l2` String,  
`geoip_country_code2` String,  
`query` String,  
`server` String,  
`timestamp` DateTime,  
`ttl` UInt16
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/shard_0/traffic_db', 'replica_00')
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (intHash32(id_client), timestamp)
SAMPLE BY intHash32(id_client)
TTL timestamp + toIntervalDay(11)
SETTINGS index_granularity = 8192;

For the view I wanted to have a destination table:

CREATE TABLE traffic_daily_aggregated
(
    `domain_l2` String,
    `timestamp` DateTime,
    `sum_hits` AggregateFunction(count),
    `id_client` UInt16
)
ENGINE = AggregatingMergeTree
PARTITION BY domain_l2
ORDER BY (domain_l2, timestamp, id_client)

and the view in question:

CREATE MATERIALIZED VIEW traffic_daily_aggregated_mv
TO traffic_daily_aggregated
AS
SELECT
    toDate(timestamp) AS timestamp,
    domain_l2,
    countState() as sum_hits,
    id_client
FROM traffic
GROUP BY
    domain_l2,
    timestamp,
    id_client

However I don't see any data in the table traffic_daily_aggregated after some data were inserted to the source table. I'm not sure whether the Replicated table is the reason that I cannot see any data. I've also tried changing the engine on the destination table traffic_daily_aggregated to ReplicatedMergeTree('/clickhouse/tables/shard_0/traffic_db_mv', 'replica_00') but to no avail. I'm running two CH instance 21.3 with 3 Zookeepers.

[Pavel Kruglov]

Hi! Most likely the reason is that Materialized view is created just on one of the instances (where the create query was executed). So, if the data is inserted into replicated table in another replica, it won't be inserted into destination table as there is no mat view on this replica. There are two possible solutions I think:

1) Don't use separate table for aggregated data and use just materialized view but with engine ReplicatedAggregatingMergeTree (you can query this view as a table)

2) Create the same materialize view on each replica.

среда, 12 апреля 2023 г. в 15:06:21 UTC+2, vaclav...@whalebone.io: