[SOLVED] How do i design a schema with proper DB engine to accumulate data depending on this need on clickhouse or in any other database?

How do i design a schema with proper DB engine to accumulate data depending on this need on clickhouse or in any other database?

We're a new Adtech company and I was planning to design a database where I'll pull all the data to a single table and then make new tables with a materialized views for others to generate multiple reports.

Say we have Inventory, impression, views for multiple reasons.

Our main table looks like this, to recreate this

CREATE TABLE report.empty_summing (times DateTime64,inventory_id String,city Nullable(String), country Nullable(String),inventory Int32 default 0, impression Int32 default 0, views Int32 default 0) ENGINE=SummingMergeTree() primary key inventory_id;

When a request comes from google ADX to our Adengine , it has a unique id which is "inventory_id" and other parameters like country, city..... other string type parameters are inserted.

When 3 types of data are inserted it looks like this.

You can see that Every row have their values inserted but I want to

Our inventory request insert looks like this.

INSERT INTO report.empty_summing (times,inventory_id,country,city,inventory,impression,views) VALUES (now(),'7120426e6abd0b04ec8c777460a78bdf4b9de0','Bangladesh','Dhaka',1,0,0);

Our impression insert looks like this.

INSERT INTO report.empty_summing (times,inventory_id,impression) VALUES (now(),'7120426e6abd0b04ec8c777460a78bdf4b9de0',1);

Our view insert looks like this.

INSERT INTO report.empty_summing (times,inventory_id,views) VALUES (now(),'7120426e6abd0b04ec8c777460a78bdf4b9de0',1);

You can see that "inventory_id" is the same for all these rows. is there any DB engine or any technique I can use where data will be merged and look like this?

Help is much appreciated. thanks in advance!

Solution