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;