update materialized view with join statement

Question

Suppose I have 2 tables A and B. I create a MV(materialized view) with a join query of two tables, psuedo like:

create materialized view a_b engine = Memory as 
select * from(
    select * from A
) all inner join (
    select * from B
) using some_col;

I known that a_b is only updated when inserting data into table A and nothing else happen when push data to B. I want my MV have to update when both table are updated.

My workaround is to create another MV that change postition of A, B and point to a_b like

create materialized view a_b_2 to a_b as 
select * from(
    select * from B
) all inner join (
    select * from A
) using same_col;

I have some questions about this approach:
1. Are there any more legal way to archive same effect in clickhouse?
2. Suppose I have 2 incoming batches data BD_A and BD_B are going to insert to A and B simultaneously. Some data of 2 batches themself (BD_A_B) is fit join condition . Is there any chance that the MV lost those BD_A_B because MV a_b processes BD_A with before-inserted B and MV a_b_2 processes BD_B with before-inserted A.

Answer 1

As far as I understand, you are trying to have a workaround of a limitation.

Clickhouse does not support multiple source tables for a MV and they have quite good reasons for this. I actually asked this to devs and got this answer:

In ClickHouse materialized view behaves more like BEFORE INSERT TRIGGER, each time processing new block arrived with insert.

So that is quite natural limitation as inserts to 2 different table will come asynchronously and you usually expect to see in JOINs whole table not only newly arrived blocks.