In this lesson we will:
- introduce materialised views;
- Demonstrate how Clickhouse Materialised Views are more powerful than materialsied views as implemented by other databases.
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.
Unlike a view, a materialised view actually saves the results of the query on disk.
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.
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
In this lesson we looked closely at Materialised Views in Clickhouse, and described the benefits and costs in using them.
We also considered briefly how materialised views can be combined with various table engines to achieve the desired semantics that we need.