Making Data Come to Life with ClickHouse Live View Tables

13 Nov, 2019

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, 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.