NestedAndRepeatedFields.sql

create table ab_stream (a array<struct<k int, v string>>, b array<struct<k int, v int>>);

insert into ab_stream values 
([(1, "one")], [(2, 200)]);

Views.sql

-- Initial setup.
create table ab_stream (t string, r json); -- A small number of recently appended rows in all tables.
create table a_compact (k int, v string); -- Periodically compacted from ab_stream.
create table b_compact (k int, v int); -- Periodically compacted from ab_stream.

create view a_stream as 
select 
    cast(json_extract(r, '$.k') as int), 
    cast(json_extract(r, '$.v') as string) 
from ab_stream
where t = 'a';

create view b_stream as 
select 
    cast(json_extract(r, '$.k') as int), 
    cast(json_extract(r, '$.v') as int) 
from ab_stream
where t = 'b';

create view a as 
select * from a_compact
union all select * from a_stream;

create view b as 
select * from b_compact
union all select * from b_stream;

-- Insert new rows.

insert into ab_stream values 
('a', json '{"k":1,"v":"one"}'), 
('b', json '{"k":2,"v":200}');

-- Compact recently added rows.
begin;

insert into a_compact
select * from a_stream;

delete from ab_stream
where t = 'a';

commit;