Materialized view in clickhouse database not showing data

Question

I use Clickhouse database.

I have text coming in segments. I need to group all text in one row without duplicate segments

when count(segment) = total Segment.

I create the first table to insert the segments. then I create a MATERIALIZED view to removing duplicate rows and group all segments.

then move it to the second table but my problem is the view does not show the values. when I run the query it is working fine

First Table :

CREATE table test_table_1
( 
    id UInt16,
    text String,
    totalSegment UInt16,
    segmentNumber UInt16,
    reference UInt16,
    insertTime DateTime
)
ENGINE = ReplacingMergeTree()
order by (id , text , totalSegment , segmentNumber , reference );

Second Table :

CREATE table test_table_2
(
    message String,
    reference UInt16,
    totalSegment UInt16
)
ENGINE = MergeTree()
order by (reference );

MATERIALIZED VIEW :

CREATE MATERIALIZED VIEW consumer To test_table_2
as
SELECT  groupArray(text) as message ,reference ,totalSegment 
from ( 
    select * 
    from 
    ( 
        SELECT COUNT(*) as countSegment, totalSegment, reference 
        from test_table_1
        where (text,totalSegment,reference,insertTime) in (
                    select    text,totalSegment,reference, max(insertTime) as insertTime 
                    from      test_table_1 
                    group by  text,totalSegment,segmentNumber,reference
        )
        group by  totalSegment, reference
        HAVING count(*) = totalSegment 
        )as a
        left join (
                select    text,segmentNumber,reference, max(insertTime) as insertTime 
                from      test_table_1 
                group by  text,segmentNumber,reference
        )as b using (reference)
        order by segmentNumber 
    )
grousp by reference ,totalSegment
order by reference ;

Insert data:

INSERT INTO test_table_1 (id,text, totalSegment, segmentNumber, reference,insertTime) VALUES(1, 'A1 ', 3, 1, '101', now() );
INSERT INTO test_table_1 (id,text, totalSegment, segmentNumber, reference,insertTime) VALUES(2, 'B1 ', 2, 1, '202', now() );
INSERT INTO test_table_1 (id,text, totalSegment, segmentNumber, reference,insertTime) VALUES(3, 'C2 ', 3, 2, '303', now() );
INSERT INTO test_table_1 (id,text, totalSegment, segmentNumber, reference,insertTime) VALUES(4, 'A3 ', 3, 3, '101', now() );
INSERT INTO test_table_1 (id,text, totalSegment, segmentNumber, reference,insertTime) VALUES(5, 'A2 ', 3, 2, '101', now() );
INSERT INTO test_table_1 (id,text, totalSegment, segmentNumber, reference,insertTime) VALUES(6, 'C1 ', 3, 1, '303', now() );
INSERT INTO test_table_1 (id,text, totalSegment, segmentNumber, reference,insertTime) VALUES(7, 'C3 ', 3, 3, '303', now() );
INSERT INTO test_table_1 (id,text, totalSegment, segmentNumber, reference,insertTime) VALUES(8, 'B2 ', 2, 2, '202', now() );

Answer 1

MV is an insert trigger. It NEVER reads the source table -- test_table_1 !!!

https://den-crane.github.io/Everything_you_should_know_about_materialized_views_commented.pdf

https://youtu.be/ckChUkC3Pns?t=9326