Inserting rows from the streaming tables into one table in realtime

Question

I have two tables say DOG and FISH. Table DOG has columns

name|species-name|neighbourhood

Table FISH has columns

name|species-name|water-type

In both of the tables data is streaming in realtime. I want to add the data from each of the table to a new table Animals in realtime having columns.

name|species-name|neighbourhood|water-type

For eg:- for dog table two rows are streamed ie

yxz, shephard, london
abc, poodle, NYC

for fish table two rows are streamed ie

lkj, Tuna, saltwater
ghf, Cod, freshwater

I want data of both these table into animal table like

name|species-name|neighbourhood|water-type
----+------------+-------------+----------
yxz  shephard     london
abc  poodle       NYC
lkj  Tuna                       saltwater
ghf  Cod                        freshwater
------------------------------------------

i have used materialzied view for one table, but i cant seem to get my head around how can i use it for two table join.

Answer 1

As far as I understand you want to union two data streams into one table.

You can just create materialized view for each of the tables (dogs and fish)

CREATE MATERIALIZED VIEW fish_mt TO animal AS
SELECT `name`, `species-name`, `water-type` FROM fish;

CREATE MATERIALIZED VIEW dogs_mt TO animal AS
SELECT `name`, `species-name`, `neighbourhood` FROM dogs;

Your result table (animal) should contain the columns from both of the source tables.

---

If you want to change the schema of result table (animal) dynamically, I guess there is no straight way to do it now (version 21.12). But soon support for semi-structured JSON data will be added, you can look there. https://github.com/ClickHouse/ClickHouse/pull/22535