Q: Materialized view in clickhouse database not showing data 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() );


A: 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
