Skip to content

James O'Claire

My personal site

Menu
  • Home
  • Projects
    • Projects Overview
    • App Goblin: App Scraper
    • Android Apps
    • Ads Data Dash
  • Contact
Menu

ClickHouse: Refreshing Take on Materialized Views

Posted on January 26, 2024October 6, 2024 by James O'Claire

Working on my Open Source MMP I have been seeing how much of it will work with ClickHouse. Unfortunately combining multiple streaming data sources in ClickHouse was proving difficult as data would semi randomly not join correctly.

This led to a potential fix by using the very recent ClickHouse refreshable materialized views. These refreshable views work much closer to how materialized views work in other databases like PostgreSQL but come with additional benefits like setting the refreshes inside the materialized view logic itself.

Official Documentation:
https://clickhouse.com/docs/en/sql-reference/statements/create/view#refreshable-materialized-view

At the time of writing, refreshable views are new enough that they require setting this to enable support. Since this is needed per session in which a table is created, you will need to use this setting. This shouldn’t be necessary after Q2 2024.

SET allow_experimental_refreshable_materialized_view = 1;

Then to make the view refreshable you just add REFRESH EVERY {INT} {TIME} like this:

CREATE MATERIALIZED VIEW attribute_impressions_mv
REFRESH EVERY 5 SECOND
TO attributed_impressions -- Specify the destination table
AS
WITH
merged_impression_event AS (
    -- Ranked rows by impression time
    SELECT 
            app.event_time AS app_event_time,
            app.store_id AS store_id,
            app.event_id,
            ...

What I then discovered though was that once a refreshable view is used, it will replace the entire contents of the destination table. This means that they likely will need further management in the future as the data grows. Additionally, they cannot be combined with the columnar data inserts I was using previously with AggregatingMergeTree.

So, once you add a refreshable view, all higher level aggregations will likely all have to be refreshable as well.

In the end, I was able to use refreshable views to solve my problem, but I am not certain they wont cause delays later due to the way they seem to refresh across all data. Perhaps I’ll have to find a way to narrow the range they aggregate.

Categories

  • Development
  • Mobile Marketing and Advertising
  • Uncategorized

Recent Posts

  • Apple: The Silent Advertising Monopoly
  • Watching the Watchers: What to track when tracking app trackers?
  • Free Mobile App ASO Tools: Fastest Growing Apps & Keyword Research added to AppGoblin
  • 2025 How to Sniff Android HTTPS Traffic with Waydroid & mitm-proxy
  • Easiest way to get Tanstack Table v8 working with Svelte 5!

Recent Comments

    Archives

    • May 2025
    • April 2025
    • March 2025
    • February 2025
    • January 2025
    • December 2024
    • November 2024
    • October 2024
    • March 2024
    • February 2024
    • January 2024
    • November 2023
    • October 2023
    • September 2023
    • October 2022
    • April 2016
    • March 2016
    • February 2016

    Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org
    © 2025 James O'Claire | Powered by Minimalist Blog WordPress Theme