I'm trying to create a Materialized view on Clickhouse which is a join on two other tables. The definitions for the tables are shown below:
CREATE TABLE t1(tId String, tName String, timestamp DateTime) ENGINE = MergeTree ORDER BY timestamp;
CREATE TABLE t2(sId String, sName String, sLength Int64, timestamp DateTime) ENGINE = MergeTree ORDER BY timestamp;
CREATE TABLE jt (`timestamp` DateTime,`tId` String CODEC(ZSTD(1)),`sName` String,`sLength` Int64,`tName` String) ENGINE = MergeTree PARTITION BY toDate(timestamp) ORDER BY timestamp;
CREATE MATERIALIZED VIEW jt_mv TO jt (`timestamp` DateTime,`tId` String CODEC(ZSTD(1)), `sName` String,`sLength` Int64,`tName` String ) AS SELECT t1.timestamp AS timestamp, t1.tId AS tId, t1.tName AS tName, t2.sName AS sName, t2.sLength AS sLength FROM (SELECT timestamp,tId,tName FROM t1 ) AS t1 INNER JOIN (SELECT tId, sName, sLength, timestamp FROM t2) AS t2 ON t1.tId = t2.tId
All tables and view are created correctly, and as expected there's no data in any tables. Now I insert data into the t1 and t2 tables
insert into t1 values ('1', 'First', NOW());
insert into t1 values ('2', 'Second', NOW());
insert into t2 values ('1', 'First 1', 5, NOW());
insert into t2 values ('1', 'First 2', 5, NOW());
insert into t2 values ('2', 'Second 1', 10, NOW());
This inserts data into t1 and t2 correctly, but when I look at the materialized view, there's no data in it.
SELECT count(*)
FROM t1
2 rows in set. Elapsed: 0.005 sec.
SELECT count(*)
FROM t2
3 rows in set. Elapsed: 0.006 sec.
SELECT count(*)
FROM jt
0 rows in set. Elapsed: 0.004 sec.
SELECT count(*)
FROM jt_mv
0 rows in set. Elapsed: 0.003 sec.
I'm unsure why this is the case. Am I missing something with how MVs work in Clickhouse? Can anyone help or point me to the documentation? I've been following this, but it's not working with the join MV.
MatView is an on insert trigger, it's triggered by insertion into the most left table (in the from section).
https://den-crane.github.io/Everything_you_should_know_about_materialized_views_commented.pdf
https://clickhouse.com/blog/using-materialized-views-in-clickhouse
https://kb.altinity.com/altinity-kb-schema-design/materialized-views/