Subscribe to data

[sigirisetti]

Hello folks,

I have some experience with Kdb which supports data subscription. Real time update are pushed to table susbcriptions

I was going through clickhouse documentation for such a feature and didn't find any or may be missed it. Is it possible to subscribe to clickhouse table updates or any other clickhouse feature to achieve the same

Thank you

[den-crane]

Nothing like this. Try https://clickhouse.tech/docs/en/sql-reference/statements/create/view/#live-view-monitoring
https://altinity.com/blog/tag/live-views/

[den-crane]

ah, Also materialized view is an insert trigger.

[loyd]

Be careful, LV reruns the query after every max_live_view_insert_blocks_before_refresh (64 by default) inserted blocks. So, even the simplest queries like SELECT max(timestamp) FROM some_table will be slow for large tables.

Thus, I don't recommend you to get any data from LV and use it only to get events about new data:

CREATE LIVE VIEW test1_lv AS SELECT now64(9) FROM test1 LIMIT 1
WATCH test1_lv EVENTS

You will receive events on every INSERT. Then, you can select data, probably with throttling.

---

Also, the next approach is useful to filter events in order to reduce extra SELECTs:
For instance, we have some table with a field that we want to use to filter stream (name):

CREATE TABLE test1(name String, ts DateTime) ENGINE = MergeTree ORDER BY (name, ts)

Create a filter table (programmatically):

CREATE TABLE filter(name String) ENGINE = Memory;

Now, use the filter table in your LV:

CREATE LIVE VIEW test1_lv AS SELECT now64(9) FROM test1 WHERE name IN filter LIMIT 1
WATCH test1_lv EVENTS

You can modify the filter table programmatically to add/remove subscriptions on the fly:

-- Subscribe to "a"
INSERT INTO filter VALUES ('a');
-- Unsubscribe
ALTER TABLE filter DELETE WHERE name = 'a'

You will receive events only on names in the test filter.

[loyd]

Also, It's reasonable to invite @vzakaznikov here to comment on this approach)