Live Views in ClickHouse

Live Views in ClickHouse stores the result of the query used to create the view and it is different from a normal view which doesn’t store any data in it. The data stored for a Live View changes based on the changes in the original data from which the view is derived. This feature is an experimental feature as of June 2023. The live views can provide speedups in frequently used queries because the query results are pre-computed and stored beforehand along with the partial result needed to combine with the new data in the source table. Let us look at an example for live views

Step 1 – Enable Experimental Live View

Go to the users.xml file and add this section to the profile for which this live view has to be enabled.

<profiles>
    <!-- Default settings. -->
    <default>
    <allow_experimental_live_view>1</allow_experimental_live_view>
    </default>
<profiles>

Alternatively, we can use the SQL statement to enable live view for the session

SET allow_experimental_live_view = 1;

Step 2 – Create the source table (Optional)

Create the following source table which uses MergeTree table engine.

CREATE TABLE live_view_source
(
ID UInt64,
Name String,
Value Float64
)
Engine = MergeTree
ORDER BY ID;

Step 3 – Create Live View

We can create a live view using CREATE LIVE VIEW statement.

CREATE LIVE VIEW live_view AS 
SELECT ID, Name, AVG(Value) as average, MIN(Value) as min_val, MAX(Value) as max_val
FROM live_view_source
GROUP BY ID, Name;

This view will store the results of the below SQL query.

SELECT ID, Name, AVG(Value) as average, MIN(Value) as min_val, MAX(Value) as max_val FROM live_view_source GROUP BY ID, Name;

References

https://clickhouse.com/docs/en/sql-reference/statements/create/view#live-view-experimental

Battle of the Views – ClickHouse Window View vs Live View