问题

我正在尝试在Clickhouse上创建一个基于两个其他表的Materialized view，下面是这些表的定义：

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

所有表和视图都已正确创建，预期情况下表中没有数据。现在我向t1和t2表中插入数据：

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());

这些操作正确地将数据插入了t1和t2表，但是当我查看Materialized view 时，它中没有数据。

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.

我不确定为什么会出现这种情况。我是否遗漏了Clickhouse中Materialized View的工作方式？有谁能帮忙或指向相关文档？我一直在这个链接上查看，但对于连接的Materialized View似乎不起作用。

英文:

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 (&#39;1&#39;, &#39;First&#39;, NOW());
insert into t1 values (&#39;2&#39;, &#39;Second&#39;, NOW());
insert into t2 values (&#39;1&#39;, &#39;First 1&#39;, 5, NOW());
insert into t2 values (&#39;1&#39;, &#39;First 2&#39;, 5, NOW());
insert into t2 values (&#39;2&#39;, &#39;Second 1&#39;, 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.