MV_poplulating_with_freeze

MV_poplulating_with_freeze.sql

create table source (A Int64, B String) Engine=MergeTree order by A;
insert into source values(1,'1');

--
-- stop ingestion
--

alter table source freeze;

create materialized view newMV Engine=SummingMergeTree order by A 
as select A, count() cnt from source group by A;

-- 
-- start ingestion (downtime was only a couple seconds)
--

create table temp_snapshot as source;

-- in the shell 
-- mv /var/lib/clickhouse/shadow/1/data/default/source/* /var/lib/clickhouse/data/default/temp_snapshot/detached/
-- cd /var/lib/clickhouse/data/default/temp_snapshot/detached/
-- let i=1;for f in `ls -1` ; do echo $i $f;((i++)); echo "alter table temp_snapshot attach part '$f';"|clickhouse-client ; done

-- new ingestion
insert into source values(1,'1');
insert into source values(2,'2');

-- source has all rows
SELECT * FROM source

┌─A─┬─B─┐
│ 2 │ 2 │
│ 1 │ 1 │
│ 1 │ 1 │
└───┴───┘

-- newMV has rows inserted after start ingestion
SELECT * FROM newMV;
┌─A─┬─cnt─┐
│ 2 │   1 │
│ 1 │   1 │
└───┴─────┘

-- temp_snapshot has rows inserted before stop ingestion 
-- temp_snapshot does not use disk space because files are immutable and they just hardlinks to the table source files
SELECT * FROM temp_snapshot
┌─A─┬─B─┐
│ 1 │ 1 │
└───┴───┘

-- 1. old rows could be added directly 
insert into `.inner.newMV` select * from temp_snapshot;

-- Final result
SELECT * FROM newMV;

┌─A─┬─cnt─┐
│ 1 │   1 │
│ 2 │   1 │
│ 1 │   1 │
└───┴─────┘

-- 2. or using temporary MV if MVselect needs group by
create table temp_null as source Engine=Null;

create materialized view tempMV to `.inner.newMV` 
as select A, count() cnt 
from temp_null group by A;
 
insert into temp_null select * from temp_snapshot;

SELECT * FROM newMV;

┌─A─┬─cnt─┐
│ 1 │   1 │
│ 2 │   1 │
│ 1 │   1 │
└───┴─────┘

drop table tempMV;
drop table temp_null;
drop table temp_snapshot;

OPTIMIZE TABLE newMV FINAL;

SELECT * FROM newMV
┌─A─┬─cnt─┐
│ 1 │   2 │
│ 2 │   1 │
└───┴─────┘