0

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.

enter image description here

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.

enter image description here

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?

enter image description here

Help is much appreciated. thanks in advance!

3
  • Are Inventory, Impressions, and View different (but related) concepts? If so, why do you want to store all 3 in the same table instead of normalizing them into 3 tables?
    – J.D.
    Aug 24 at 11:50
  • @J.D. then I have to use join which is pretty inefficient on billions of rows. I guess. Aug 24 at 17:48
  • Joining between datasets that each have billions of rows is not any more inefficient per se, depending on your use cases and predicates. And if you're already planning to materialize the data anyway, you can materialize it as a single dataset as needed. But again, only really depends on if those 3 things are actually distinctly different concepts worth normalizing. Btw, can you please tag which database system and version you're using?
    – J.D.
    Aug 24 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.