--- url: 'https://altinity.com/blog/2019-11-13-making-data-come-to-life-with-clickhouse-live-view-tables' title: 'Making Data Come to Life with ClickHouse® Live View Tables' author: name: vzaltinity url: 'https://altinity.com/author/vzaltinity/' date: '2019-11-13T13:19:22-08:00' modified: '2019-11-13T13:19:22-08:00' type: post summary: Have you ever wanted to get a notification from your database when the query result has changed due to new data? Have you ever wanted to build a real-time dashboard where your graphs would change in real-time based on fresh... categories: - Blog tags: - ClickHouse - Live Views image: 'https://altinity.com/wp-content/uploads/2020/02/527aa-1241_sm.jpg' published: true --- # Making Data Come to Life with ClickHouse® Live View Tables Have you ever wanted to get a notification from your database when the query result has changed due to new data? Have you ever wanted to build a real-time dashboard where your graphs would change in real-time based on fresh aggregates from your ClickHouse cluster? Well, starting in the 19.14.3.3 ClickHouse release, an experimental feature was added to ClickHouse that you most likely did not notice. Now in addition to the classical View tables as well as the powerful [Materialized Views](https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-1), ClickHouse added to its toolbox support for Live View tables. ## What is a Live View? With the brief introduction out of the way, let’s start using Live Views to see how cool they are and how easy it is to make one. In my case I will be using the 19.16.3 stable release. ``` $ clickhouse-client ClickHouse client version 19.16.3.6 (official build). Connecting to localhost:9000 as user default. Connected to ClickHouse server version 19.16.3 revision 54427. user-node :) ``` Because Live Views are an experimental feature you need to enable the support for them using the `allow_experimental_live_view` setting. `user-node :) SET allow_experimental_live_view = 1` Let’s now check if the setting was set as well as what other settings we have related to Live View tables. ``` user-node :) SELECT name, value FROM system.settings WHERE name LIKE '%live_view%' SELECT name, value FROM system.settings WHERE name LIKE '%live_view%' ┌─name───────────────────────────────────────┬─value─┐ │ allow_experimental_live_view │ 1 │ │ live_view_heartbeat_interval │ 15 │ │ temporary_live_view_timeout │ 5 │ │ max_live_view_insert_blocks_before_refresh │ 64 │ └───────────────────────────────────────────┴────────┘ ``` The `**allow_experimental_live_view**` setting was set and we are ready to get started. ## Live Views in Action To get started we need to create a source table that we will use in our Live View. Let’s make something very simple that will store some integers. ``` user-node :) CREATE TABLE myints (a Int32) Engine=Memory CREATE TABLE myints ( `a` Int32 ) ENGINE = Memory ``` Now that we have a source table, we can create a Live View on top of it using the `CREATE LIVE VIEW` statement. The syntax is similar to creating a View where the stored query is specified using the `AS SELECT` clause. ``` user-node :) CREATE LIVE VIEW lv AS SELECT sum(a) FROM myints CREATE LIVE VIEW lv AS SELECT sum(a) FROM myints ``` Let’s insert a few rows into our source table `user-node :) INSERT INTO myints VALUES (1),(2),(3)` and see what we have in our Live View using the `SELECT` query. ``` user-node :) SELECT * FROM lv SELECT * FROM lv ┌─sum(a)─┐ │ 6 │ └────────┘ ``` Well, nothing new and nothing surprising, but then again `SELECT` queries are not what Live Views are for. Let’s rub our hands and get ready to see real-time results using the new `WATCH` query. We first open another ClickHouse client so that we can insert new data in one terminal and see the results in another. Launch another ClickHouse client and this time let’s use the new `WATCH` query. Note that you need to enable `WATCH` queries by using the `allow_experimental_live_view` setting in this new session. So let’s do that first `user-node2 :) SET allow_experimental_live_view = 1` and now execute the `WATCH` query using our Live View table. ``` user-node2 :) WATCH lv WATCH lv ┌─sum(a)─┬─_version─┐ │ 6 │ 1 │ └───────┴───────────┘ ↑ Progress: 1.00 rows, 16.00 B (0.02 rows/s., 0.36 B/s.) ``` What we see is that our `WATCH` query does not return but sits there waiting. What is it waiting for? You guessed it, the `WATCH` query is waiting to provide us the new query result once the result changes. Are you ready for the magic? I am sure you are, so go back to the first ClickHouse client and insert more data. Let’s add three more rows like so. `user-node :) INSERT INTO myints VALUES (4),(5),(6)` Did you see that? Check the other ClickHouse client where you have the `WATCH` query running. You should see that it is showing something new. ``` user-node2 :) WATCH lv WATCH lv ┌─sum(a)─┬─_version─┐ │ 6 │ 1 │ └───────┴───────────┘ ┌─sum(a)─┬─_version─┐ │ 21 │ 2 │ └───────┴───────────┘ → Progress: 2.00 rows, 32.00 B (0.01 rows/s., 0.11 B/s.) ``` It sent us the new query result of twenty one! Wait, let’s add more data in our first ClickHouse client `user-node :) INSERT INTO myints VALUES (7),(8),(9)` and on the other client see what the `WATCH` query shows us ``` user-node2 :) WATCH lv WATCH lv ┌─sum(a)─┬─_version─┐ │ 6 │ 1 │ └───────┴───────────┘ ┌─sum(a)─┬─_version─┐ │ 21 │ 2 │ └───────┴───────────┘ ┌─sum(a)─┬─_version─┐ │ 45 │ 3 │ └───────┴───────────┘ ↘ Progress: 3.00 rows, 48.00 B (0.01 rows/s., 0.13 B/s.) ``` There it is, our new stored query result of forty five! That is cool, isn’t? What you have just witnessed is Live Views in action. You no longer need to poll for new query results. The Live View table along with the `WATCH` query lets you see the data in real-time as soon as the result changes and only when the result changes. ## Conclusion I had a lot of fun implementing Live View tables to support my own project and I hope you now can think about how this new functionality can be applied to yours. Stay tuned to our blog for further articles as we continue to explore Live Views in more detail. Until next time, happy viewing using Live View tables! Don’t forget that at Altinity we are always happy to push ClickHouse to the next level.