0

Is it possible to define the TTL for a table in Clickhouse so that it references other table? Let's say I have a chat application and in my database I have two tables: chats and chat_messages. Chats have start and stop time information and I want to delete old chats along with their messages entirely when they expire - so basing on the chat stop_time. I tried to create those tables in following way:

db43af298bb9 :) CREATE TABLE chats (id Int64, start_time DateTime, stop_time DateTime) ENGINE = MergeTree() ORDER BY (start_time, id) TTL stop_time + INTERVAL 1 MONTH;

CREATE TABLE chats
(
    `id` Int64, 
    `start_time` DateTime, 
    `stop_time` DateTime
)
ENGINE = MergeTree()
ORDER BY (start_time, id)
TTL stop_time + toIntervalMonth(1)

Ok.

0 rows in set. Elapsed: 0.014 sec. 

db43af298bb9 :) CREATE TABLE chat_messages (id Int64, text String, chat_id Int64) ENGINE = MergeTree() ORDER BY id TTL (SELECT stop_time from chats where chats.id = chat_id) + INTERVAL 1 MONTH;

CREATE TABLE chat_messages
(
    `id` Int64, 
    `text` String, 
    `chat_id` Int64
)
ENGINE = MergeTree()
ORDER BY id
TTL 
(
    SELECT stop_time
    FROM chats
    WHERE chats.id = chat_id
) + toIntervalMonth(1)

Received exception from server (version 19.16.10):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Missing columns: 'chat_id' while processing query: 'SELECT stop_time FROM chats WHERE id = chat_id', required columns: 'id' 'chat_id' 'stop_time', source columns: 'stop_time' 'id' 'start_time'. 

0 rows in set. Elapsed: 0.017 sec.

The TTL definition for the second table fails because it tries to find the 'call_id' column in 'chats' table instead of the source 'chat_messages' table. Is what I'm trying to achieve even possible or am I forced to use ALTER DELETE mechanism instead?

makasprzak
  • 5,082
  • 3
  • 30
  • 49
  • it's not possible. Moreover it's mis-design. CH is OLAP. It should be one wide table , not two. – Denny Crane Apr 02 '20 at 14:34
  • Thank you @DenisZhuravlev. The real case is way more complex and the equivalent of 'chats' is actually a materialized view of a typical OLAP table that aggregates data into such time bounded entities. The point is we are looking for a possibility to consistently remove those entities at once. I tried to make the example as simple as possible. Btw, would you mind taking a look at this other related question? https://stackoverflow.com/questions/60994456/clickhouse-ttl-vs-alter-delete – makasprzak Apr 02 '20 at 14:57

0 Answers0