Clickhouse: Materialized View not updating

[nathanmarlor]

ClickHouse server version 18.16.0 revision 54412.

I'm matching raised/cleared events within CH using the following:

CREATE TABLE test (ts DateTime, set Int8, clear Int8) ENGINE = Memory()

INSERT INTO test (ts, set, clear) VALUES ('2019-01-01 00:01:00', 1, 0)
INSERT INTO test (ts, set, clear) VALUES ('2019-01-01 00:01:30', 0, 1)

SELECT ts AS RaisedTime, MIN(clear_ts) AS ClearTime, set AS event FROM test ALL INNER JOIN (SELECT ts AS clear_ts, clear AS event FROM test) USING (event) WHERE event > 0 AND clear_ts > ts GROUP BY RaisedTime, event FORMAT CSV

Produces the correct result:

"2019-01-01 00:01:00","2019-01-01 00:01:30",1

However, when this query is moved into a materialized view it stops updating:

CREATE MATERIALIZED VIEW testview ENGINE = Memory() POPULATE AS SELECT ts AS RaisedTime, MIN(clear_ts) AS ClearTime, set AS event FROM test ALL INNER JOIN (SELECT ts AS clear_ts, clear AS event FROM test) USING (event) WHERE event > 0 AND clear_ts > ts GROUP BY RaisedTime, event

SELECT * FROM testview FORMAT CSV

"2019-01-01 00:01:00","2019-01-01 00:01:30",1

INSERT INTO test (ts, set, clear) VALUES ('2019-01-01 00:03:00', 2, 0)
INSERT INTO test (ts, set, clear) VALUES('2019-01-01 00:04:00', 0, 2)

SELECT * FROM testview FORMAT CSV

"2019-01-01 00:01:00","2019-01-01 00:01:30",1

[den-crane]

I think MV solves test JOIN test over inserted buffer not over real table.
And this a bad idea because CH's join places a right table to the memory, so eventually it will stop working with out of memory.

Try another approach
https://gist.github.com/den-crane/49ce2ae3a688651b9c2dd85ee592cb15
https://gist.github.com/den-crane/d03524eadbbce0bafa528101afa8f794

[filimonov]

@nathanmarlor do you have any further questions?

[Rahuljais098]

I have created materialized view in clickhouse database but when inserting a new row in the table Employee and User the view is not updating.

here is my Query
CREATE TABLE Test.Employee (Emp_id Int32, Emp_name String, Emp_salary Int32) ENGINE = Log
CREATE TABLE Test.User (Emp_id Int32, Emp_address String, Emp_Mobile String) ENGINE = Log

CREATE MATERIALIZED VIEW Test.MV_Emp_detailss (Emp_id Int32, Sum(Emp_salary) Int64, Emp_name String, Emp_address String) ENGINE = AggregatingMergeTree PARTITION BY Emp_id ORDER BY Emp_id SETTINGS index_granularity = 8192 AS SELECT Emp_id, Sum(Emp_salary), Emp_name, Emp_address FROM Test.Employee INNER JOIN Test.User USING (Emp_id) GROUP BY Emp_id, Emp_name, Emp_address, Emp_salary

[den-crane]

@Rahuljais098 MV traces only inserts into left table (Test.Employee in your case)

insert into Test.User values(1, 'xyz', '55-5');
insert into Test.MV_Emp_detailss select number%2, toString(number%2), 10 from numbers(10);

SELECT *
FROM Test.MV_Emp_detailss

┌─Emp_id─┬──s─┬─Emp_name─┬─Emp_address─┐
│      1 │ 50 │ 1        │ xyz         │
└────────┴────┴──────────┴─────────────┘

Only Emp_id = 1 inserted ( number%2 = 0 or 1) because of INNER JOIN.

[antonmarin]

Hi. Is it solved?

[den-crane]

@antonmarin it was nothing so solve. It came from Materialized View design.

_MV это insert trigger. Этот триггер следит за инсертами в исходную таблицу, и выполняет над вставленными записями преобразование select и вставляет результат, либо в явно созданную таблицу, либо в неявно созданную inner таблицу._
_Т.е. исходная таблица вообще не читается!!! (кроме стадии populate). Триггер получает от инсерта буфер с записями, поэтому движок исходной таблицы не имеет значения. Например, можно использовать движок Null, такие таблицы не хранят данные вообще, но инсерт в Null триггерит MV и она работает. Причем в этом буфере что самое интересное не весь “инсерт”, т.к. вставленные одним инсертом записи могут разделится в несколько буферов и тогда MV сработает несколько раз, и т.о. схлопнет / сгруппирует записи инсерта не до конца. Вы вставили инсертом 999 записей, а MV 3 раза вставит count()=333._