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 220.127.116.11 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 18.104.22.168 (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
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 │ └────────────────────────────────────────────┴───────┘
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
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
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.
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.