In this lesson we will:

  • introduce materialised views;
  • Demonstrate how Clickhouse Materialised Views are more powerful than materialsied views as implemented by other databases.

What Is A View?

A view is a convenience function for.

create table pizza_orders ( order_id integer, customer_id integer, value float );
create view high_value_orders as select * from pizza_orders where value > 90.00

A view does not store any data, but instead queries the underlying table each time the view is accessed. This means they do not necessarily give any performance speed up.

What Is A Materialised View?

Unlike a view, a materialised view actually saves the results of the query on disk.

update x

The upside of this is that the materialised views are faster to query, because the query is already pre-calculated. However, there are two downsides.

Firstly, they take more storage because the results. This could imply duplication such as in the example below where the high value orders will be stored in both the original table and the materialised view.

Secondly, manaing the materialised views will add delays when we insert and update data, and require database resources.

Materialised Views and Table Engines

The behaviour of our Materialised View can be combined with table engines to acheive.

For instance, we could use a SummingMergeTree, or a ReplacingMergreeTree to

Next Lesson:

Connecting Clickhouse To Kafka

In this lesson we learn how to connect Clickhouse to Kafka to ingest in real time streams of messages.

0h 10m

Continuous Delivery For Data Engineers

This site has been developed by the team behind Timeflow, an Open Source CI/CD platform designed for Data Engineers who use dbt as part of the Modern Data Stack. Our platform helps Data Engineers improve the quality, reliability and speed of their data transformation pipelines.

Join our mailing list for our latest insights on Data Engineering:

Timeflow Academy is the leading online, hands-on platform for learning about Data Engineering using the Modern Data Stack. Bought to you by Timeflow CI

© 2023 Timeflow Academy. All rights reserved