Taking a Closer Look at ClickHouse Live View Tables

Dec 5, 2019

In the couple of previous blog posts, I have introduced Live Views tables and covered basic usage. Now, in this post, we will take a closer look at Live View tables.
Specifically, we will look at the options available for the WATCH query, then
introduce temporary Live Views, as well
as look at the new JSONEachRowWithProgress format.

Setting Things Up

As before we will be using the 19.16.3 release and will use two instances
of clickhouse-client. Don’t forget that we will need to enable the
allow_experimental_live_view setting in both sessions.

ClickHouse client version (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 19.16.3 revision 54427.

:) SET allow_experimental_live_view = 1

To keep the setup to the minimum, we will again use a very simple
source table

CREATE TABLE myints (a Int32) Engine=Memory

and pre-populate it with some initial data

INSERT INTO myints VALUES (1),(2),(3)

and use it in a simple Live View table.


WATCH Query Options

The WATCH query enables us to watch a live stream of query results.
Without any options, the query returns the current query result
and infinitely waits for changes to the query result.

:) WATCH lv


│      6 │        1 │
↙ Progress: 1.00 rows, 16.00 B (8.93 rows/s., 142.95 B/s.)

To abort the WATCH query you can use Ctrl-C.


However, an infinite stream of query result updates in not always necessary.
Sometimes you just want to get a fixed number of changes, one of the most
common cases being just the need to get the next update. To control the number of
query result updates that you would like to receive you can use the LIMIT
option where the value of the LIMIT can be 0 to N. The value of 0
indicates that the WATCH query should not wait for any new query results
and therefore will return immediately once query is evaluated.
One way to think of LIMIT 0 is as if you are setting the non-blocking flag.

Let’s give it a try and see that the WATCH query with the LIMIT 0
returns immediately.



│      6 │        1 │

1 rows in set. Elapsed: 0.002 sec. 

user-node :)

However, using the WATCH query with the LIMIT set to 0 is not very useful
as you might as well use the SELECT instead.

The most common value of LIMIT is just 1. In many use cases
you do not want to receive an infinite number of query result changes
but only the next one. With the LIMIT set to 1 you will receive
the current query result and will be notified only once when
the query result changes.

Again, let’s give a try. Execute WATCH query in one client and
insert one more row into our source table for the WATCH query to complete.

Once you have the WATCH query running you can insert more data as follows.


The client that has the WATCH query running should show the following.



│      6 │        1 │
│     10 │        2 │

2 rows in set. Elapsed: 20.346 sec.

As you can see, the WATCH query only provided one update as expected.
You might ask why is LIMIT 1 very common? The answer is simple:
it allows you to get notified the next time the query result has changed.
One such use case is watching a table that stores log messages.
In this case we can use count() for our Live View. A
separate SELECT query can be issued once we detect that the count() has changed,
indicating that there are new log messages available to read from our source table.


A short form of the WATCH query can be constructed if you
specify EVENTS after the table name. In this case,
instead of the query result, you will just get the latest query
result version.

Note that the stored query will still be evaluated
every time source table data changes.

Let’s try it and confirm that with the EVENTS we only
get the current query result version without the query result itself.



│       2 │

The EVENTS is useful when you only want to
get short notifications that you can use as triggers
about changes to the query result without
looking at the actual result.

Temporary Live Views

In some use cases, for example in a dynamic web application,
Live Views can be created dynamically to display specific
live data to the user. However, it is common in such cases
that the application can exit unexpectedly without
cleaning up its Live Views. For such cases,
a TEMPORARY Live View can be used.

A TEMPORARY Live View can be created using
the CREATE TEMPORARY LIVE VIEW statement. This statement will create
a Live View which will be automatically deleted when no
users are watching the table after the timeout specified by the
temporary_live_view_timeout setting. By default
the value of temporary_live_view_timeout setting is set
to five seconds.

It is important to note that compared to a regular temporary
table, a TEMPORARY Live View table lives outside
the scope of a single session. This is done because sometimes clients
that create Live Views can disappear and reappear due to various
factors such as unstable network connection but as long as the client comes back within the timeout period the Live View table does not have to be recreated.

Because a TEMPORARY Live View table lives outside the scope
of a single session, it means that multiple clients can use the same
temporary Live View. The Live View table will automatically be deleted
after the timeout once all clients stop watching the table.

Let’s see TEMPORARY Live View in action by creating one


and immediately running WATCH temp_lv.

:) WATCH temp_lv

WATCH temp_lv

│     10 │        1 │

Note that the table will be deleted in five seconds so you need to be quick.

Now, abort the WATCH query using Ctrl-C and try to re-execute WATCH query after
five seconds. You should see the following error.

:) WATCH temp_lv

WATCH temp_lv

Received exception from server (version 19.16.3):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table default.temp_lv doesn't exist..

As you can see, the Live View table has been deleted because the timeout after the last active user has elapsed. The fact that temporary Live Views are automatically deleted when no more clients are watching it helps with reducing resource consumption on the server. Remember, Live Views store their partial results in memory to always have the latest result ready for any client who might ask for it but it does come at a cost. First, similarly to the Materialized Views, Live View’s stored query is executed on each insert block and therefore inserts into the source table are blocked until processing completes. Second,
mergeable blocks for the query result are kept in memory and therefore each active Live View will consume memory. The amount of memory depends on the query result. Therefore, keeping the number of Live Views to a minimum will help speed up inserts and will reduce memory
consumption on the server.

Naming Temporary Live Views

In a use case where temporary Live Views are created dynamically and are shared between
different clients the question of naming temporary Live Views can arise.
The recommended solution to this problem is to use the SHA1 of the query
that is used to create the Live View table as a part of the table name.

For example, a temporary Live View for the SELECT 1 query can be created using
the lv_<SHA1> convention with the addition of the IF NOT EXISTS clause.

CREATE TEMPORARY LIVE VIEW IF NOT EXISTS lv_42364a017b73ef516a0eca9827e6fa00623257ee AS SELECT 1

Using this convention will allow other clients to easily reuse this Live View
or create a new one if it does not already exist.

Using Live Views Over HTTP Interface

The most common way that clients connect to Live View tables is via an HTTP interface.
The HTTP interface provides ease of use and portability across many systems and programming
languages and it is especially useful for web applications.

Let’s see how we can use an HTTP interface to watch our previously created lv
table using the curl utility.
Note, that for the WATCH query to work over an HTTP interface we must not
forget to set the allow_experimental_live_view setting for the connection.

echo 'WATCH lv LIMIT 0' | curl 'http://localhost:8123/?allow_experimental_live_view=1' --data-binary @-
10    2

Now, let’s re-run the same query but with the LIMIT set to 1.

echo 'WATCH lv LIMIT 1' | curl 'http://localhost:8123/?allow_experimental_live_view=1' --data-binary @-
10    2

As you can see the connection remains open and curl does not return. Instead,
the connection will block until the query result changes.
So let’s insert another row into our source table so that a new query result is pushed
to our HTTP client.


You should observe that your curl command exits and the result should be as below.

$ echo 'WATCH lv LIMIT 1' | curl 'http://localhost:8123/?allow_experimental_live_view=1' --data-binary @-
10    2
15    3

Wait, did you see that? ClickHouse has just pushed a notification to the outside
world! No longer do you have to re-send the same query over and over again to your
database server and bother it with unnecessary requests. With Live View tables, ClickHouse
can now notify you that query result has changed and provide you with the new result
at the same time.

Keeping Long-Lived Connections Alive

Because watching Live View tables requires a long-lived connection, Live View
tables support heartbeats to keep the connection alive.
The interval between heartbeats is set using the live_view_heartbeat_interval
setting. By default, the live_view_heartbeat_interval is set to fifteen seconds.
Every heartbeat interval the WATCH query will provide a progress notification
that you can observe when using clickhouse-client. Let’s start a WATCH query
again in the clickhouse-client and this time we will pay attention to the
progress notifications that are displayed below the query result.

:) WATCH lv


│     15 │        3 │
↗ Progress: 1.00 rows, 16.00 B (9.70 rows/s., 155.27 B/s.)

Do you see that arrow ? before the Progress message? Pay attention and you should
see that the arrow keeps turning once in a while. How often does it turn?
You guessed it, every live_view_heartbeat_interval seconds or whenever a new query result
is returned. These periodic progress events are what the Live View table uses to keep
the long-lived connection alive.

New JSONEachRowWithProgress Format

When Live View tables were added to ClickHouse a new output format was added as well.
The new format is JSONEachRowWithProgress and is used to facilitate using
Live View tables over the HTTP interface using the HTTP long polling technique.

Let’s again use curl command to watch our Live View. This
time lets use the JSONEachRowWithProgress format.

First, execute the WATCH query with the LIMIT 0.

$ echo 'WATCH lv LIMIT 0 FORMAT JSONEachRowWithProgress' | curl 'http://localhost:8123/?allow_experimental_live_view=1' --data-binary @-

Now re-execute the same query but using the old JSONEachRow format so that we can
compare them.

$ echo 'WATCH lv LIMIT 0 FORMAT JSONEachRow' | curl 'http://localhost:8123/?allow_experimental_live_view=1' --data-binary @-

If you compare the two outputs there should be no surprises. The JSONEachRowWithProgress differ
from the old JSONEachRow by having extra progress messages added to the output.
But why do we need this new output format? The answer is simple, to keep the long-lived HTTP connections

Let’s see how it works when our WATCH query is blocked waiting for new query results.
Now we will use the LIMIT 1 to block and wait for the new result.

We should see the following

$ echo 'WATCH lv LIMIT 1 FORMAT JSONEachRowWithProgress' | curl 'http://localhost:8123/?allow_experimental_live_view=1' --data-binary @-

and then every live_view_heartbeat_interval seconds we should see progress messages
added to the output.


These progress messages are what keeps the long-lived HTTP connection alive until
the query result changes. Without them, the HTTP connection would just timeout.


We have taken a closer look at Live View tables. We looked at the WATCH query options,
introduced temporary Live Views, and looked at the new JSONEachRowWithProgress format.
All these pieces should enable you to start putting Live View tables into practice.
As always until the next time, happy viewing using Live View tables!



  1. Thanks for the blog. Very informative.

    I am running into an error when running the command to create the temporary view. Is it still valid feature?

    “`18927489c009 🙂 CREATE TEMPORARY LIVE VIEW IF NOT EXISTS lv_42364a017b73ef516a0eca9827e6fa00623257ee AS SELECT 1

    Syntax error: failed at position 18 (‘LIVE’):

    CREATE TEMPORARY LIVE VIEW IF NOT EXISTS lv_42364a017b73ef516a0eca9827e6fa00623257ee AS SELECT 1

    Expected TABLE

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.