ClickHouse – Troubleshooting Materialized View Issues

clickhousedatabase-designschema

I’m new to ClickHouse and having an issue with MV. I have a record table which is the data source. I’m inserting all the data here. Then created another table called adv_general_report using mv_adv_general_report materialized view.

This is my schema.

Records table data.

The odd part is after inserting the data to record table, the sum of impression is perfectly adding to both adv_general_report and mv_adv_general_report materialized view but views and clicks are always showing zero.

You can see running this query. That showing amount of view

SELECT sum(views) as views from records;

enter image description here

But if you run this

select sum(views) as views from adv_general_report;

enter image description here

It’s 0 . also the select query used for a materialized view is showing the sum of view perfectly. Any idea why?

Best Answer

  • it doesn't need to apply any aggregation functions to columns from sorting key - campaign_id, adgroup_id, ads_id, MINUTE
CREATE TABLE IF NOT EXISTS adv_general_report (
    campaign_id Int32, /* <<--- */
    adgroup_id Int32,
    ads_id Int32,
    MINUTE Int32,

    TIME SimpleAggregateFunction(any,DateTime) DEFAULT now(),
    /* .. */
    advertiser_cost SimpleAggregateFunction(SUM,Float64) DEFAULT 0.00
)
engine = AggregatingMergeTree
    PARTITION BY toYYYYMM(TIME)
    ORDER BY (MINUTE,ads_id,campaign_id,adgroup_id)
    PRIMARY KEY (MINUTE,ads_id,campaign_id,adgroup_id)
    SETTINGS index_granularity = 8192;

CREATE MATERIALIZED VIEW mv_adv_general_report TO adv_general_report
    AS SELECT
          campaign_id, /* <<--- */
          adgroup_id,
          ads_id,
          MINUTE,

          any(TIME) AS TIME,
          /* .. */
          SUM(advertiser_cost) AS advertiser_cost
        FROM records
        WHERE isNotNull(records.campaign_id)
        GROUP BY campaign_id, adgroup_id, ads_id, MINUTE;
  • need to pass GROUP BY clause to select data
select campaign_id, sum(views) as views 
from adv_general_report
group by campaign_id;
Related Question